Privileges

Overview

Privileges, also referred to as grants, are used to enforce security. Privileges are assigned with a GRANT statement to allow access to database objects and to define what the user is authorized to do with the database object. The REVOKE statement is used to remove privileges.

MariaDB Enterprise Server authorization is issued as privilege grants to user accounts, which define the specific operations the account can perform and the resources they can access.

Privileges can be aggregated into ROLES to make it easy to GRANT a common set of privileges to a number of users.

Different versions of MariaDB Enterprise Server may have different grants. See the All Versions Comparison Table.

For a comparison of changes made to privileges between MariaDB Enterprise Server 10.4 and MariaDB Enterprise Server 10.5, see What's New in MariaDB Server 10.5.

MariaDB Xpand uses privileges to control access to database objects:

  • Xpand supports granular access control with privileges at multiple scopes

  • Xpand's privilege system is designed to be compatible with MariaDB Enterprise Server and MySQL

For a full list of privileges supported by Xpand, see "Privileges for MariaDB Xpand".

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Grant Privileges

To grant the ALTER privilege on the example-table to test@192.0.2.0/24:

GRANT ALTER ON example-database.example-table
TO test@192.0.2.0/24;

Grant Privileges

Privileges can be granted to a user account or to a role. Only users with the GRANT OPTION can grant privileges to another user. Users can only grant privileges that their own user account or active role(s) has been granted.

To grant privileges to a user account, execute the GRANT statement and specify the user account details:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON *.*
   TO 'USERNAME'@'HOSTNAME';

Replace 'USERNAME'@'HOSTNAME' with the values for the specific user account.

To grant privileges to a role, execute the GRANT statement and specify the role details:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON *.*
   TO 'ROLENAME';

Replace 'ROLENAME' with the name of the specific role.

Revoke Privileges

To remove the ALTER privilege from test@192.0.2.0/24:

REVOKE ALTER ON example-database.example-table
TO test@192.0.2.0/24;

Revoke Privileges

Privileges can be revoked from a user account or from a role.

To revoke privileges from a user account, execute the REVOKE statement and specify the user account details:

REVOKE SELECT, INSERT, UPDATE, DELETE
   ON *.*
   FROM 'USERNAME'@'HOSTNAME';

Replace 'USERNAME'@'HOSTNAME' with the values for the specific user account.

To revoke privileges from a role, execute the REVOKE statement and specify the role details:

REVOKE SELECT, INSERT, UPDATE, DELETE
   ON *.*
   FROM 'ROLENAME';

Replace 'ROLENAME' with the name of the specific role.

Show Privileges

To show a user's privileges, execute the SHOW GRANTS statement.

To show privileges for the user logged into the current session, execute the statement without specifying any arguments:

SHOW GRANTS;

+-------------------------------------------------------------+
| Grants for root@192.0.2.10                                  |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+

To show privileges for a specific user, execute the statement and specify the user account details with the syntax FOR 'USERNAME'@'HOSTNAME':

SHOW GRANTS FOR 'app_user'@'192.0.2.%';

+------------------------------------------------------------------------------+
| Grants for app_user@192.0.2.%                                                |
+------------------------------------------------------------------------------+
| GRANT DELETE, INSERT, SELECT, UPDATE ON `app_db`.* TO 'app_user'@'192.0.2.%' |
+------------------------------------------------------------------------------+

Privilege Failures

Xpand raises an error message if an operation fails due to a lack of privileges:

SELECT * FROM hq_sales.invoices;

ERROR 1045 (HY000): [11281] Permission denied: User 'USERNAME'@'HOSTNAME' is missing SELECT on `hq_sales`.`invoices`.; transaction aborted

Privilege Scopes

Global Scope

To grant privileges at global scope, execute the GRANT statement and specify ON *.*:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON *.*
   TO 'USERNAME'@'HOSTNAME';

Replace 'USERNAME'@'HOSTNAME' with the values for the specific user account.

All privileges are applicable at global scope.

Database Scope

To grant privileges at database scope, execute the GRANT statement and specify ON DATABASE_NAME.*:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON DATABASE_NAME.*
   TO 'USERNAME'@'HOSTNAME';

Replace DATABASE_NAME with the name of the specific database.

Replace 'USERNAME'@'HOSTNAME' with the values for the specific user account.

