Secure MySQL Database with System Roles and Permissions

Published on February 10, 2021•Updated on November 21, 2023

Introduction

In MySQL, a role is a group of named privileges that can be assigned to users. When creating a database application with a lot of users, managing their permissions individually can be a programming nightmare. This is where system roles and permissions come in.

For instance, take a hypothetical bank database application. It might have 5 system developers, 3 managers, several hundred bank clerks, and maybe 2 auditors.

In such a scenario, the 5 system developers should be granted full access to the database. That's, they should be in a position to create/read/update, and delete objects in the database.

Contrary to this, the 3 bank managers must have limited access to the database, and they may only have permissions to insert, update and delete new records. However, they may not be conversant with database programming, and therefore, you need to deny them permissions to drop database objects or even modify column names in the tables.

Next, you have got the bank clerks. They also need to log in to the system with limited access. While the clerks can only INSERT transactions in the database, they may need to turn to their managers if they want to delete an erroneously entered record.

Finally, auditors need to view data for reporting purposes. So, they should have read-only access to the database tables. You can manage such complicated permissions by using roles in MySQL version 8.x.

By using MySQL roles, you can reduce the number of grant commands needed to secure your application. To put this into a better perspective, if you want to change permissions for a thousand bank clerks dynamically, you'll revoke the grants for a single role instead of doing it manually for each clerk.

In this guide, you'll use MySQL roles and permissions to selectively enable and disable permissions across multiple users for the different database assets.

For this demonstration, you'll use a hypothetical bank database with 1 system developer, 1 manager, 2 bank clerks, and 1 bank auditor.

Prerequisites

Before you proceed with this tutorial, make sure you have the following:

An Ubuntu 20.04 server.
A sudo user.
A Lamp Stack.

This guide is tested on a MySQL database running on Ubuntu 20.04 server. However, it should also work on other Operating Systems supporting MySQL version 8.x.

Create a Sample Database and System Roles

Connect to your server and log in to MySQL as root.

$ sudo mysql -u root -p

Next, enter your root password for MySQL and press Enter to proceed. Begin by creating a blank bank database.

mysql> CREATE DATABASE bank;

Switch to the bank database.

mysql> USE bank;

Once the database is in place, you'll proceed to create sample tables.

Create a Clients Table

Next, create a clients table. This table will store the KYC (Know Your Customer) information for your hypothetical bank. It will assign a unique client_id for each customer in the bank and hold their names and address information.

mysql> CREATE TABLE clients (
       client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       first_name  VARCHAR(50),
       last_name  VARCHAR(50),
       address  VARCHAR(255)
       ) ENGINE = InnoDB;

Insert sample data to the clients table.

mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('JOHN', 'DOE', '1ST STREET, FRESNO, CA XYZ');
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('MARY', 'SMITH', '2ND STREET, MIAMI, FL 123');
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('STEVE', 'MARK', '3RD STREET, PHOENIX, AZ ABC');

Query the clients table to make sure that the records are inserted.

mysql> SELECT
       client_id,
       first_name,
       last_name,
       address
       FROM clients;

Make sure the clients are listed as shown below.

+-----------+------------+-----------+-----------------------------+
| client_id | first_name | last_name | address                     |
+-----------+------------+-----------+-----------------------------+
|         1 | JOHN       | DOE       | 1ST STREET, FRESNO, CA XYZ  |
|         2 | MARY       | SMITH     | 2ND STREET, MIAMI, FL 123   |
|         3 | STEVE      | MARK      | 3RD STREET, PHOENIX, AZ ABC |
+-----------+------------+-----------+-----------------------------+
3 rows in set (0.00 sec)

Create the Transactions Table

In your hypothetical bank, you'll store customer activities in the clients_transactions table. This table assigns a unique transaction_id for every debit or credit a customer makes to his/her account. The client_id in this table refers back to the clients table creating a parent-child relationship to associate the transactions with the customers.

The transaction_date column records the actual date when a new activity is inserted into the database. The transaction_type column holds a value of either a Credit(C) or a Debit(D). The amount column stores the actual monetary value for the transaction.

