CREATE ROLE

Create new roles to simplify privilege management. Learn how to define a role that can be assigned to multiple users or other roles.

Syntax

CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role 
  [WITH ADMIN 
    {CURRENT_USER | CURRENT_ROLE | user | role}]

Description

The CREATE ROLE statement creates one or more MariaDB roles. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE ROLE creates a new row in the mysql.user table that has no privileges, and with the corresponding is_role field set to Y. It also creates a record in the mysql.roles_mapping table.

If any of the specified roles already exist, ERROR 1396 (HY000) results. If an error occurs, CREATE ROLE will still create the roles that do not result in an error. The maximum length for a role is 128 characters. Role names can be quoted, as explained in the Identifier names page. Only one error is produced for all roles which have not been created:

ERROR 1396 (HY000): Operation CREATE ROLE failed for 'a','b','c'

Failed CREATE or DROP operations, for both users and roles, produce the same error code.

PUBLIC and NONE are reserved, and cannot be used as role names. NONE is used to unset a role and PUBLIC has a special use in other systems, such as Oracle, so is reserved for compatibility purposes.

For valid identifiers to use as role names, see Identifier Names.

WITH ADMIN

The optional WITH ADMIN clause determines whether the current user, the current role or another user or role has use of the newly created role. If the clause is omitted, WITH ADMIN CURRENT_USER is treated as the default, which means that the current user will be able to GRANT this role to users.

OR REPLACE

If the optional OR REPLACE clause is used, it acts as a shortcut for:

DROP ROLE IF EXISTS name;
CREATE ROLE name ...;

IF NOT EXISTS

When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified role already exists. Cannot be used together with the OR REPLACE clause.

Examples

CREATE ROLE journalist;

CREATE ROLE developer WITH ADMIN lorinda@localhost;

Granting the role to another user. Only user lorinda@localhost has permission to grant the developer role:

SELECT USER();

+-------------------+
| USER()            |
+-------------------+
| henning@localhost |
+-------------------+
...

GRANT developer TO ian@localhost;

Access denied for user 'henning'@'localhost'

SELECT USER();

+-------------------+
| USER()            |
+-------------------+
| lorinda@localhost |
+-------------------+

GRANT m_role TO ian@localhost;

The OR REPLACE and IF NOT EXISTS clauses. The journalist role already exists:

CREATE ROLE journalist;
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'journalist'

CREATE OR REPLACE ROLE journalist;
Query OK, 0 rows affected (0.00 sec)

CREATE ROLE IF NOT EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;

+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1975 | Can't create role 'journalist'; it already exists |
+-------+------+---------------------------------------------------+

See Also

• Identifier Names

• Roles Overview

• DROP ROLE

_{This page is licensed: CC BY-SA / Gnu FDL}