In this blog, you will study how to use MySQL roles to make simpler the privilege managements.
Introduction to MySQL roles
Naturally, you have several users with the same set of privileges. Earlier, the only way to grant and revoke privileges to several users is to change privileges of each user independently, which is time-consuming.
To make it easier, MySQL provided a new object called role that is a named group of privileges.
If you want to grant the same set of privileges to multiple users, you should do it as follows:
First, make a new role.
Next, grant privileges to the role.
Next, grant the role to the users.
In case you want to modification the privileges of the users, you need to change the privileges of the granted role first. The changes will take outcome to all users to which the role granted.
MySQL role example
First, create a new database named CRM, which stands for customer relationship organization.
Next, switch to the crm database:
Then, create customer table inside the CRM database.
After that, insert data into the customer table.
Finally, verify the insert by using the subsequent SELECT statement:
Creating roles
Assume you develop an application that uses the CRM database. To interact with the CRM database, you need to generate accounts for developers who want full access to the database. In addition, you need to create accounts for users who want only read access and others who prerequisite both read/write access.
To avoid granting privileges to each user account separately, you create a set of roles and grant the appropriate roles to each user account.
To create new roles, you use CREATE ROLE statement:
The role name is similar to user account that consists of user and host parts: role_name@host_name.
If you overlook the host part, it will default to ‘%’ that mean any hosts.
Granting privileges to roles
To grant privileges to a role, you use GRANT statement. The subsequent statement grants all privileges to crm_dev role:
The subsequent statement grants SELECT privilege to crm_read role:
The subsequent statement grants INSERT, UPDATE, and DELETE privileges to crm_write role:
Assigning roles to user accounts
Assume you need one user account as the developer, one user account that can have read-only access and two user accounts that can have read/write access.
To make new users, you use CREATE USER statements as follows:
To assign roles to users, you use GRANT statement:
Note that the GRANT statement for the crm_write1@localhost and crm_write2@localhost accounts grant both crm_read and crm_write roles.
To confirm the role assignments, you use the SHOW GRANTS statement as the subsequent example:
The statement returned the following result set:
As you can see, it just returned granted roles. To show the privileges that roles characterize, you use the USING clause with the name of the granted roles as follows:
The statement returns the following output:
Set default roles
Now if you connect to the MySQL using the crm_read1 user account and try to access the CRM database:
The statement issued the subsequent error message:
This is because when you granted roles to a user account, it did not automatically make the roles to turn out to be active when the user account connects to the database server.
If you invoke the CURRENT_ROLE() function:
It returned NONE, meaning no active roles.
To lay down which roles should be active each time a user account connects to the database server, you use the SET DEFAULT ROLE statement.
The subsequent statement set the default for the crm_read1@localhost account all its allocated roles.
This day and age, if you connect to the MySQL database server using the crm_read1 user account and invoke the CURRENT_ROLE() function:
You can test the privileges of crm_read account by swapping the current database to CRM, performing a SELECT statement and a DELETE statement as follows:
It worked as expected. When we issued the DELETE statement, we received an error because crm_read1user account has read access only.
Setting active roles
A user account can modify the current user’s active privileges within the current session by requiring which granted role are active.
The subsequent statement set the active role to NONE, meaning no active role.
To set active roles to all granted role, you use:
To set active roles to default roles that set by the SET DEFAULT ROLE statement, you use:
To set active named roles, you use:
Revoking privileges from roles
To revoke privileges from a specific role, you use the REVOKE statement. The REVOKE statement takes effect not only the role but also any account granted the role.
For example, to for the time being make all read/write users read only, you change the crm_write role as follows:
To restore the privileges, you need to re-grant them as follows:
Removing roles
To eliminate one or more roles, you use the DROP ROLE statement as follows:
Like REVOKE statement, the DROP ROLE statement revokes roles from every user account to which it was granted.
For instance, to remove the crm_read, crm_write roles, you use the subsequent statement:
Copying privileges from a user account to another
MySQL treats user account like a role, hence, you can grant a user account to another user account like granting a role to that user account. This lets you to copy privileges from a user to one more user.
What if you need another developer account for the CRM database:
First, create the new user account:
Second, copy privileges from the crm_dev1 user account to crm_dev2 user account as follows:
In this blog, you have educated how to use MySQL roles to make it laid-back to manage privileges of user accounts.