Create the clients_transactions table.

mysql> CREATE TABLE clients_transactions (
       transaction_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       client_id INT NOT NULL,
       transaction_date  DATE,
       transaction_type  VARCHAR(1), 
       amount  DECIMAL(17, 2)
       ) ENGINE = InnoDB;

Insert sample data to the clients_transactions table.

mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('1', '2021-01-21', 'C', '45.00');
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('2', '2021-01-21', 'C', '60.00');
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('3', '2021-01-21', 'C', '90.00');
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('3', '2021-01-21', 'D', '14.00');

Query the clients_transactions table to make sure the data is in place.

mysql> SELECT
       transaction_id,
       client_id,
       transaction_date,
       transaction_type,
       amount
       FROM clients_transactions;

Confirm the listing below.

+----------------+-----------+------------------+------------------+--------+
| transaction_id | client_id | transaction_date | transaction_type | amount |
+----------------+-----------+------------------+------------------+--------+
|              1 |         1 | 2021-01-21       | C                |  45.00 |
|              2 |         2 | 2021-01-21       | C                |  60.00 |
|              3 |         3 | 2021-01-21       | C                |  90.00 |
|              4 |         3 | 2021-01-21       | D                |  14.00 |
+----------------+-----------+------------------+------------------+--------+
4 rows in set (0.00 sec)

Create System Roles

With the sample data in place, you'll now create system roles. For this guide, you'll have the following groups:

system_developer: You'll assign this role to the system developers designing the bank application. They'll have full access to the database. For this guide, you'll have a 1 system developer.
bank_manager: You'll use this role to manage permissions for bank managers. They can create, read, update and delete records, but they won't have any permissions to create or drop database objects.
bank_clerk: In a real-world scenario, you'll rely on many bank clerks to record deposits and withdraws for the customers in the clients_transactions table. Therefore, your bank clerks require permission to create new clients, but they will need to turn to their manager if they want any recorded updated or deleted upon giving a justifiable reason. You'll create 2 clerks for this demonstration.
bank_auditor: Your auditor needs read-only permissions to the tables for reporting purposes. You'll only have 1 auditor in this guide.

So, create all the required system roles by running the commands below one by one.

mysql> CREATE ROLE 'system_developer'@'localhost';
mysql> CREATE ROLE 'bank_manager'@'localhost';
mysql> CREATE ROLE 'bank_clerk'@'localhost';
mysql> CREATE ROLE 'bank_auditor'@'localhost';

Make sure the roles are inserted into the database.

mysql> SELECT User FROM mysql.user;

Ensure the names of the roles appear on the MySQL users' list, as shown below.

+------------------+
| User             |
+------------------+
| ...              |
| bank_auditor     |
| bank_clerk       |
| bank_manager     |
| system_developer |
| ...              |
+------------------+

Assign Privileges to the Roles

In this step, you'll grant permissions to the roles that you've just created.

Begin by granting full permissions to the system_developer role.

mysql> GRANT ALL PRIVILEGES ON bank.* TO 'system_developer'@'localhost';

Next, assign permissions to the bank_manager role. Essentially, you want the manager to only create, read and update the clients table but have INSERT, SELECT, UPDATE, and DELETE permissions to the clients_transactions table.

mysql> GRANT INSERT, SELECT, UPDATE ON bank.clients TO 'bank_manager'@'localhost';
mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON bank.clients_transactions TO 'bank_manager'@'localhost';

Then, give INSERT and SELECT permissions to the bank clerks but deny them UPDATE and DELETE privileges since the manager will perform such risky commands.

mysql> GRANT INSERT, SELECT ON bank.clients TO 'bank_clerk'@'localhost';
mysql> GRANT INSERT, SELECT ON bank.clients_transactions TO 'bank_clerk'@'localhost';

Next, grant SELECT(read-only) permissions to the bank auditor.

mysql> GRANT SELECT ON bank.clients TO 'bank_auditor'@'localhost';
mysql> GRANT SELECT ON bank.clients_transactions TO 'bank_auditor'@'localhost';

To confirm permission for any role, use the command below.

