Simplify Privilege Management Using Roles in MySQL

Typically, there may be many users in an online MySQL database server, and some of these users may have the same permissions.

As a database administrator or maintainer, you may grant the same permissions to multiple users. This process is time-consuming and prone to errors.

MySQL roles allow you to simplify the authorization process. You just need to create a role for users with the same permissions and assign the role to those users.

You can also create multiple roles to authorize different users. You can also assign multiple different roles to a user.

MySQL role syntax

If you want to grant the same set of permissions to multiple users, follow these steps:

1. Create a new role.
2. Grant permissions to the role.
3. Grant the role to the user.

Create role Syntax

This is the syntax for creating roles:

CREATE ROLE role_name1 [, role_name2 [, ...]];

A role name is similar to a user account and consists of two parts: name and host. If the host part is omitted, it defaults to % that is any host.

If you need to create multiple roles in one statement, separate the different role names with commas.

Drop role syntax

The following is the syntax for dropping roles:

DROP ROLE role_name1 [, role_name2 [, ...]];

If you need to remove multiple roles in one statement, separate the different role names with commas.

Grant privileges to a roles

To authorize a role, use the following GRANT statement :

GRANT privilege_type [,privilege_type],..
ON privilege_object
TO role_name;

Revoke privileges from a role

To revoke permissions from a role, use the following REVOKE statement :

REVOKE
   priv1 [, priv2 [, ...] ]
ON privilege_object
FROM role_name;

MySQL role Examples

This example shows the complete steps to authorize a user using roles.

Precondition

1. Create a database named sqlizdb:

   CREATE DATABASE sqlizdb;
   

2. Select sqlizdb database as default :

   USE sqlizdb;
   

3. Create a new table named test_table in the database sqlizdb:

   CREATE TABLE test_table(
       id int AUTO_INCREMENT PRIMARY KEY,
       txt varchar(100) NOT NULL
   );
   

4. Insert a row into the test_table table :

   INSERT INTO test_table(txt)
   VALUES('Hello World.');
   

5. Query rows from the test_table table :

   SELECT * FROM test_table;
   

   Here is the output:

   +----+--------------+
   | id | txt          |
   +----+--------------+
   |  1 | Hello World. |
   +----+--------------+

Creating a Role

Suppose that there will be many users in this sqlizdb database, and the users needs the following three types privileges:

• Full access to the database for developers.
• Read and write access to the database for maintainers.
• Read access to the database for browser.

You can create three roles with different privileges and grant each user account the appropriate role.

To create the three roles, use the CREATE ROLE statement :

CREATE ROLE
    sqlizdb_dev,
    sqlizdb_read,
    sqlizdb_write;

Here, we created three roles:

• Role sqlizdb_dev is for developers.
• Role sqlizdb_read is for browser.
• Role sqlizdb_write is for maintainers.

The host part is omitted here, and the these roles can be used on any host.

Grant privileges to roles

Roles are used sqlizdb_dev by developers and should grant all permissions :

GRANT ALL ON sqlizdb.* TO sqlizdb_dev;

The following statement grants SELECT permissions to the sqlizdb_read role:

GRANT SELECT ON sqlizdb.* TO sqlizdb_read;

The following statement grants INSERT, UPDATE and DELETE permissions to the sqlizdb_write role:

GRANT INSERT, UPDATE, DELETE ON sqlizdb.* TO sqlizdb_write;

Assign roles to user accounts

Let’s say you need one user account as developer, one user account with read-only access, and two user accounts with read/write access.

Please use the following statements to create a new user :

-- 开发者用户
CREATE USER sqlizdb_dev_user1@'%' IDENTIFIED BY 'SqLiZ9879123!';
-- 只读用户
CREATE USER sqlizdb_read_user1@'%' IDENTIFIED BY 'SqLiZ9879123!';
-- 读写用户
CREATE USER sqlizdb_write_user1@'%' IDENTIFIED BY 'SqLiZ9879123!';
CREATE USER sqlizdb_write_user2@'%' IDENTIFIED BY 'SqLiZ9879123!';

To assign a role to a user, use the GRANT statement. The following statements grant roles to 4 users respectively:

GRANT sqlizdb_dev TO sqlizdb_dev_user1@'%';
GRANT sqlizdb_read TO sqlizdb_read_user1@'%';
GRANT sqlizdb_read, sqlizdb_write
TO sqlizdb_write_user1@'%', sqlizdb_write_user2@'%';

To verify roles for users, use the SHOW GRANTS statement as following:

SHOW GRANTS FOR sqlizdb_dev_user1@'%';

+----------------------------------------------------+
| Grants for sqlizdb_dev_user1@%                     |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `sqlizdb_dev_user1`@`%`      |
| GRANT `sqlizdb_dev`@`%` TO `sqlizdb_dev_user1`@`%` |
+----------------------------------------------------+

As you can see, the statement simply returns the granted role. To display the permissions that a role represents, use a USING clause, as follows:

SHOW GRANTS FOR sqlizdb_write_user1@'%'
USING sqlizdb_write;

The statement returns the following output:

+---------------------------------------------------------------------------+
| Grants for sqlizdb_write_user1@%                                          |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqlizdb_write_user1`@`%`                           |
| GRANT INSERT, UPDATE, DELETE ON `sqlizdb`.* TO `sqlizdb_write_user1`@`%`  |
| GRANT `sqlizdb_read`@`%`,`sqlizdb_write`@`%` TO `sqlizdb_write_user1`@`%` |
+---------------------------------------------------------------------------+

Conclusion

In MySQL, you can use MySQL roles to simplify user permissions management.