CREATE ROLE

CREATE ROLE

To create a role:

CREATE ROLE role1;

OR REPLACE

To create a role if it does not exist, or replace the role if it does exist, use the OR REPLACE clause:

CREATE OR REPLACE ROLE role1;

IF NOT EXISTS

By default, if you try to create a role with a name that already exists, an error is raised.

To prevent an error if the named role is already present, add the IF NOT EXISTS clause:

CREATE ROLE IF NOT EXISTS role1;

If the specified role does not exist, it will be created. If the specified role already exists, the query does not raise an error.

WITH ADMIN

To create a role with a specific administrator user, add the WITH ADMIN clause:

CREATE ROLE role1 WITH ADMIN 'root'@'127.0.0.1';

Show Applicable Roles

Users can list the roles available to them by querying the information_schema.applicable_roles table:

SELECT * FROM information_schema.applicable_roles;

+----------------+-----------+--------------+------------+
| GRANTEE        | ROLE_NAME | IS_GRANTABLE | IS_DEFAULT |
+----------------+-----------+--------------+------------+
| root@127.0.0.1 | role1     | YES          | NO         |
+----------------+-----------+--------------+------------+

Show Enabled Roles

Users can list the roles activated in their current session by querying the information_schema.enabled_roles table:

SELECT * FROM information_schema.enabled_roles;

Users can also execute the CURRENT_ROLE() function:

SELECT CURRENT_ROLE();

+----------------+
| CURRENT_ROLE() |
+----------------+
| role1          |
+----------------+

Show Cluster-wide Roles

Administrators can list all created roles by querying the system.roles system table:

SELECT * FROM system.roles;

+---------------------+----------+
| role                | rolename |
+---------------------+----------+
| 7198234908421821441 | role1    |
+---------------------+----------+