Roles were introduced in MariaDB 10.0.5.
CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role [WITH ADMIN {CURRENT_USER | CURRENT_ROLE | user | role}]
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.
Before MariaDB 10.1.13, the CREATE ROLE
statement was not permitted in prepared statements.
For valid identifiers to use as role names, see Identifier Names.
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.
The OR REPLACE
clause was added in MariaDB 10.1.3
If the optional OR REPLACE
clause is used, it acts as a shortcut for:
DROP ROLE IF EXISTS name; CREATE ROLE name ...;
The IF NOT EXISTS
clause was added in MariaDB 10.1.3
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.
CREATE ROLE journalist; CREATE ROLE developer WITH ADMIN lorinda;
The OR REPLACE
and IF NOT EXISTS
clauses:
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 | +-------+------+---------------------------------------------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/create-role/