Case study: Installing and configuring MariaDB for least privilege

Note that absolutely none of this is authoritative or directly based on relevant documentation. It’s mostly what I found and figured out and guessed and (in some cases) made up. Some of it may be wrong or dangerous or lead to disaster or confusion. I am not taking responsibility here for anything, not even spelling. Read and digest at your own peril!

First, download MariaDB from MariaDB.


Log on as an administrator and install MariaDB. It proposes a default installation directory

C:\Program Files\MariaDB 11.7

which you should probably change to something more D:-ish, like

D:\Program Files\MariaDB 11.7

to separate applications from the operating system.

(This post will use the default path because the test machine only has one drive.)

It also proposes that you also install HeidiSQL which is a management client for MySQL and MariaDB. I recommend installing it because Heidi is a cute Swiss character.

It also asks whether UTF8 should be used as default character set and I think you should want that, assuming the alternative is ASCII.

On the same form it asks for the data directory and proposes

C:\Program Files\MariaDB 11.7\data

which again you probably should replace with something on the D drive and outside the program directory like

C:\MariaDB\data

to avoid a mix of program and data files in the same directory, which is always bad.

(I will leave the default setting here too.)


It will then ask for the password of the root user, the first user of the database server. Pick something really difficult to crack like secretpassword1. (We have already used secretpassword for PostgreSQL and we are not stupid. Never use the same password twice! But adding a 1 makes this completely safe, of course.)


Now, the good thing is that MariaDB actually installs itself using a service SID and you do not have to change it.

PS C:\> Get-Service mariadb*

Status   Name               DisplayName
------   ----               -----------
Running  MariaDB            MariaDB


PS C:\> sc.exe qc MariaDB
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: MariaDB
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 2   AUTO_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "C:\Program Files\MariaDB 11.7\bin\mysqld.exe" "--defaults-file=C:\Program Files\MariaDB 11.7\data\my.ini" "MariaDB"
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : MariaDB
        DEPENDENCIES       :
        SERVICE_START_NAME : NT SERVICE\MariaDB
PS C:\>

And the installer also configured permissions:

PS C:\Program Files\MariaDB 11.7> icacls .
. NT SERVICE\TrustedInstaller:(I)(F)
  NT SERVICE\TrustedInstaller:(I)(CI)(IO)(F)
  NT AUTHORITY\SYSTEM:(I)(F)
  NT AUTHORITY\SYSTEM:(I)(OI)(CI)(IO)(F)
  BUILTIN\Administrators:(I)(F)
  BUILTIN\Administrators:(I)(OI)(CI)(IO)(F)
  BUILTIN\Users:(I)(RX)
  BUILTIN\Users:(I)(OI)(CI)(IO)(GR,GE)
  CREATOR OWNER:(I)(OI)(CI)(IO)(F)
  APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(RX)
  APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)
  APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(RX)
  APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)

Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\MariaDB 11.7> icacls .\data\
.\data\ CHAMPIGNAC\Administrator:(F)
        CHAMPIGNAC\Administrator:(OI)(CI)(IO)(F)
        NT SERVICE\MariaDB:(OI)(CI)(F)
        NT SERVICE\TrustedInstaller:(I)(F)
        NT SERVICE\TrustedInstaller:(I)(CI)(IO)(F)
        NT AUTHORITY\SYSTEM:(I)(F)
        NT AUTHORITY\SYSTEM:(I)(OI)(CI)(IO)(F)
        BUILTIN\Administrators:(I)(F)
        BUILTIN\Administrators:(I)(OI)(CI)(IO)(F)
        BUILTIN\Users:(I)(RX)
        BUILTIN\Users:(I)(OI)(CI)(IO)(GR,GE)
        CREATOR OWNER:(I)(OI)(CI)(IO)(F)
        APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(RX)
        APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)
        APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(RX)
        APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)

Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\MariaDB 11.7>

Since NT Service\MariaDB is a member of Authenticated Users it is also a member of Users by default, hence has read access to the program directory. The Service SID is specifically given Full Control of the data directory. The service name has no version number in it and will hence likely not change, so we do not need to configure a group to represent it.


The only thing that is left to do is creation of the service resource group:

PS C:\> Get-Service mariadb*

Status   Name               DisplayName
------   ----               -----------
Running  MariaDB            MariaDB


PS C:\> New-LocalGroup RC_MariaDB-0x34

Name            Description
----            -----------
RC_MariaDB-0x34


PS C:\> $sddl=(sc.exe sdshow MariaDB)[1]
PS C:\> $sddl
D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
PS C:\> Get-LocalGroup RC_MariaDB-0x34|Format-List


Description     :
Name            : RC_MariaDB-0x34
SID             : S-1-5-21-344341352-2539047333-2300305637-1036
PrincipalSource : Local
ObjectClass     : Group



PS C:\> $sddl="D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)(A;;0x34;;;S-1-5-21-344341352-2539047333-2300305637-1036)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)"
PS C:\> sc.exe sdset MariaDB $sddl
[SC] SetServiceObjectSecurity SUCCESS
PS C:\> sc.exe sdshow MariaDB

D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)(A;;LCRPWP;;;S-1-5-21-344341352-2539047333-2300305637-1036)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
PS C:\>

Add whomever you want to the resource group.


Use HeideiSQL to connect to MariaDB.

Next: TBD