mysql> SHOW GRANTS FOR '[Role Name]'@'localhost';

For instance, to confirm the permissions for the bank_auditor, run.

mysql> SHOW GRANTS FOR 'bank_auditor'@'localhost';

The bank_auditor grants should be listed as shown below.

+----------------------------------------------------------------+
| Grants for bank_auditor@localhost                              |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bank_auditor`@`localhost`               |
| GRANT SELECT ON `bank`.`clients` TO `bank_auditor`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

Create the System Users

Once you've defined the system roles, you can add as many users to each role. Begin by creating a user named mary. You'll later assign her to the system_developer role.

mysql> CREATE USER 'mary'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_MARY_PASSWORD';

Then, create a user named raphael who later takes the bank_manager role.

mysql> CREATE USER 'raphael'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_RAPHAEL_PASSWORD';

Provision two bank clerks named james and andrew. They will be in charge of creating new clients and recording customer activities in the database.

mysql> CREATE USER 'james'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_JAMES_PASSWORD';
mysql> CREATE USER 'andrew'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_ANDREW_PASSWORD';

Finally, add auditor bob to your MySQL server. He will query the database tables to create reports for the bank.

mysql> CREATE USER 'bob'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_BOB_PASSWORD';

Query the mysql.user table to make sure that the users are created successfully.

mysql> SELECT User FROM mysql.user;

Confirm the users from the list shown below.

+------------------+
| User             |
+------------------+
| ...              |
| andrew           |
| bob              |
| james            |
| mary             |
| raphael          |
| ...              |
+------------------+

Assign the Users to the System Roles

You now have system roles and users. In this step, you'll assign users to their respective roles using the syntax below.

mysql> GRANT '[ROLE NAME]'@'[HOSTNAME]' TO '[USERNAME]'@'[HOSTNAME]';

Run the command below to assign the users to your roles.

Assign user mary to the system_developer role.

mysql> GRANT 'system_developer'@'localhost' TO 'mary'@'localhost';

Then, match user raphael to the `bank_manager' role.

mysql> GRANT 'bank_manager'@'localhost' TO 'raphael'@'localhost';

Ensure user james and andrew are merged to the bank_clerk role.

mysql> GRANT 'bank_clerk'@'localhost' TO 'james'@'localhost';
mysql> GRANT 'bank_clerk'@'localhost' TO 'andrew'@'localhost';

Associate user bob to the bank_auditor role.

mysql> GRANT 'bank_auditor'@'localhost' TO 'bob'@'localhost';

Once you've matched the users to their respective roles, run the commands below to make them active.

mysql> SET DEFAULT ROLE ALL TO 'mary'@'localhost';
mysql> SET DEFAULT ROLE ALL TO 'raphael'@'localhost';
mysql> SET DEFAULT ROLE ALL TO 'james'@'localhost';
mysql> SET DEFAULT ROLE ALL TO 'andrew'@'localhost';
mysql> SET DEFAULT ROLE ALL TO 'bob'@'localhost';

Exit from the MySQL interface:

mysql> QUIT;

Test the System Permissions

Finally, you'll log in with the new user's accounts and check whether the granted permissions work as expected.

To start the permission verification process, log in as user bob the auditor.

mysql> mysql -u bob -p

Enter the password for user bob and run the command below to check the current active role.

mysql> SELECT current_role();

As you can see below, bob is current logged in as a bank_auditor;

+----------------------------+
| current_role()             |
+----------------------------+
| `bank_auditor`@`localhost` |
+----------------------------+
1 row in set (0.00 sec)

Execute a SELECT command on the two tables and see if the permissions work.

First switch to the database bank;

mysql> USE bank;

Then, as an auditor, run the below command to see the actual names of clients who've made the deposits and withdrawals for reporting purposes.

mysql> SELECT
       clients_transactions.client_id,
       first_name,
       last_name,
       transaction_id,
       transaction_date
       FROM clients_transactions
       LEFT JOIN clients
       ON clients_transactions.client_id = clients.client_id;

As an auditor, your report should be displayed as shown below.

