GRANT

Overview

Assigns privileges to a user account or security role.

USAGE

Common Syntax:

GRANT
   <privilege_type> [, <privilege_type>] ...
ON [TABLE | FUNCTION  | PROCEDURE] <privilege_target_name>
TO <user_name> [ @ <host_name> ] [<authentication_option>]
WITH [GRANT OPTION]

Authentication option:

IDENTIFIED BY '<cleartext_password>'
IDENTIFIED BY PASSWORD '<password_hash>'
IDENTIFIED WITH {mysql_native_password | sha256_password} [{BY | AS} '<cleartext_password>']

DETAILS

MariaDB Xpand's GRANT statement has some differences from MariaDB Enterprise Server:

  • The user account can't be specified using CURRENT_USER, CURRENT_USER(), or SESSION_USER()

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

  • Values supplied for column_list and object_type are ignored

  • The REQUIRE clause is not supported

  • The following resource limit options are not supported and cause a syntax error to be raised:

    • MAX_QUERIES_PER_HOUR

    • MAX_UPDATES_PER_HOUR

    • MAX_CONNECTIONS_PER_HOUR

    • MAX_USER_CONNECTIONS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

GRANT

Let's create a new user:

CREATE USER usr1;

Here are the privileges for the newly created user:

SHOW GRANTS FOR usr1\G
*************************** 1. row ***************************
Grants for usr1@%: GRANT USAGE ON *.* TO 'usr1'@'%'

To grant privileges at global scope:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON *.*
   TO 'usr1'@'%';

Here are the assigned privileges:

*************************** 1. row ***************************
Grants for usr1@%: GRANT DELETE, INSERT, SELECT, UPDATE ON *.* TO 'usr1'@'%'

Grant Scope-Based Privileges

To grant privileges at global scope:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON *.*
   TO 'usr1'@'%';

To grant privileges at database scope:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON db1.*
   TO 'usr1'@'%';

To grant privileges at table scope:

GRANT SELECT, INSERT, UPDATE, DELETE
   ON db1.customers
   TO 'usr1'@'%';

To grant privileges on a stored procedure:

GRANT EXECUTE
   ON PROCEDURE db1.validate_customer
   TO 'usr1'@'%';

To grant privileges on a stored function:

GRANT EXECUTE
   ON FUNCTION db1.cart_total
   TO 'usr1'@'%';

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES