CREATE ROLE

Overview

In 23.09, 6.1:

Creates a role.

In 6.1:

Creates a role.

In 6.0, 5.3:

Not present

USAGE

Common Syntax:

CREATE [OR REPLACE] ROLE
   [IF NOT EXISTS] <role_name> [, <role_name>] ...
   [WITH ADMIN <user_name>]

DETAILS

This statement creates a role.

For Xpand, all objects are case-insensitive.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

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    |
+---------------------+----------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Added in MariaDB Xpand 6.1.0.

6.0

  • Not present.

5.3

  • Not present.

Release Series

History

6.0

  • Not present.

5.3

  • Not present.

Release Series

History

6.1

  • Added in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES