GRANT
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
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(), orSESSION_USER()The
_and%wildcards are not supported in database name specificationsValues supplied for
column_listandobject_typeare ignoredThe
REQUIREclause is not supportedThe following resource limit options are not supported and cause a syntax error to be raised:
MAX_QUERIES_PER_HOURMAX_UPDATES_PER_HOURMAX_CONNECTIONS_PER_HOURMAX_USER_CONNECTIONS
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'@'%';