Skip to main content

REVOKE ROLE

Removes a role from a user.

Syntax

REVOKE ROLE <role_name> FROM { USER <user_name> }

Examples

Grant Privileges to a User

Create a user:

mysql>
create user user1 identified by 'abc123';

Grant the ALL privilege on all existing tables in the default database to the user user1:

mysql>
grant all on default.* to user1;
mysql>
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:

mysql>
grant select on mydb.* to role role1;

Show the grants for the role:

mysql>
show grants for role role1;
+-------------------------------------+
| Grants |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+

Grant a Role to a User

User user1 grants are:

mysql>
show grants for user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%' |
+-----------------------------------------+

Role role1 grants are:

mysql>
show grants for role role1;
+-------------------------------------+
| Grants |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+

Grant role role1 to user user1:

mysql>
 grant role role1 to user1;

Now, user user1 grants are:

mysql>
show grants for user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%' |
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-----------------------------------------+

Revoke Role From a User

mysql>
revoke role role1 from user user1;
mysql>
show grants for user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%' |
+-----------------------------------------+