Privileges for MariaDB Xpand

Granting Privileges

Xpand supports an access control system that is similar to that of MySQL. You can grant privileges globally (using ON *.* ), at the database level (ON <dbname>.*), or at the table level (ON <dbname>.<tablename>).

To modify privileges, use the GRANT and REVOKE commands, which differ from their MySQL equivalents as follows:

  • column_list and object_type are ignored.

  • The ssl options cipher issuer and subject are not supported

Note

Xpand does not allow SYSTEM tables to be modified directly. Use SQL to modify users and privileges.

To display permissions, issue the SHOW GRANTS statement. For example, to list permissions for the current user:

sql> show grants;
+-------------------------------------------------------------+
| Grants for root@10.2.2.243                                  |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+

To show grants for a specific user:

sql> show grants for sergei;
+-------------------------------------------------------------------------------------------------------+
| Grants for sergei@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sergei'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0FXXXXXXXXXXXXX32313728C250DBF' |
+-------------------------------------------------------------------------------------------------------+

Supported Privileges

Xpand supports the following privileges:

  • ALL [PRIVILEGES]: All Privileges with the exception of GRANT OPTION

  • ALTER

  • ALTER ROUTINE: Allow ALTER or DROP of stored routines (procedures and functions)

  • BINLOG ADMIN: Added in MariaDB Xpand 6.1.

  • CLUSTER ADMIN: Added in MariaDB Xpand 6.1.

  • CONNECTION ADMIN: Added in MariaDB Xpand 6.1.

  • CREATE: Allow CREATE TABLE and CREATE DATABASE

  • CREATE ROUTINE

  • CREATE TEMPORARY TABLES

  • CREATE USER: Allow CREATE USER and DROP USER

  • CREATE VIEW

  • DELETE

  • DROP: Allow DROP TABLE, DROP DATABASE, and DROP VIEW

  • EXECUTE

  • GRANT OPTION

  • INDEX: Allow CREATE INDEX and DROP INDEX

  • INSERT

  • PROCESS: The privilege enables use of SHOW PROCESSLIST and SHOW ENGINES

  • REFERENCES: The creation of a foreign key constraint requires the REFERENCES for the parent table

  • RELOAD: Enable use of FLUSH operations

  • REPAIR ADMIN: Added in MariaDB Xpand 6.1.

  • REPLICATION ADMIN: Added in MariaDB Xpand 6.1.

  • REPLICATION CLIENT: Allow SHOW MASTER STATUS and SHOW SLAVE STATUS

  • REPLICATION SLAVE: Allow reading binary logs

  • SELECT

  • SET USER: Added in MariaDB Xpand 6.1.

  • SHOW DATABASES

  • SHOW VIEW: Enables use of the SHOW CREATE VIEW statement

  • SHUTDOWN: Xpand recommends using clx dbstop instead

  • SUPER: Allow administrative commands and grants all privileges associated with the TRIGGER permission. Starting with MariaDB Xpand 6.1, some permissions previously granted by SUPER have been split to the BINLOG ADMIN, CLUSTER ADMIN, CONNECTION_ADMIN, REPAIR ADMIN, REPLICATION ADMIN, SET USER, and SYSTEM VARIABLES ADMIN privileges.

  • SYSTEM VARIABLES ADMIN: Added in MariaDB Xpand 6.1.

  • TRIGGER

  • UPDATE

  • USAGE

For additional information, see "Privileges for MariaDB Xpand".

Unsupported Privileges

Xpand ignores the following privileges:

  • CREATE TABLESPACE: Not applicable for Xpand.

  • EVENT: Not applicable for Xpand.

  • LOCK TABLES: Enables the use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege.

  • PROXY: Not applicable for Xpand.

Xpand-Specific Privileges

Xpand implements an OSAuth privilege that allows users with this privilege to be authenticated by the OS. This allows a SQL user to log in without a password as long as they have been authenticated as the corresponding OS user.

sql> GRANT OSAUTH ON *.* to 'xpand'@'localhost';

The OSAuth privilege is granted to the database management user (xpandm) for use with clx, and the database daemon user (xpand) for use by statd and XpandGUI.

Other Differences

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

  • The GRANT statement ignores REQUIRE, if included.

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

  • 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

    • RENAME USER, CURRENT_USER, CURRENT_USER() and SESSION_USER() are not supported.