The following privileges are applicable at database scope:

  • ALTER

  • ALTER ROUTINE

  • CREATE

  • CREATE ROUTINE

  • CREATE VIEW

  • DELETE

  • DROP

  • EVENT

  • EXECUTE

  • INDEX

  • INSERT

  • GRANT OPTION

  • LOCK TABLES

  • REFERENCES

  • SELECT

  • SHOW VIEW

  • TRIGGER

  • UPDATE

Table Scope

To grant privileges at table scope, execute the GRANT statement and specify ON DATABASE_NAME.TABLE_NAME:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON DATABASE_NAME.TABLE_NAME
   TO 'USERNAME'@'HOSTNAME';

Replace DATABASE_NAME and TABLE_NAME with the name of the specific database and table.

Replace 'USERNAME'@'HOSTNAME' with the values for the specific user account.

The following privileges are applicable at table scope:

  • ALTER

  • CREATE VIEW

  • CREATE

  • DELETE

  • DROP

  • GRANT OPTION

  • INDEX

  • INSERT

  • REFERENCES

  • SELECT

  • SHOW VIEW

  • TRIGGER

  • UPDATE

Column Scope

Xpand does not support column scope for privileges.

If GRANT is used to specify column privileges, an error is raised:

GRANT SELECT (COLUMN_LIST)
   ON DATABASE.TABLE
   TO 'USERNAME'@'HOSTNAME';

ERROR 1 (HY000): [24585] Bad parameter.: Column privileges are not supported

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

Xpand does not allow the system tables to be modified directly. GRANT and REVOKE must be used to modify user privileges.

Compatibility Privileges

Xpand accepts certain privileges for compatibility with MariaDB Enterprise Server and MySQL. These privileges are checked when a ALL PRIVILEGES are granted to a user, but those privileges do not have an effect in other cases.

Xpand accepts the following privileges for compatibility purposes:

SUPER Privilege

The SUPER privilege is intended for administrative users. Depending on the Xpand version, users with the SUPER privilege are able to read and modify most or all objects in the database. When a user does not require this level of access, MariaDB recommends granting more fine-grained privileges to users.

For each version of Xpand, the SUPER privilege works in the following way:

  • In Xpand 6.0 and before, the SUPER privilege implicitly includes the ALL [PRIVILEGES] privilege. When SUPER is granted to a user, that user effectively has all privileges on all database objects.

  • Starting with Xpand 6.1, the privilege system allows for more fine-grained control. The BINLOG ADMIN, CLUSTER ADMIN, CONNECTION ADMIN, REPAIR ADMIN, REPLICATION ADMIN, SET USER, and SYSTEM VARIABLES ADMIN privileges have been added, and the SUPER privilege has been redefined as a superset of those privileges. The SUPER privilege does not implicitly include the ALL [PRIVILEGES] privilege. If a user with SUPER privileges requires all privileges on all database objects, the user must also be granted the ALL [PRIVILEGES] privilege.

For additional information, see "SUPER Privilege".

OSAUTH Privilege

Xpand implements an OSAUTH privilege. When a local database user is granted this privilege, the user account can be authenticated by the OS. This allows the user to log into the database without a password as long as they have been authenticated as the corresponding OS user.

To grant a local database user account the OSAUTH privilege, execute the GRANT statement:

GRANT OSAUTH ON *.*
   TO 'USERNAME'@'localhost';

Replace USERNAME with the name of the specific database user account, which must have the same name as the corresponding OS user account.

By default, Xpand grants the OSAUTH privilege to some default database users:

  • The xpandm database user account is granted the OSAUTH privilege to allow clx to connect without a password

  • The xpand database user account is granted the OSAUTH privilege to allow statd and XpandGUI to connect without a password

For additional information, see "OSAUTH Privilege".

Special Cases

  • The _ and % wild cards are not supported in database name specifications.

  • The GRANT and REVOKE statements ignore values supplied for column_list and object_type

  • The GRANT statement does not support the REQUIRE clause.

  • The DEFINER and SQL SECURITY arguments for views are ignored.

  • The following GRANT WITH options are not supported and generate a syntax error:

    • MAX_QUERIES_PER_HOUR

    • MAX_UPDATES_PER_HOUR

    • MAX_CONNECTIONS_PER_HOUR

    • MAX_USER_CONNECTIONS

  • The RENAME USER statement is not supported.

  • The CURRENT_USER() and SESSION_USER() functions are not supported.