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:

   mysql_secure_installation
   

2. As prompted by the script, set a password for the root user, disallow remote root logins, and remove anonymous users.

3. 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.

4. 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...

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:

   sudo tree -puga /usr/lib*/mysql /lib*/mysql \
     /etc/mysql* /etc/my.cnf* /var/lib*/mysql
   

2. Stop MariaDB if it is running.

3. Change the ownership of all files that are not owned by either the root or mysql users to whichever...

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 Read permissions. They should not have...

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:

   SELECT @@old_passwords;
   

3. 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.

4. Go back to...

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:

   mkdir -v ssl-tmp;cd ssl-tmp
   

2. Create a certificate authority key file using...

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:

   CREATE DATABASE IF NOT EXISTS test;
   

3. Run the following command to create a role:

   CREATE ROLE read_only; 
   

4. Grant the role some permissions using the following statement:

   GRANT SELECT ON test.* TO read_only; 
   GRANT USAGE  ON test.* TO read_only;
   

5. Display the permissions granted to the role using the following statement:

   SHOW GRANTS FOR read_only;
   

   The output of the preceding statement is...

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:

   sudo adduser mysql shadow
   

2. 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:

   sudo adduser pamuser
   sudo passwd pamuser...