GRANT ROLE to a User
Granting a role to a user enables the user to perform all operations allowed by the role (through the access privileges granted to the role).
Syntax
GRANT ROLE <role_name> TO { USER <user_name> }
Examples
Grant Privileges to a User
Create a user:
CREATE USER user1 IDENTIFIED BY 'abc123';
Grant the ALL privilege on all existing tables in the default database to the user user1:
GRANT ALL ON default.* TO user1;
SHOW GRANTS FOR user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
+-----------------------------------------+
Grant Privileges to a Role
Grant the SELECT privilege on all existing tables in the mydb database to the role role1:
Create role:
CREATE ROLE role1;
Grant privileges to the role:
GRANT SELECT ON mydb.* TO ROLE role1;
Show the grants for the role:
SHOW GRANTS FOR ROLE role1;
+-------------------------------------+
| Grants |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+
Grant a Role to a User
User user1 grants are:
SHOW GRANTS FOR user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%' |
+-----------------------------------------+
Role role1 grants are:
SHOW GRANTS FOR ROLE role1;
+-------------------------------------+
| Grants |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+
Grant role role1 to user user1:
GRANT ROLE role1 TO user1;
Now, user user1 grants are:
SHOW GRANTS FOR user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%' |
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-----------------------------------------+