Introduction to MySQL roles
Typically, you have a number of users with the same set of privileges. Previously, the only way to grant and revokeprivileges to multiple users is to change privileges of each user individually, which is time-consuming.
To make it easier, MySQL provided a new object called role that is a named collection of privileges.
If you want to grant the same set of privileges to multiple users, you should do it as follows:
- First, create a new role.
- Second, grant privileges to the role.
- Third, grant the role to the users.
In case you want to change the privileges of the users, you need to change the privileges of the granted role only. The changes will take effect to all users to which the role granted.
MySQL role example
First, create a new database named CRM, which stands for customer relationship management.
CREATE DATABASE crm;
Next, switch to the crm database:
USE crm;
Then, create customer table inside the CRM database.
CREATE TABLE customer(
id INT PRIMARY KEY AUTO_INCREMENT,
first_name varchar(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL,
email VARCHAR(255)
);
After that, insert data into the customer table.
INSERT INTO customer(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'),
('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
INSERT INTO customer(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'),
('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Finally, verify the insert by using the following SELECT statement:
SELECT * FROM customer;
+----+------------+-----------+----------------+-----------------------------+
| id | first_name | last_name | phone | email |
+----+------------+-----------+----------------+-----------------------------+
| 1 | John | Doe | (408)-987-7654 | john.doe@mysqltutorial.org |
| 2 | Lily | Bush | (408)-987-7985 | lily.bush@mysqltutorial.org |
+----+------------+-----------+----------------+-----------------------------+
2 rows in set (0.00 sec)
SELECT * FROM customer;
+----+------------+-----------+----------------+-----------------------------+
| id | first_name | last_name | phone | email |
+----+------------+-----------+----------------+-----------------------------+
| 1 | John | Doe | (408)-987-7654 | john.doe@mysqltutorial.org |
| 2 | Lily | Bush | (408)-987-7985 | lily.bush@mysqltutorial.org |
+----+------------+-----------+----------------+-----------------------------+
2 rows in set (0.00 sec)
Creating roles
Suppose you develop an application that uses the CRM database. To interact with the CRM database, you need to create accounts for developers who need full access to the database. In addition, you need to create accounts for users who need only read access and others who need both read/write access.
To avoid granting privileges to each user account individually, you create a set of roles and grant the appropriate roles to each user account.
To create new roles, you use CREATE ROLE statement:
CREATE ROLE crm_dev, crm_read, crm_write;
The role name is similar to user account that consists of user and host parts: role_name@host_name.
If you omit 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 following statement grants all privileges to crm_dev role:
GRANT ALL ON crm.* TO crm_dev;
GRANT ALL ON crm.* TO crm_dev;
The following statement grants SELECT privilege to crm_read role:
GRANT SELECT ON crm.* TO crm_read;
GRANT SELECT ON crm.* TO crm_read;
The following statement grants INSERT, UPDATE, and DELETE privileges to crm_write role:
GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;
GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;
Assigning roles to user accounts
Suppose 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 create new users, you use CREATE USER statements as follows:
-- developer user
CREATE USER crm_dev1@localhost IDENTIFIED BY 'Secure$1782';
-- read access user
CREATE USER crm_read1@localhost IDENTIFIED BY 'Secure$5432';
-- read/write users
CREATE USER crm_write1@localhost IDENTIFIED BY 'Secure$9075';
CREATE USER crm_write2@localhost IDENTIFIED BY 'Secure$3452';
To assign roles to users, you use GRANT statement:
GRANT crm_dev TO crm_dev1@localhost;
GRANT crm_read TO crm_read1@localhost;
GRANT crm_read, crm_write TO crm_write1@localhost, crm_write2@localhost;
Note that the GRANT statement for the crm_write1@localhost and crm_write2@localhost accounts grant both crm_read and crm_write roles.
To verify the role assignments, you use the SHOW GRANTS statement as the following example:
SHOW GRANTS FOR crm_dev1@localhost;
SHOW GRANTS FOR crm_dev1@localhost;
The statement returned the following result set:
+-----------------------------------------------+
| Grants for crm_dev1@localhost |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost` |
| GRANT `crm_dev`@`%` TO `crm_dev1`@`localhost` |
+-----------------------------------------------+
2 rows in set (0.02 sec)
+-----------------------------------------------+
| Grants for crm_dev1@localhost |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost` |
| GRANT `crm_dev`@`%` TO `crm_dev1`@`localhost` |
+-----------------------------------------------+
2 rows in set (0.02 sec)
As you can see, it just returned granted roles. To show the privileges that roles represent, you use the USING clause with the name of the granted roles as follows:
SHOW GRANTS FOR crm_write1@localhost USING crm_write;
The statement returns the following output:
+---------------------------------------------------------------------+
| Grants for crm_write1@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_write1`@`localhost` |
| GRANT INSERT, UPDATE, DELETE ON `crm`.* TO `crm_write1`@`localhost` |
| GRANT `crm_read`@`%`,`crm_write`@`%` TO `crm_write1`@`localhost` |
+---------------------------------------------------------------------+
+---------------------------------------------------------------------+
| Grants for crm_write1@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_write1`@`localhost` |
| GRANT INSERT, UPDATE, DELETE ON `crm`.* TO `crm_write1`@`localhost` |
| GRANT `crm_read`@`%`,`crm_write`@`%` TO `crm_write1`@`localhost` |
+---------------------------------------------------------------------+
Setting default roles
Now if you connect to the MySQL using the crm_read1 user account and try to access the CRMdatabase:
>mysql -u crm_read1 -p
Enter password: ***********
mysql>USE crm;
The statement issued the following error message:
ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'
ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'
This is because when you granted roles to a user account, it did not automatically make the roles to become active when the user account connects to the database server.
If you invoke the CURRENT_ROLE() function:
SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
It returned NONE, meaning no active roles.
To specify which roles should be active each time a user account connects to the database server, you use the SET DEFAULT ROLE statement.
The following statement set the default for the crm_read1@localhost account all its assigned roles.
SET DEFAULT ROLE ALL TO crm_read1@localhost;
SET DEFAULT ROLE ALL TO crm_read1@localhost;
Now, if you connect to the MySQL database server using the crm_read1 user account and invoke the CURRENT_ROLE() function:
>mysql -u crm_read1 -p
Enter password: ***********
>SELECT CURRENT_ROLE();
>mysql -u crm_read1 -p
Enter password: ***********
>SELECT CURRENT_ROLE();
You will see the default roles for crm_read1 user account.
+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+
1 row in set (0.00 sec)
+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+
1 row in set (0.00 sec)
You can test the privileges of crm_read account by switching the current database to CRM, executing a SELECT statement and a DELETE statement as follows:
mysql> use crm;
Database changed
mysql> SELECT COUNT(*) FROM customer;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> DELETE FROM customer;
ERROR 1142 (42000): DELETE command denied to user 'crm_read1'@'localhost' for table 'customer'
mysql> use crm;
Database changed
mysql> SELECT COUNT(*) FROM customer;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> DELETE FROM customer;
ERROR 1142 (42000): DELETE command denied to user 'crm_read1'@'localhost' for table 'customer'
It worked as expected. When we issued the DELETE statement, we received an error because crm_read1 user account has read access only.
Setting active roles
A user account can modify the current user’s effective privileges within the current session by specifying which granted role are active.
The following statement set the active role to NONE, meaning no active role.
SET ROLE NONE;
To set active roles to all granted role, you use:
SET ROLE ALL;
SET ROLE ALL;
To set active roles to default roles that set by the SET DEFAULT ROLE statement, you use:
SET ROLE DEFAULT;
SET ROLE DEFAULT;
To set active named roles, you use:
SET ROLE granted_role_1, granted_role_2, ...
SET ROLE granted_role_1, granted_role_2, ...
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 temporarily make all read/write users read only, you change the crm_write role as follows:
REVOKE INSERT, UPDATE, DELETE ON crm.* FROM crm_write;
To restore the privileges, you need to re-grant them as follows:
GRANT INSERT, UPDATE, DELETE ON crm.* FOR crm_write;
GRANT INSERT, UPDATE, DELETE ON crm.* FOR crm_write;
Removing roles
To remove one or more roles, you use the DROP ROLE statement as follows:
DROP ROLE role_name, role_name, ...;
Like REVOKE statement, the DROP ROLE statement revokes roles from every user account to which it was granted.
For example, to remove the crm_read, crm_write roles, you use the following statement:
DROP ROLE crm_read, crm_write;
DROP ROLE crm_read, crm_write;
Copying privileges from a user account to another
MySQL treats user account like a role, therefore, you can grant a user account to another user account like granting a role to that user account. This allows you to copy privileges from a user to another user.
Suppose you need another developer account for the CRM database:
First, create the new user account:
CREATE USER crm_dev2@localhost IDENTIFIED BY 'Secure$6275';
Second, copy privileges from the crm_dev1 user account to crm_dev2 user account as follows:
GRANT crm_dev1@localhost TO crm_dev2@localhost;
GRANT crm_dev1@localhost TO crm_dev2@localhost;
In this tutorial, you have learned how to use MySQL roles to make it easier to manage privileges of user accounts.
