Roles for MariaDB Xpand

This page is part of MariaDB's Documentation.

The parent of this page is: Roles

Overview

A role is a named set of privileges. MariaDB Xpand uses roles to control access to database objects:

You can grant privileges to roles and revoke privileges from roles
Xpand allows a user to be granted multiple roles, and the user can activate multiple roles at once
When you grant a role to a user, the user can use all privileges held by the role
When you grant a role to another role, the role inherits all privileges held by the granted role, and users with the role can use all privileges held by both roles

Compatibility

MariaDB Xpand 6.1

Create Roles

To create one or more roles, use the CREATE ROLE statement:

CREATE ROLE administrator;

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

CREATE ROLE IF NOT EXISTS employee, contractor, manager;

Privileges and Roles

Grant Privileges to a Role

To grant privileges to one or more roles, specify the privileges to grant to the roles using the GRANT statement:

GRANT SELECT ON *.* TO administrator;

Revoke Privileges from a Role

To revoke privileges from a role, specify the privileges to revoke from the role using the REVOKE statement:

REVOKE SELECT ON *.* FROM administrator;

Grant Roles to a Role

To grant roles to one or more roles, specify the roles to grant to the roles using the GRANT statement:

GRANT employee TO manager;

When you grant a role to another role, the role inherits all privileges held by the granted role, and users with the role can use all privileges held by both roles.

Revoke Roles from a Role

To revoke privileges from a role, specify the privileges to revoke from the role using the REVOKE statement:

REVOKE employee FROM manager;

Roles and Users

Grant a Role to a User

To grant a role to one or more users, use the GRANT statement:

GRANT employee TO alice, sofia;

When you grant a role to a user, the user can use all privileges held by the role.

When granting a role to a user, the WITH ADMIN OPTION option allows the user to also grant that role to other users (similar to WITH GRANT OPTION):

GRANT contractor TO alice WITH ADMIN OPTION;

Revoke a Role from a User

To revoke a role from a user, use the REVOKE statement:

REVOKE employee FROM sofia;

To disable a user's ability to grant a role to other users, add ADMIN OPTION:

REVOKE ADMIN OPTION FOR contractor FROM alice;

Set the Default Role for a User

To set the default role for a user, use the SET DEFAULT ROLE statement:

SET DEFAULT ROLE employee FOR alice;
SHOW GRANTS FOR alice;

+-----------------------------------------------------+
| Grants for alice@localhost                          |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'%'                   |
| GRANT `employee` TO 'alice'@'%'                     |
| SET DEFAULT ROLE `employee` FOR 'alice'@'%'         |
+-----------------------------------------------------+

To activate all of the user's roles by default, set the default role to ALL:

SET DEFAULT ROLE ALL FOR alice;

To remove all of the user's default roles, set the default role to NONE:

SET DEFAULT ROLE NONE FOR alice;

Activate Roles

Set a Role

To activate one or more roles for a user, use the SET ROLE statement:

SET ROLE employee, contractor;

Set All Roles

To activate all roles granted to a user, use the SET ROLE ALL statement:

SET ROLE ALL;
SELECT CURRENT_ROLE();

+------------------------------------+
| CURRENT_ROLE()                     |
+------------------------------------+
| employee, contractor               |
+------------------------------------+

If any additional roles are granted to the user, the additional roles are not added until one of the following occurs:

SET ROLE ALL is called again
The user logs out and logs back in

Deactivate All Roles for a User

To deactivate all roles granted to a user, use the SET ROLE NONE statement:

SET ROLE NONE;
SELECT CURRENT_ROLE();

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

Drop a Role

To delete a role, use the DROP ROLE statement:

DROP ROLE employee;

If any users are granted the role, the grant will be revoked:

SHOW GRANTS FOR alice;

+-----------------------------------------+
| Grants for alice@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'%'       |
+-----------------------------------------+

Show Roles

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;

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();

Show Cluster-wide Roles

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

SELECT * FROM system.roles;

+---------------------+------------------+
| role                | rolename         |
+---------------------+------------------+
| 7189494665609938945 | account_managers |
| 7189854484132978689 | customer_support |
| 7189856788787845121 | contractors      |
+---------------------+------------------+

Name Resolution for Users and Roles

Role names and user names are frequently mixed in the same command context, so Xpand has rules to distinguish between them:

When a hostname is specified (such as in alice@%), it can only be a user name, so Xpand will only search users.
However, when no hostname is specified (such as in admin), the name is ambiguous. In this case, Xpand searches roles first, and then searches users. When users are searched, the default hostname (%) is used.

Consider the following example:

GRANT SELECT ON *.*
   TO name1;

When the above is executed, Xpand performs the following actions:

Xpand searches for a role named name1
If the role does not exist, Xpand searches for a user named name1@%
If the user does not exist, Xpand creates the user named name1@%

Information Schema

The following information schema tables are related to roles:

Table	Contents
`applicable_roles`	Shows all roles available to the current user.
`enabled_roles`	Shows all roles activated in the current session.

System Tables

The following system tables are related to user accounts, roles, and privileges:

System Table	Description
`roles`	Stores the names of all roles on the Xpand cluster.
`roles_mapping`	Stores the mapping between roles and and user specifications, including whether the user was granted `WITH ADMIN` for the role.
`session_roles`	Stores the list of roles available to a session.
`users`	Stores user account details
`user_acl`	Stores global, database, and table privileges for user accounts
`user_role_links`	Stores a mapping between roles and user specifications, including whether the role is a default role for the user.
`user_routine_acl`	Stores routine privileges for user accounts