MariaDB Cookbook (2014)
Chapter 13. MariaDB Security
In this chapter, we will cover the following recipes:
· Securing MariaDB with mysql_secure_installation
· Securing MariaDB files on Linux
· Securing MariaDB files on Windows
· Checking for users with insecure passwords
· Encrypting connections with SSL
· Using roles to control user permissions
· Authenticating using the PAM authentication plugin
Introduction
Security is important, but because the value of the data in a given database ranges from worthless to billions of dollars, deciding on how much and what type of security to employ varies greatly. The recipes in this chapter focus on a few common ways to enhance MariaDB's default security, but they really only scratch the surface of the topic.
Securing MariaDB with mysql_secure_installation
The simplest way to add a bit of extra security to our MariaDB installation is just a command line away.
How to do it...
To secure a default install of MariaDB, perform the following steps:
1. Open a terminal and run the following command:
2. mysql_secure_installation
3. As prompted by the script, set a password for the root user, disallow remote root logins, and remove anonymous users.
4. Since we've been using the test database for various recipes in the current and other chapters, we may not want to remove it when prompted.
5. Reload the privilege tables when prompted.
How it works...
The mysql_secure_installation program is actually just a script written in PERL. Its sole purpose is to apply some basic security settings that nearly every MariaDB installation should have. This script should be run first thing after installing MariaDB on a server. It takes only a minute and should be considered as an essential step that we must perform whenever we install MariaDB.
There's more...
When installing MariaDB on Windows, Ubuntu, or Debian, we are prompted to set a root password. If we went ahead and did so, we would not need to set a root password when prompted by the script (and the script will tell us so). However, we will not be prompted to set a root user password when installing MariaDB on Red Hat, CentOS, or Fedora, so on those systems, running mysql_secure_installation is doubly important.
See also
· The full documentation of the mysql_secure_installation script is available at https://mariadb.com/kb/en/mysql_secure_installation/
Securing MariaDB files on Linux
Filesystem security is an important part of keeping the data in our databases safe. This is because MariaDB, like most programs, stores the data it handles in files on our filesystem. If those files can be read and copied by anyone who can log in to the server, then there's nothing stopping them from making a copy of those files and then accessing them with MariaDB on another server. This recipe is about securing our files on Linux.
Getting ready
Prior to starting this recipe, use the package manager to install the tree program.
On Fedora, Red Hat, or CentOS, run the following command:
sudo yum install tree
On Debian or Ubuntu, run the following command:
sudo apt-get install tree
How to do it...
1. Open a terminal window and run the following statements:
2. sudo tree -puga /usr/lib*/mysql /lib*/mysql \
3. /etc/mysql* /etc/my.cnf* /var/lib*/mysql
4. Stop MariaDB if it is running.
5. Change the ownership of all files that are not owned by either the root or mysql users to whichever of those is used for other files in the directory. For example, consider the following statement:
6. sudo chown -v mysql: /var/lib/mysql/flightstats/ontime.frm
7. Remove the read and write permissions from the group and other users from all files and directories under the /var/lib/mysql/ directory. The permissions of the /var/lib/mysql directory itself and the /var/lib/mysql/mysql.sock file (if it exists) are different, they should be 755 and 777, respectively. This is done using the following statements:
8. sudo chmod -vR go-rw /var/lib/mysql/
9. sudo chmod -v 755 /var/lib/mysql
10.sudo chmod -v 777 /var/lib/mysql/mysql.sock
11. Start MariaDB again.
How it works...
For this recipe, we use the tree program to view the ownership and permissions of various MariaDB files on our filesystem. This same information could be gathered using the find or ls programs, but their output is not as easy to read as the output of tree.
The most vulnerable MariaDB directory on Linux is the one where our data is actually stored. By default, this directory is /var/lib/mysql/, but it can be configured to reside somewhere else. If there does not appear to be any data under /var/lib/mysql/, then check the value of the datadir variable in our my.cnf file or in the mysql command-line client as follows:
SHOW VARIABLES LIKE 'datadir';
The way to prevent access to our databases from people who otherwise have legitimate access to our database server is to limit the access of the data directory to just the mysql user. This user is created automatically when installing the MariaDB packages.
In this recipe, we use the chown command to change the ownership of a single file. We can also recursively change the ownership of all files in a directory (and directories under that directory) in one go using chown with the -R flag on a directory. This is shown in the following statement:
sudo chown -Rv mysql: /var/lib/mysql/
All files under the /var/lib/mysql/ directory, with the exception of that directory itself, and the mysql.sock socket file (if it is there) can be set so that only the mysql user can access them. On Linux, this is typically set as 600 permissions for files and 700 permissions for the directories.
The socket file needs to have global read and write permissions so that remote clients can connect to our server. On some Linux distributions, this file is found under the /var/run/ or /run/ directory. So, we can lock down /var/lib/mysql/ even tighter. However, on other Linux distributions, the socket file is found under /var/lib/mysql/, and if the directory is /var/lib/mysql/, it must be accessible to everyone (755 permissions), and the socket file must have global read-write permissions (777).
If we are on a Linux distribution where the socket file is located under /var/lib/mysql/, we can configure a new location of our choice and then lock down the data directory so that only the mysql user can access it (700 permissions). This is a good thing to do as anyone with read access to the data directory, while they may not have rights to the files, can still see the names of all of our databases.
We should also be careful to keep our server up to date with all of the latest security updates of both the operating system and MariaDB.
There's more...
On Debian and Ubuntu distributions, there is a special file under /etc/mysql/ named debian.cnf, and like our data directory, special care should be taken to keep this file private. This file is automatically created when installing MariaDB. An example of this can be seen in the following screenshot:
The two password entries are randomly generated and will match each other. This file is used by the operating system to perform upgrades and other routine maintenance using the special debian-sys-maint user that is created automatically when installing MariaDB on Debian or Ubuntu. This database user has full access to all of our databases (if it didn't, it couldn't do the things it has to do). By default, this file is locked down so that only our system's root user has read and write access (600 or -rw, if we're viewing the permissions with ls -l). We should never change this, and if we use a configuration monitoring tool, we might want to set up a check to make sure that this file's permissions stay locked down.
If an attacker gains physical access to our database server, meaning they can open it up and physically remove the disk drive, our only way to prevent such an access to our database files is if we encrypt our entire disk. If we elect to not utilize encryption, either because of the performance penalty or some other reason, we need to make sure that the physical security of our server is appropriate to the value of the data in our database. The type or amount of security that this entails will vary on a case-by-case basis.
Securing MariaDB files on Windows
Filesystem security is an important part of keeping the data in our databases safe. This is because MariaDB, like most programs, stores the data it handles in the files on our filesystem. If these files can be read and copied by anyone who can log in to the server, then there's nothing stopping them from making a copy of those files and then accessing them with MariaDB on another server. This recipe is about securing our files on Windows.
How to do it...
1. Using Windows Explorer, navigate to the MariaDB installation directory (in MariaDB 10.0, the default location is C:\Program Files\MariaDB 10.0\).
2. Right-click on the directory and select Properties, as shown in the following screenshot:
3. In the Properties window, click on the Security tab and check the permissions. The SYSTEM and Administrator accounts should have full rights to the directory, but standard users should only have Read & execute, List folder contents, and Readpermissions. They should not have Write or any Special permissions as shown in the following screenshot:
4. Apply any changes if necessary and click on OK to close the Properties window.
How it works...
By default, the MariaDB installer for Windows configures the installation directory to have proper permissions. This doesn't mean that we can just assume everything is all right and we should not check periodically to make sure that the permissions are still what they should be.
We should also be careful to keep our server up to date with all of the latest security updates to both the operating system and MariaDB.
There's more...
If an attacker gains physical access to our database server, meaning they can open it up and physically remove the disk drive, our only way to prevent access to our database files is if we encrypt our hard drive. If we select to not utilize encryption, either because of the performance penalty or some other reason, we need to make sure that the physical security of our server is appropriate to the value of the data in our database. The type or amount of security that this entails will vary on a case-by-case basis.
Checking for users with insecure passwords
Our actual MariaDB user passwords are not stored in plain text by MariaDB as it would be very insecure. Instead, a mathematical hash of the password is stored. When we are connected, MariaDB hashes the password that we enter and compares it to the stored hash. This is all well and good, but in MariaDB, there are actually two hashing options and one is definitely better than the other.
How to do it...
To discover the password hashing function used by MariaDB and to make sure all of the users on our server are using the more secure option, perform the following steps:
1. Open the mysql command-line client and connect to our MariaDB database server with a user that has the SUPER privilege.
2. Find out what the value of the old_passwords variable is by using the following statement:
3. SELECT @@old_passwords;
4. If the value is not 0, inspect our configuration files and look for the setting. Remove any found instances (the entire line) and restart MariaDB.
5. Go back to the mysql command-line client and select the Host, User, and Password columns from the mysql.user table using the following statement:
6. SELECT Host,User,Password FROM mysql.user;
7. In the output, look for any users with short (16 characters) or empty values in the Password column.
8. Contact the identified users and have them set a new password.
How it works...
A long time back, in MariaDB's past, the password hashes generated were only 16 hexadecimal digits long. This was fine back then, but these password hashes are no longer fine today. Password hashes in MariaDB today begin with a * character followed by 40 hexadecimal digits. These hashes are much more secure, and all the users who still have old password hashes should upgrade them to the new style.
Before we tell users to change their passwords, we need to ensure that the new passwords they set will use the new password hash. The old_password variable controls which hashing function is to be used. By default, it is 0, which means the new password hashing function will be used. If set to 1, the old hashing function is used. This setting was introduced for backward compatibility when the new hashing function was introduced so that the old clients could still connect.
Every modern MariaDB client application should support the new password hashes, so there is no longer a need to have old_password=1. However, there are old example configurations out there that people sometimes copy and paste which contain this setting and which means we may have it set and not realize it.
Using old-style password hashes is dangerous because they are so short. Modern computers can crack them fairly easily, so they should never be used.
Once we've verified that our MariaDB configuration is not setting old_password=1 anywhere, we can have our users change their passwords (or change it for them). To identify users with old-style passwords, we can simply look at the hashes. We do this by selecting them from the mysql.user table using the following command:
SELECT User,Password FROM mysql.user;
The output will likely contain many more entries, but will be similar to the following screenshot:
The badpass user has an old-style password hash and the goodpass user has a current-style password hash. To properly identify the users, we would want to also select the Host column, but to make the output easier to read, it was not used in this simulated example.
See also
· The documentation of the PASSWORD() function is available at https://mariadb.com/kb/en/password/
· The documentation of the SET PASSWORD command is available at https://mariadb.com/kb/en/set-password/
Encrypting connections with SSL
When we are connecting to a MariaDB database running on our local workstation, there's really no need to think about whether or not the traffic between the mysql client and our database is secure. The traffic is all local and is confined to a single machine.
If, on the other hand, our client is running on one server and our database is on another server in some other part of the world, or even in the same datacenter, we should think about encrypting the traffic between the two.
Getting ready
This is a Linux-only recipe. To prepare for this recipe, we will need a set of SSL certificates. Certificates signed by a recognized and trusted certificate authority are preferred, but we can also use certificates we create ourselves. To create a set of self-signed certificates, we need to perform the following steps:
1. Create a temporary directory and navigate to it by using the following statement:
2. mkdir -v ssl-tmp;cd ssl-tmp
3. Create a certificate authority key file using the following statement:
4. openssl genrsa -out mariadb-ca.key 4096
5. Create a certificate authority certificate using the following statements:
6. openssl req -x509 -new -nodes -days 9999 \
7. -key mariadb-ca.key \
8. -out mariadb-ca.pem
9. Answer the questions asked when running the command in step 3 using the defaults or our actual information. Do the same when asked in subsequent steps.
10. Create a key and certificate files for our MariaDB server using the following set of statements:
11.openssl genrsa -out mariadb-server.key 4096
12.
13.openssl req -new \
14. -key mariadb-server.key \
15. -out mariadb-server.csr
16.
17.openssl x509 -req -set_serial 01 -days 9999 \
18. -CA mariadb-ca.pem \
19. -CAkey mariadb-ca.key \
20. -in mariadb-server.csr \
21. -out mariadb-server.pem
22. Create a key and a certificate file to use with the mysql command-line client using the following set of statements:
23.openssl genrsa -out mariadb-client.key 4096
24.
25.openssl req -new \
26. -key mariadb-client.key \
27. -out mariadb-client.csr
28.
29.openssl x509 -req -set_serial 02 -days 9999 \
30. -CA mariadb-ca.pem \
31. -CAkey mariadb-ca.key \
32. -in mariadb-client.csr \
33. -out mariadb-client.pem
34. Move the certificates and keys to our MariaDB data directory using the following statements:
35.sudo mv -vi mariadb*.pem /var/lib/mysql/
36.sudo mv -vi mariadb*.key /var/lib/mysql/
We are now ready to start the actual recipe. The names used in the recipe will match the names of the certificates and keys we just created here. If we have other certificates and keys we want to use instead, we just need to modify the recipe to match their names.
How to do it...
1. Edit our configuration and add the following statements to the bottom of our my.cnf file or to an ssl.cnf file under the /etc/mysql/conf.d/ or /etc/my.cnf.d/ directories:
2. # SSL configuration for mysqld and the mysql client
3.
4. [mysqld]
5.
6. ssl-ca=/var/lib/mysql/mariadb-ca.pem
7.
8. ssl-key=/var/lib/mysql/mariadb-server.key
9.
10.ssl-cert=/var/lib/mysql/mariadb-server.pem
11.[mysql]
12.
13.ssl-ca=/var/lib/mysql/mariadb-ca.pem
14.
15.ssl-key=/var/lib/mysql/mariadb-client.key
16.
17.ssl-cert=/var/lib/mysql/mariadb-client.pem
18. Restart MariaDB.
19. Connect to MariaDB with the mysql command-line client and run the following commands:
20.STATUS;
21.SHOW VARIABLES LIKE 'have_ssl';
22.SHOW STATUS LIKE 'Ssl%';
23. Create a user that requires SSL by using the following statement:
24.GRANT ALL on test.* TO 'ssluser'@'localhost'
25. IDENTIFIED BY 'ssluserpassword'
26. REQUIRE SSL;
27. Exit the client and then reconnect as ssluser (this should succeed):
28.mysql -u ssluser -p test
29. Exit the client and then reconnect as ssluser using the --skip-ssl flag on the command line (this connection attempt should fail):
30.mysql -u ssluser -p --skip-ssl test
31. Exit the client and then reconnect as a different user that does not have REQUIRE SSL as part of their GRANT statements using the --skip-ssl flag (this connection attempt should succeed).
32.mysql -u root -p --skip-ssl
How it works...
SSL is supported in MariaDB Linux packages using whatever the system default version of OpenSSL is. Enabling support on the server side for SSL connections is simply a matter of adding the ssl-ca, ssl-key, and ssl-cert variables to a [mysqld] section of ourMariaDB configuration. On the client, we can choose to specify the information every time we connect, as shown in the following statements:
mysql -u ssluser –ssl-ca=/var/lib/mysql/mariadb-ca.pem \
--ssl-key=/var/lib/mysql/mariadb-client.key \
--ssl-cert=/var/lib/mysql/mariadb-client.pem test
However, it is far easier for us to add these to a [mysql] section of a configuration file.
Once SSL support is enabled, we can verify if it is working using the STATUS; and SHOW VARIABLES LIKE 'have_ssl'; commands.
The STATUS; command contains an SSL line, which shows us the SSL cipher being used to encrypt our connection to the database. An example of what this line looks like is shown as follows:
SSL: Cipher in use is DHE-RSA-AES256-SHA
The complete output of the STATUS; command will look similar to the following screenshot:
If SSL can be used and it is enabled, the output of the SHOW VARIABLES LIKE 'have_ssl'; command will look like the following screenshot:
If our installation of MariaDB supports SSL, but it is just not configured, the value of the have_ssl variable will be DISABLED. If SSL is not supported or built-in in our installation of MariaDB, the variable will be set to NO.
The SHOW STATUS LIKE 'Ssl%'; command shows us all of the various SSL status variables. The complete output is too large to show in a screenshot, but the following is an example screenshot showing just the Ssl_session% status variables:
When creating users, we can add a REQUIRE SSL option to the end that will force that user to connect using SSL. Users who don't have the REQUIRE option are free to connect with or without using SSL. To connect without SSL, we can add the --skip-ssl option to our command line. If a user with REQUIRE SSL tries to do this, they will get an error when they try to connect, as shown in the following screenshot:
The error is exactly like the error for a mistyped password.
There's more...
We can restrict a user's SSL connections to require specific certificates and specific ciphers if we want to. This is done as part of the GRANT command, but instead of simply saying REQUIRE SSL, we can use the following statements:
GRANT ALL on test.* TO 'ssluser'@'localhost'
IDENTIFIED BY 'ssluserpassword'
REQUIRE ISSUER '/C=US/ST=NC/L=Raleigh/0=MariaDB'
AND CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';
The information after REQUIRE ISSUER must match our actual certificates, and CIPHER needs to be the one supported by MariaDB. The Ssl_cipher_list status variable has a list of all supported ciphers. We can also specify the name and force the use of a valid X509certificate.
Another thing we could do would be to get our certificate signed by a recognized third-party signing authority like we would for an SSL-enabled website. The actual encryption is no different (SSL is SSL), but the fact that the certificate is verified adds a measure of trust.
See also
· SSL system variables are documented at: https://mariadb.com/kb/en/ssl-server-system-variables/
· SSL status variables are documented, along with other status variables, at https://mariadb.com/kb/en/server-status-variables/
· Per account SSL options for GRANT statements are documented at https://mariadb.com/kb/en/grant/#per-account-ssl-options
Using roles to control user permissions
Roles are an alternative way of managing permissions. They are used to give users permissions as a group instead of individually. For example, all users from the finance department could be assigned to a finance role with permissions specific to the tasks they need to perform.
Roles were first introduced in MariaDB 10.0.
How to do it...
To create an example role and demonstrate how roles work, perform the following steps:
1. Launch the mysql command-line client and connect to our MariaDB database server.
2. Create a test database, if it doesn't exist, using the following statement:
3. CREATE DATABASE IF NOT EXISTS test;
4. Run the following command to create a role:
5. CREATE ROLE read_only;
6. Grant the role some permissions using the following statement:
7. GRANT SELECT ON test.* TO read_only;
8. GRANT USAGE ON test.* TO read_only;
9. Display the permissions granted to the role using the following statement:
10.SHOW GRANTS FOR read_only;
The output of the preceding statement is shown as follows:
11. Create a test user using the following statement:
12.CREATE USER test_user@'localhost'
13. IDENTIFIED BY 'testpassword';
14. Display the permissions granted to test_user using the following statement:
15.SHOW GRANTS FOR test_user@'localhost';
16. Assign the read_only role to test_user, as follows:
17.GRANT read_only TO test_user@'localhost';
18. Display the permissions granted to test_user again using the following statement (they will be different than when we ran the statement previously):
19.SHOW GRANTS FOR test_user@'localhost';
The output of the preceding statement is shown as follows:
20. Log out of MariaDB and log back in as test_user using the following statement:
21.mysql -u test_user -p
22. Try to use the test database using the following statement (we will get an access denied error):
23.USE test;
24. Set the role to read_only and try to use the test database again using the following statement (this time, we will be able to access the test database):
25.SET ROLE read_only;
26.USE test;
27. Show the current role and the current user, as follows:
28.SELECT current_role();
29.SELECT current_user();
30. Display the permissions granted by using the following statement:
31.SHOW GRANTS;
How it works...
The roles feature is included and enabled in MariaDB by default. There's nothing that needs to be done before we can start using it.
Roles sort of exist in the same area as users, and we use the same commands (such as GRANT, REVOKE, and SHOW) with roles that we use for users. However, they are not users; for example, roles can't log in. Instead, roles are a collection of permissions that we can grant to a user.
Granting a role to a user doesn't automatically apply it to the user whenever they are logged in. The user has to use the SET ROLE command to enable a given role and the permissions that a role provides. We can see the currently enabled role with the SELECT current_role() command.
The output of steps 12 and 13 will look similar to the following screenshot:
When a role is enabled, the SHOW GRANTS command will show both the default GRANT permissions assigned to the user and the GRANT permissions provided by the currently applied role. The output of this command will look like the following screenshot:
See also
· The complete documentation of the roles feature is available at https://mariadb.com/kb/en/roles/
· Information about the development of the roles feature in MariaDB is available at https://mariadb.atlassian.net/browse/MDEV-4397
Authenticating using the PAM authentication plugin
We're not limited to using MariaDB's built-in authentication system. We can also authenticate users using Linux's Pluggable Authentication Modules (PAM) system. Using PAM can enable authentication schemes far beyond what MariaDB provides, including things such as using biometric scanners, authenticator token generators, and so on.
Getting ready
The PAM authentication plugin is only available on Linux, so the server-side portions of this recipe are Linux-only. The mysql command-line client on Windows can make use of the PAM authentication on a Linux-based MariaDB server so that part of the recipe is cross-platform.
How to do it...
1. On Debian or Ubuntu systems, add the system mysql user to the shadow group using the following command:
2. sudo adduser mysql shadow
3. Create a new system-login account named pamuser using either the useradd or adduser commands and set the user's password using the following statements:
4. sudo adduser pamuser
5. sudo passwd pamuser
6. Launch the mysql command-line client and connect to our MariaDB database server.
7. Install the auth_pam plugin using the following statement:
8. INSTALL SONAME 'auth_pam';
9. Create a user that matches the name of the system user and is authenticated using PAM in the following manner:
10.CREATE USER pamuser@'localhost' IDENTIFIED VIA pam USING 'common-password';
11. Grant the user privileges to our test database using the following statements:
12.GRANT ALL ON test.* to pamuser@'localhost';
13.FLUSH PRIVILEGES;
14. Open a new terminal window and use the mysql command-line client to connect to our server using the pamuser user we created using the following statement:
15.mysql -u pamuser
16. Enter the password when prompted and then issue the SHOW GRANTS; command to view the privileges granted to pamuser.
How it works...
Like many other plugins that ship with MariaDB, the PAM authentication plugin is disabled by default. This plugin can be easily enabled using the INSTALL SONAME command. The INSTALL PLUGIN command can also be used if desired.
Once installed, we can create users that are identified via PAM instead of via the standard password option. When identifying users in this way, we need to tell MariaDB what type of PAM authentication to use. These are defined using PAM configuration files. The existing ones can be found under the /etc/pam.d/ directory. To keep things simple with this recipe, the standard common-password authentication was used. It checks the password entered with the passwords stored in the shadow file, but it could be any sort of authentication that PAM supports, including LDAP, Active Directory, smart cards, or even biometric scanners.
The PAM authentication plugin simply hands authentication duties off to PAM and then waits for a response. This is why we are still prompted for a password even when we didn't specify -p on the command line when logging in. After PAM has checked our credentials, if it comes back with an OK, then our user is authenticated and given the rights granted to them. If not, the login fails as expected.
The output of steps 7 and 8, assuming we enter the password correctly, will look similar to the following screenshot:
There's more...
We don't have to rely on the existing configuration files or even the existing PAM plugins when using PAM to authenticate users. We can easily create our own plugins and configurations. The following See also section contains links to the documentations that describe how to do this.
See also
· More on the PAM authentication plugin can be found at https://mariadb.com/kb/en/pam-authentication-plugin/
· An excellent blog post on writing your own PAM authentication plugin can be found at https://blog.mariadb.org/writing-a-mariadb-pam-authentication-plugin/