MYSQL - CREATE ROLE Statement

MySQL CREATE ROLE Statement

A role in MySQL is a set of privileges with name. You can create one or more roles in MySQL using the CREATE ROLE statement. To create a role, you need to have CREATE ROLE or CREATE USER privilege.

Syntax

Following is the syntax of the CREATE ROLE Statement −

CREATE ROLE [IF NOT EXISTS] desiredname

Where, desiredname is the name of the role you need to create.

Example

Following query creates a role named TestRole_ReadOnly.

CREATE ROLE 'TestRole_ReadOnly';

Now, lets 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';

Example

You can create a role by specifying the host −

CREATE ROLE samplerole@localhost;

Creating Multiple roles

You can also create multiple roles at once as shown below −

CREATE ROLE 'MyAdmin', 'MyDeveloper';

The IF NOT EXISTS clause

If you try to create a role with an existing name an error will be generated −

CREATE ROLE 'testrole@localhost';
CREATE ROLE 'testrole@localhost';
ERROR 1396 (HY000): Operation CREATE ROLE failed for 
'testrole@localhost'@'%'

If you use the IF NOT EXISTS clause along with the CREATE ROLE statement as shown below a new role will be created and if a role with the given name, already exists the query will be ignored.

CREATE ROLE IF NOT EXISTS 'testrole@localhost';