Setting System Variables in MySQL Server

To enable MySQL for encrypted communication, MySQL requires the ssl_ca, ssl_cert, and ssl_key system variables, which specify the paths to SSL files that permit clients to connect through an encrypted connection.

Next, you need to edit the mysqld.cnf configuration file located in the /etc/mysql/mysql.conf.d directory for the new certificates and enable mandatory encrypted connection requirements for the client with the require_secure_transport [1] system variable:

$ vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
ssl_ca= /var/lib/mysql/new_certs/ca.pem
ssl_cert=/var/lib/mysql/new_certs/ server-cert.pem
ssl_key=/var/lib/mysql/new_certs/server-key.pem
require_secure_transport=ON

Now, change the ownership and permissions to SSL files and restart the database service:

$ chown -R mysql:mysql /var/lib/mysql/new_certs/
$ chmod 600 client-key.pem server-key.pem ca-key.pem
$ sudo service mysql restart

Once backed up, log in to the server and check the SSL status by typing \s or by checking the value of the have_ssl variable (Listing 3).

mysql> show global variables like '%ssl%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | \etc\mysql\new_certs\ca.pem          |
| ssl_capath    |                                      |
| ssl_cert      | \etc\mysql\new_certs\server-cert.pem |
| ssl_cipher    |                                      |
| ssl_crl       |                                      |
| ssl_crlpath   |                                      |
| ssl_key       | \etc\mysql\new_certs\server-key.pem  |
+---------------+--------------------------------------+
9 rows in set (0.53 sec)

The configuration ensures an SSL connection and disables MySQL client access to the server with the --ssl-mode=DISABLED string. The client will receive the error

$ mysql -u user -p -h <MySQL_IPaddress>
ERROR 1045 (28000): Access denied for user 'user'@'%' (using password: YES)

if access to the server is attempted.

Client-Side Encryption in Transit

To secure this connection further, copy the CA and client files to the client and modify the user to require a trusted certificate. To begin, create a directory in which to save client files and use scp or some other utility to transfer ca.pem, client-key.pem, and client-cert.pem files to the client machine:

$ mkdir ~/certs
$ scp user@[IP_Address]:/var/lib/mysql/new_certs/ca-cert.pem ~/certs/
$ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-cert.pem ~/certs/
$ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-key.pem ~/certs/

If the server is not configured with the require_secure_transport system variable and the user account is created with no REQUIRE clause or the account has no specific encryption requirements, as above, connection attempts fall to an unencrypted connection. To alter the user to add the REQUIRE X509 clause, enter:

mysql> alter user 'user'@'client_ip' require X509;
mysql> flush privileges;

From now onward, every remote connection from client user will require that -ssl-key and -ssl-cert options be specified, whereas adding the --ssl-ca variable is optional. These variables contain paths to the client's SSL files under the ~/certs directory:

$ mysql -u user -p -h <SSLServer_IPAddress>-ssl-ca= ~/certs/ca.pem -ssl-cert=~/certs/client-cert.pem -ssl-key=under ~/certs/client-key.pem

After hitting Enter, the client will establish a secure SSL connection. In the meantime, start the tshark sniffer to confirm encryption in transit. You will observe an encrypted communication.

Low-Privilege Users

MySQL offers account management statements to set up user accounts and control associated account privileges. The authorization system grants privileges that differ in context and are applied at varying levels of operations. However, it's a good practice not to assign unnecessary privileges to the account users and exercise caution by enabling limited resource access. In this section, I discuss security precautions for providing only enough access required for the job.

Least privileged user accounts reduce the risk of an attacker's access to critical systems. MySQL assigns administrative, database, and specific database object-relevant privileges to users.

The most common recommended privileges are SELECT, UPDATE, DELETE, and INSERT. However, if a user only needs to add information to the database, only the INSERT privilege is required to add records into it. To assign insert permission to the user, enter:

mysql> grant insert on database.* to 'user'@'localhost';

Similarly, the lowest level privilege for access only allows the user to read, edit, or delete a column. For column-level reading privileges, enter:

mysql> grant select(coulmn_name) on database.Clients to 'user'@'localhost';
mysql> flush privileges;

Find out more about the MySQL account authorization system from the official documentation [2].

Privilege Guidelines

MySQL offers certain privilege statements, which, if assigned unnecessarily, can potentially risk subverting the privilege system or reading and writing files accessed by the server host. The following are some potentially risky user privileges that can significantly affect server security:

• GRANT OPTION: Revokes or grants certain privileges from other users that the user itself poses. However, users can use the WITH clause to combine their assigned privileges.
• ALTER: Allows non-administrative users to undermine the authorization system by renaming tables.
• SHUTDOWN: Allows the use of mysqladmin shutdown to terminate the server and restrict server services to the users.
• SUPER: Controls server behaviors and operations and lets a client kill other account threads and modify the server configuration.
• PROCESS: Lets the user see process threads of other account users in plaintext and gives access to InnoDB INFORMATION_SCHEMA FILES tables.
• FILES: Allows user to read, write, and create files on the server host and is a global privilege that allows writing to the server data directory files that implement privilege tables.

Most importantly, grants for MySQL systems that allow access to the authentication_string column of the mysql.user table can enable changing the password and connecting to the server through that account.

You can find more privilege-granting guidelines for specific clauses from the official MySQL documentation [3].

Setting Resource Limits

Another way to set low-privilege user accounts that enhances MySQL security is to set up resource limits. MySQL offers a global system variable max_user_connections that allows setting a limit of simultaneous connections by given accounts. However, it does not place any limits on what happens once the user connects. Hence, MySQL offers an individual account management system by setting per-hour resource limits in the mysql.user table for:

• max_queries_per_hour to store queries made by the user in the max_questions column,
• max_updates_per_hour to store updates issued by an account in the max_updates column, and
• max_connections_per_hour to store number of user connects with the server in the max_connections column.

To establish these limits at account creation time, use create user or check assigned resources for an already established account with select user (Listing 4, which indicates that no limits have been set on resource access) before altering existing account limits with alter user and setting a limit for per-hour queries generated by the user:

mysql> create user 'user'@'localhost' identified by 'password'
    - > with max_queries_per_hour 15
    - > max_updates_per_hour 12
    - > max_connections_per_hour 4
    - > max_user_connections 3;
mysql> alter user 'user'@'localhost' with max_queries_per_hour 25;

mysql> select user, max_questions, max_updates, max_connections, max_user_connections from mysql.user where user='user_name';
+-----------+---------------+-------------+-----------------+---------------------+
| User      | max_questions | max_updates | max_connections | max_user_connections|
+-----------+---------------+-------------+-----------------+---------------------+
| user_name | 0             | 0           | 0               | 0                   |
+-----------+---------------+-------------+-----------------+---------------------+
1 row in set (0.001 sec)

Now, recheck the mysql.user table to confirm the settings.