+-----------+------------+-----------+----------------+------------------+
| client_id | first_name | last_name | transaction_id | transaction_date |
+-----------+------------+-----------+----------------+------------------+
|         1 | JOHN       | DOE       |              1 | 2021-01-21       |
|         2 | MARY       | SMITH     |              2 | 2021-01-21       |
|         3 | STEVE      | MARK      |              3 | 2021-01-21       |
|         4 | STEVE      | MARK      |              4 | 2021-01-21       |
+-----------+------------+-----------+----------------+------------------+
4 rows in set (0.00 sec)

Next, test if an insert command works.

mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('1', '2021-01-21', 'D', '3.00');

You should get an error since the bank_auditor role does not have an INSERT permission.

ERROR 1142 (42000): INSERT command denied to user 'bob'@'localhost' for table 'clients_transactions'

Exit from the MySQL command-line interface.

mysql> QUIT;

Next, log in as a bank_clerk. You can test this by signing in as user james;

mysql> mysql -u james -p

Enter the password for user james and hit Enter to proceed. Next, check the currently active role.

mysql> SELECT current_role();

You should be signed in as a bank-clerk as shown below:

+--------------------------+
| current_role()           |
+--------------------------+
| `bank_clerk`@`localhost` |
+--------------------------+
1 row in set (0.00 sec)

Try to enter a new client to the clients table.

mysql> USE bank;
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('FRANK', 'MANU', '4TH STREET, TUCSON, AZ DEF');

Your INSERT query should be successful.

Query OK, 1 row affected (0.01 sec)

Similarly, insert a value to the clients_transactions table.

mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('1', '2021-01-21', 'D', '3.00');

The new activity should be recorded in the database as well.

Query OK, 1 row affected (0.00 sec)

Remember, as a member of the bank_clerk role, you are not allowed to delete transactions. Try the same and see what happens.

mysql> DELETE FROM clients where client_id = '1';

You'll now get the error below.

ERROR 1142 (42000): DELETE command denied to user 'james'@'localhost' for table 'clients'

Exit from the MySQL server.

mysql> QUIT;

Next, log in as raphael and test the bank_manager permissions.

mysql> mysql -u raphael -p

Enter the password for user raphael and press Enter to continue. Next, check your current role in the database.

mysql> SELECT current_role();

The output below confirms that you're currently logged in with permissions for the bank_manger role.

+----------------------------+
| current_role()             |
+----------------------------+
| `bank_manager`@`localhost` |
+----------------------------+
1 row in set (0.00 sec)

Switch to the bank database.

mysql> USE bank;

Try deleting the first entry in the clients_transactions table.

mysql> DELETE FROM clients_transactions where transaction_id = '1';

The transactions should now be deleted without any problem since you've DELETE permissions.

Query OK, 1 row affected (0.01 sec)

However, as a bank manager, you're not allowed to change the database schema. Confirm this by trying to drop the entire clients_transactions table.

mysql> DROP TABLE clients_transactions;

You should see the error below.

mysql> ERROR 1142 (42000): DROP command denied to user 'raphael'@'localhost' for table 'clients_transactions'

Exit from the MySQL database.

mysql> QUIT;

Finally, log in as user mary. Remember, mary belongs to the role system_developer that has full developer options to the database.

mysql> mysql -u mary -p

Key in the password for user mary and press Enter. Then, confirm the active role.

mysql> SELECT current_role();

The output below confirms that indeed, mary belongs to the role system_developer.

+--------------------------------+
| current_role()                 |
+--------------------------------+
| `system_developer`@`localhost` |
+--------------------------------+
1 row in set (0.00 sec)

As user mary, you have full permissions to the bank database. Switch to the database and try dropping the clients_transactions table.

mysql> USE bank;
mysql> DROP TABLE clients_transactions;

The query should run without any problem, as shown below, because you are the super-admin of the database.

Query OK, 0 rows affected (0.03 sec)

Conclusion

In this tutorial, you've gone through the steps of creating roles and assigning them to users. You have also seen how to grant permissions to the roles to manage your database security better. Although this guide is for demonstration purposes, you may use the code in this guide to better your MySQL applications.