Syntax
------ 
GRANT
 priv_type [(column_list)]
 [, priv_type [(column_list)]] ...
 ON [object_type] priv_level
 TO user_specification [ user_options ...]
 
user_specification:
 username [authentication_option]
 
authentication_option:
 IDENTIFIED BY 'password' 
 | IDENTIFIED BY PASSWORD 'password_hash'
 | IDENTIFIED {VIA|WITH} authentication_rule [OR
authentication_rule ...]
 
authentication_rule:
 authentication_plugin
 | authentication_plugin {USING|AS}
'authentication_string'
 | authentication_plugin {USING|AS} PASSWORD('password')
 
GRANT PROXY ON username
 TO username [, username] ...
 [WITH GRANT OPTION]
 
user_options:
 [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
 [WITH with_option [with_option] ...]
 
object_type:
 TABLE
 | FUNCTION
 | PROCEDURE
 
priv_level:
 *
 | *.*
 | db_name.*
 | db_name.tbl_name
 | tbl_name
 | db_name.routine_name
 
with_option:
 GRANT OPTION
 | resource_option
 
resource_option:
 MAX_QUERIES_PER_HOUR count
 | MAX_UPDATES_PER_HOUR count
 | MAX_CONNECTIONS_PER_HOUR count
 | MAX_USER_CONNECTIONS count
 | MAX_STATEMENT_TIME time
 
tls_option:
 SSL 
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
 
Description
----------- 
The GRANT statement allows you to grant privileges or roles
to accounts. To use GRANT, you must have the GRANT OPTION
privilege, and you must have the privileges that you are
granting.
 
Use the REVOKE statement to revoke privileges granted with
the GRANT statement.
 
Use the SHOW GRANTS statement to determine what privileges
an account has.
 
Account Names
 
For GRANT statements, account names are specified as the
username argument in the same way as they are for CREATE
USER statements. See account names from the CREATE USER page
for details on how account names are specified.
 
Implicit Account Creation
 
The GRANT statement also allows you to implicitly create
accounts in some cases.
 
If the account does not yet exist, then GRANT can implicitly
create it. To implicitly create an account with GRANT, a
user is required to have the same privileges that would be
required to explicitly create the account with the CREATE
USER statement.
 
If the NO_AUTO_CREATE_USER SQL_MODE is set, then accounts
can only be created if authentication information is
specified, or with a CREATE USER statement. If no
authentication information is provided, GRANT will produce
an error when the specified account does not exist, for
example:
 
show variables like '%sql_mode%' ;
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
 
GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY '';
ERROR 1133 (28000): Can't find any matching row in the user
table
 
GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED VIA PAM
using 'mariadb' require ssl ;
Query OK, 0 rows affected (0.00 sec)
 
select host, user from mysql.user where user='user123' ;
 
+------+----------+
| host | user |
+------+----------+
| % | user123 |
+------+----------+
 
Privilege Levels
 
Privileges can be set globally, for an entire database, for
a table or routine,
or for individual columns in a table. Certain privileges can
only be set at
certain levels.
Global privileges are granted using *.* for
priv_level. Global privileges include privileges to
administer the database
and manage user accounts, as well as privileges for all
tables, functions, and
procedures. Global privileges are stored in the mysql.user
table.
Database privileges are granted using db_name.*
for priv_level, or using just * to use default database.
Database
privileges include privileges to create tables and
functions, as well as
privileges for all tables, functions, and procedures in the
database. Database privileges are stored in the mysql.db
table.
Table privileges are granted using db_name.tbl_name
for priv_level, or using just tbl_name to specify a table in
the default
database. The TABLE keyword is optional. Table privileges
include the
ability to select and change data in the table. Certain
table privileges can
be granted for individual columns.
Column privileges are granted by specifying a table for
priv_level and providing a column list after the privilege
type. They allow
you to control exactly which columns in a table users can
select and change.
Function privileges are granted using FUNCTION
db_name.routine_name
for priv_level, or using just FUNCTION routine_name to
specify a function
in the default database.
Procedure privileges are granted using PROCEDURE
db_name.routine_name
for priv_level, or using just PROCEDURE routine_name to
specify a procedure
in the default database.
 
The USAGE Privilege
 
The USAGE privilege grants no real privileges. The SHOW
GRANTS
statement will show a global USAGE privilege for a
newly-created user. You
can use USAGE with the GRANT statement to change options
like GRANT OPTION
and MAX_USER_CONNECTIONS without changing any account
privileges.
 
The ALL PRIVILEGES Privilege
 
The ALL PRIVILEGES privilege grants all available
privileges. Granting all
privileges only affects the given privilege level. For
example, granting all
privileges on a table does not grant any privileges on the
database or globally.
 
Using ALL PRIVILEGES does not grant the special GRANT OPTION
privilege.
 
You can use ALL instead of ALL PRIVILEGES.
 
The GRANT OPTION Privilege
 
Use the WITH GRANT OPTION clause to give users the ability
to grant privileges
to other users at the given privilege level. Users with the
GRANT OPTION privilege can
only grant privileges they have. They cannot grant
privileges at a higher privilege level than
they have the GRANT OPTION privilege.
 
The GRANT OPTION privilege cannot be set for individual
columns.
If you use WITH GRANT OPTION when specifying column
privileges,
the GRANT OPTION privilege will be granted for the entire
table.
 
Using the WITH GRANT OPTION clause is equivalent to listing
GRANT OPTION
as a privilege.
 
Global Privileges
 
The following table lists the privileges that can be granted
globally. You can
also grant all database, table, and function privileges
globally. When granted
globally, these privileges apply to all databases, tables,
or functions,
including those created later.
 
To set a global privilege, use *.* for priv_level.
 
Privilege | Description | 
 
CREATE USER | Create a user using the CREATE USER statement,
or implicitly create a user with the GRANT statement. | 
 
FILE | Read and write files on the server, using statements
like LOAD DATA INFILE or functions like LOAD_FILE(). Also
needed to create CONNECT outward tables. MariaDB server must
have the permissions to access those files. | 
 
GRANT OPTION | Grant global privileges. You can only grant
privileges that you have. | 
 
PROCESS | Show information about the active processes, via
SHOW PROCESSLIST or mysqladmin processlist. | 
 
RELOAD | Execute FLUSH statements or equivalent mysqladmin
commands. | 
 
REPLICATION CLIENT | Execute SHOW MASTER STATUS and SHOW
SLAVE STATUS informative statements. | 
 
REPLICATION SLAVE | Accounts used by slave servers on the
master need this privilege. This is needed to get the
updates made on the master. | 
 
SHOW DATABASES | List all databases using the SHOW DATABASES
statement. Without the SHOW DATABASES privilege, you can
still issue the SHOW DATABASES statement, but it will only
list databases containing tables on which you have
privileges. | 
 
SHUTDOWN | Shut down the server using SHUTDOWN or the
mysqladmin shutdown command. | 
 
SUPER | Execute superuser statements: CHANGE MASTER TO, KILL
(users who do not have this privilege can only KILL their
own threads), PURGE LOGS, SET global system variables, or
the mysqladmin debug command. Also, this permission allows
the user to write data even if the read_only startup option
is set, enable or disable logging, enable or disable
replication on slaves, specify a DEFINER for statements that
support that clause, connect once after reaching the
MAX_CONNECTIONS. If a statement has been specified for the
init-connect mysqld option, that command will not be
executed when a user with SUPER privileges connects to the
server. | 
 
Database Privileges
 
The following table lists the privileges that can be granted
at the database
level. You can also grant all table and function privileges
at the database
level. Table and function privileges on a database apply to
all tables or
functions in that database, including those created later.
 
To set a privilege for a database, specify the database
using
db_name.* for priv_level, or just use *
to specify the default database.
 
Privilege | Description | 
 
CREATE | Create a database using the CREATE DATABASE
statement, when the privilege is granted for a database. You
can grant the CREATE privilege on databases that do not yet
exist. This also grants the CREATE privilege on all tables
in the database. | 
 
CREATE ROUTINE | Create Stored Programs using the CREATE
PROCEDURE and CREATE FUNCTION statements. | 
 
CREATE TEMPORARY TABLES | Create temporary tables with the
CREATE TEMPORARY TABLE statement. This privilege enable
writing and dropping those temporary tables | 
 
DROP | Drop a database using the DROP DATABASE statement,
when the privilege is granted for a database. This also
grants the DROP privilege on all tables in the database. | 
 
EVENT | Create, drop and alter EVENTs. Added in MySQL 5.1.6.
| 
 
GRANT OPTION | Grant database privileges. You can only grant
privileges that you have. | 
 
statement; you also need to have the SELECT privilege on a
table, in order to lock it. | 
 
Table Privileges
 
Privilege | Description | 
 
ALTER | Change the structure of an existing table using the
ALTER TABLE statement. | 
 
CREATE | Create a table using the CREATE TABLE statement.
You can grant the CREATE privilege on tables that do not yet
exist. | 
 
CREATE VIEW | Create a view using the CREATE_VIEW statement.
| 
 
DELETE | Remove rows from a table using the DELETE
statement. | 
 
DELETE HISTORY | Remove historical rows from a table using
the DELETE HISTORY statement. Displays as DELETE VERSIONING
ROWS when running SHOW GRANTS until MariaDB 10.3.15 and
until MariaDB 10.4.5 (MDEV-17655), or when running SHOW
PRIVILEGES (MDEV-20382). From MariaDB 10.3.4. From MariaDB
10.3.5, if a user has the SUPER privilege but not this
privilege, running mysql_upgrade will grant this privilege
as well. | 
 
DROP | Drop a table using the DROP TABLE statement or a view
using the DROP VIEW statement. Also required to execute the
TRUNCATE TABLE statement. | 
 
GRANT OPTION | Grant table privileges. You can only grant
privileges that you have. | 
 
INDEX | Create an index on a table using the CREATE INDEX
statement. Without the INDEX privilege, you can still create
indexes when creating a table using the CREATE TABLE
statement if the you have the CREATE privilege, and you can
create indexes using the ALTER TABLE statement if you have
the ALTER privilege. | 
 
INSERT | Add rows to a table using the INSERT statement. The
INSERT privilege can also be set on individual columns; see
Column Privileges below for details. | 
 
REFERENCES | Unused. | 
 
SELECT | Read data from a table using the SELECT statement.
The SELECT privilege can also be set on individual columns;
see Column Privileges below for details. | 
 
SHOW VIEW | Show the CREATE VIEW statement to create a view
using the SHOW CREATE VIEW statement. | 
 
TRIGGER | Execute triggers associated to tables you update,
execute the CREATE TRIGGER and DROP TRIGGER statements. You
will still be able to see triggers. | 
 
UPDATE | Update existing rows in a table using the UPDATE
statement. UPDATE statements usually include a WHERE clause
to update only certain rows. You must have SELECT privileges
on the table or the appropriate columns for the WHERE
clause. The UPDATE privilege can also be set on individual
columns; see Column Privileges below for details. | 
 
Column Privileges
 
Some table privileges can be set for individual columns of a
table. To use
column privileges, specify the table explicitly and provide
a list of column
names after the privilege type. For example, the following
statement would allow
the user to read the names and positions of employees, but
not other information
from the same table, such as salaries.
 
GRANT SELECT (name, position) on Employee to
'jeffrey'@'localhost';
 
Privilege | Description | 
 
INSERT (column_list) | Add rows specifying values in columns
using the INSERT statement. If you only have column-level
INSERT privileges, you must specify the columns you are
setting in the INSERT statement. All other columns will be
set to their default values, or NULL. | 
 
REFERENCES (column_list) | Unused. | 
 
SELECT (column_list) | Read values in columns using the
SELECT statement. You cannot access or query any columns for
which you do not have SELECT privileges, including in WHERE,
ON, GROUP BY, and ORDER BY clauses. | 
 
UPDATE (column_list) | Update values in columns of existing
rows using the UPDATE statement. UPDATE statements usually
include a WHERE clause to update only certain rows. You must
have SELECT privileges on the table or the appropriate
columns for the WHERE clause. | 
 
Function Privileges
 
Privilege | Description | 
 
ALTER ROUTINE | Change the characteristics of a stored
function using the ALTER FUNCTION statement. | 
 
EXECUTE | Use a stored function. You need SELECT privileges
for any tables or columns accessed by the function. | 
 
GRANT OPTION | Grant function privileges. You can only grant
privileges that you have. | 
 
Procedure Privileges
 
Privilege | Description | 
 
ALTER ROUTINE | Change the characteristics of a stored
procedure using the ALTER PROCEDURE statement. | 
 
EXECUTE | Execute a stored procedure using the CALL
statement. The privilege to call a procedure may allow you
to perform actions you wouldn't otherwise be able to do,
such as insert rows into a table. | 
 
GRANT OPTION | Grant procedure privileges. You can only
grant privileges that you have. | 
 
Proxy Privileges
 
Privilege | Description | 
 
PROXY | Permits one user to be a proxy for another. | 
 
The PROXY privilege allows one user to proxy as another
user, which means their privileges change to that of the
proxy user, and the CURRENT_USER() function returns the user
name of the proxy user.
 
The PROXY privilege only works with authentication plugins
that support it. The default mysql_native_password
authentication plugin does not support proxy users.
 
The pam authentication plugin is the only plugin included
with MariaDB that currently supports proxy users. The PROXY
privilege is commonly used with the pam authentication
plugin to enable user and group mapping with PAM.
 
For example, to grant the PROXY privilege to an anonymous
account that authenticates with the pam authentication
plugin, you could execute the following:
 
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
 
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;
 
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
 
GRANT PROXY ON 'dba'@'%' TO ''@'%';
 
A user account can only grant the PROXY privilege for a
specific user account if the granter also has the PROXY
privilege for that specific user account, and if that
privilege is defined WITH GRANT OPTION. For example, the
following example fails because the granter does not have
the PROXY privilege for that specific user account at all:
 
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
 
SHOW GRANTS;
 
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost'
IDENTIFIED BY PASSWORD
'*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+-----------------------------------------------------------------------------------------------------------------------+
 
GRANT PROXY ON 'dba'@'localhost' TO
'bob'@'localhost';
 
ERROR 1698 (28000): Access denied for user
'alice'@'localhost'
 
And the following example fails because the granter does
have the PROXY privilege for that specific user account, but
it is not defined WITH GRANT OPTION:
 
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
 
SHOW GRANTS;
 
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost'
IDENTIFIED BY PASSWORD
'*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT PROXY ON 'dba'@'localhost' TO
'alice'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------------+
 
MariaDB [(none)]> GRANT PROXY ON 'dba'@'localhost' TO
'bob'@'localhost';
 
ERROR 1698 (28000): Access denied for user
'alice'@'localhost'
 
But the following example succeeds because the granter does
have the PROXY privilege for that specific user account, and
it is defined WITH GRANT OPTION:
 
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
 
SHOW GRANTS;
 
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost'
IDENTIFIED BY PASSWORD
'*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT
OPTION |
| GRANT PROXY ON 'dba'@'localhost' TO
'alice'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
 
GRANT PROXY ON 'dba'@'localhost' TO
'bob'@'localhost';
 
Query OK, 0 rows affected (0.004 sec)
 
A user account can grant the PROXY privilege for any other
user account if th