Securing MySQL – Making Use of Data Access Privileges for a Secure Installation

MySQL installation security is something that should be on the  mind of every MySQL DBA. While we have discussed how you should take care of your MySQL security as a whole (take a look at some of our previous posts, specifically the MySQL security series Part One and Part Two), we haven’t  discussed specific security-related issues, including  those issues related to privileges. We do that here.

What are Privileges in MySQL?

Privileges in MySQL can be granted to accounts. If you grant account privileges in MySQL, you determine which operations the account can perform. Privileges can be granted to either databases or database objects (tables, indexes, views etc.) Privileges can also be dynamic or static. Static privileges are built into the server while dynamic privileges can be defined at runtime.

How to Make Use of Privileges for  MySQL Security

In order to make use of privileges in MySQL, this is what you have to remember:

• In order to assign or revoke privileges, you need to have a MySQL user. Users can be created by running a CREATE USER query:
   

  CREATE USER ‘demouser’@’localhost’ IDENTIFIED BY ‘password’;

   

• To assign or revoke privileges, use the GRANT and REVOKE statements respectively:
   

  GRANT ALL ON demo_db.* TO ‘demouser’@’localhost’;
  REVOKE INSERT ON *.* FROM ‘demouser’@’localhost’;

   

• You can save privileges by running FLUSH PRIVILEGES. View them by running SHOW GRANTS.

• The GRANT statement cannot be used to grant both privileges and roles; the statement must grant either privileges or roles.

In order to make your MySQL installation more secure, consider the following:

• Only grant your users the privileges necessary to accomplish their tasks (e.g., don’t use GRANT ALL if that’s not necessary)

• In general, avoid running MySQL as an Unix root user because any user with the FILE privilege is able to cause the server to create files as root.

• Do not grant the FILE privilege to users who are not administrators (refer to the explanation above)

• Consider running mysqld as an ordinary unprivileged user.

• Do not grant PROCESS or SUPER privileges to users who are not administrators. The PROCESS privilege allows the user to view all processes running in MySQL. The SUPER privilege, among other things, enables server configuration changes, enables use of the CREATE SERVER, ALTER SERVER and DROP SERVER statements,and also enables use of the KILL statement letting the user kill statements belonging to other accounts. Bear in mind that MySQL reserves an extra connection for users who have the SUPER privilege. The SUPER privilege also lets a user control replication servers.

Follow the advice above and you should be well on your path to a more secure MySQL installation. However, if you’re using a newer version of MySQL, there is one more thing that we need you to be aware of – MySQL has introduced roles in MySQL 8.0.16.

Roles in MySQL Security

If you have never heard of roles in MySQL, don’t fret. Roles are very similar to privileges which is why we have included them in this blog post. In fact, roles are just that – they are collections of privileges, i.e.,  when a role is assigned to a user, a user is assigned all privileges connected to that role. Roles can be added and removed using the CREATE ROLE and DROP ROLE statements. To make use of roles in MySQL, create a role name, grant privileges to that role, and then assign it to a user like this:

CREATE ROLE [IF NOT EXISTS] ‘role_1’;
GRANT SELECT ON db_name.* TO user_name;
GRANT role_1 TO user_name@localhost;

What Privileges Should You Grant?

When granting privileges, follow the principle of least privilege: a MySQL account should only be granted privileges required for its purposes. The most commonly used privileges are:

• ALL PRIVILEGES which grants all privileges to an account.

• CREATE grants the privileges necessary to create databases and tables.

• DROP grants the privileges necessary to drop databases and tables.

• DELETE grants the privileges necessary to delete rows from tables.

• INSERT grants the privileges necessary to insert rows into tables.

• SELECT grants the privileges necessary to run SELECT queries.

• UPDATE grants the privileges necessary to update rows in a table (to run UPDATE queries)

In general, it is a good idea to avoid granting all privileges to an account; instead, consider following the principle of least privilege. Also keep in mind that granting additional privileges to a certain user does not remove privileges that were in place previously. You can also view the privileges of a specific user by using this syntax (replace username with the username of your user):

SHOW GRANTS FOR ‘username’;

Summary

When dealing with privileges in MySQL, keep in mind that you should follow the principle of the least privilege (i.e., only granting privileges that are necessary). Following the advice outlined in this blog should help make your MySQL installation more secure. Remember that your privileges need to be saved to take effect (the FLUSH PRIVILEGES statement can help you do that), in addition, keep in mind that from MySQL 8.0.16 you can make use of roles in MySQL to assign sets of privileges to a specific user.

We hope that this blog post has helped you secure your MySQL instance(s). If you have any questions or thoughts, feel free to comment, also consider having a look through our MySQL security series (here and here)