MySQL - DROP ROLE Statement

---

---

MySQL DROP ROLE Statement

You can drop/delete one or more existing roles using the DROP ROLE Statement. To execute this statement, you need to have CREATE USER or DROP ROLE privilege.

Syntax

Following is the syntax of the DROP USER statement −

DROP ROLE [IF EXISTS] role_name ...

Where, role_name is the name of the MySQL user you need to delete.

Example

Assume we have created a role named TestRole_ReadOnly using the CREATE ROLE statement as follow −

CREATE ROLE 'TestRole_ReadOnly';

Now, let’s grant read only privilege to the created role using the GRANT statement as −

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

Then, you can GRANT the created role to a user as follows −

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT 'TestRole_ReadOnly' TO 'newuser'@'localhost';

You can retrieve the list of roles granted to all the users using the following query −

SELECT * FROM mysql.role_edges;

Output

Following is the output of the above query −

FROM_HOST	FROM_USER	TO_HOST	TO_USER	WITH_ADMIN_OPTION
%	TestRole_ReadOnly	localhost	newuser	N

Following query deletes the above created role −

DROP ROLE 'TestRole_ReadOnly';

Since we have removed the created role if you verify the list of roles again you will get an empty set as shown below −

SELECT * FROM mysql.role_edges;
Empty set (0.00 sec)

Removing Multiple roles

You can also delete multiple roles at once using the DROP ROLE statement. If we have created two roles as −

CREATE ROLE 'MyAdmin', 'MyDeveloper';

Following query drops the above created roles at once −

DROP ROLE 'MyAdmin', 'MyDeveloper';

The IF EXISTS clause

If you try to drop a MySQL role that doesn’t exist error will be generated as shown below −

DROP ROLE demo@localhost;
ERROR 1396 (HY000): Operation DROP ROLE failed for 'demo'@'localhost'

If you use the IF EXISTS clause along with the DROP ROLE statement as shown below, the specified role will be dropped and if a role with the given name doesn’t exist, the query will be ignored.

DROP ROLE IF EXISTS demo;