Privileges for MariaDB Xpand

MariaDB Xpand supports privileges for user accounts that are different than MariaDB Enterprise Server. When using the Xpand Storage Engine topology, the privileges described here only apply when you connect to the Xpand nodes.

Supported Privileges

MariaDB 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)

  • 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 privilege for the parent table

  • RELOAD: Enable use of FLUSH operations

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

  • REPLICATION SLAVE: Allow reading binary logs

  • SELECT

  • SHOW DATABASES

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

  • SHUTDOWN: MariaDB recommends using clx dbstop to shutdown instead of relying on this privilege.

  • SUPER: Allow administrative commands and grants all privileges associated with the TRIGGER permission

  • TRIGGER

  • UPDATE

  • USAGE

Unsupported Privileges

Xpand ignores the following privileges:

  • CREATE TABLESPACE: Not applicable for MariaDB Xpand

  • EVENT: Not applicable for MariaDB 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 MariaDB Xpand.

Xpand-specific Privileges

Xpand service implement 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.

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 Xpand GUI.

Other Differences

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

  • The GRANT statement ignores REQUIRE, if included.

  • The GRANT and REVOKE statements 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.