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]
 | PUBLIC
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 user_specification [, user_specification ...]
  [WITH GRANT OPTION]

GRANT rolename TO grantee [, grantee ...]
  [WITH ADMIN OPTION]

grantee:
  rolename
  username [authentication_option]

user_options:
  [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  [WITH with_option [with_option] ...]

object_type:
  TABLE
 | FUNCTION
 | PROCEDURE
 | PACKAGE
 | PACKAGE BODY

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 do not take effect immediately and are only applied to
connections created after the GRANT statement was executed.

* Global privileges priv_type 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 prior to
MariaDB 10.4, and in  mysql.global_priv table afterwards.
* Database privileges priv_type 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 priv_type 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 priv_type 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 priv_type 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 priv_type 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.

BINLOG ADMIN
------------

Enables administration of the binary log, including the PURGE BINARY LOGS
statement and setting the system variables:

* binlog_annotate_row_events
* binlog_cache_size
* binlog_commit_wait_count
* binlog_commit_wait_usec
* binlog_direct_non_transactional_updates
* binlog_expire_logs_seconds
* binlog_file_cache_size
* binlog_format
* binlog_row_image
* binlog_row_metadata
* binlog_stmt_cache_size
* expire_logs_days
* log_bin_compress
* log_bin_compress_min_len
* log_bin_trust_function_creators
* max_binlog_cache_size
* max_binlog_size
* max_binlog_stmt_cache_size
* sql_log_bin and
* sync_binlog.

Added in MariaDB 10.5.2.

BINLOG MONITOR
--------------

New name for REPLICATION CLIENT from MariaDB 10.5.2, (REPLICATION CLIENT still
supported as an alias for compatibility purposes). Permits running SHOW
commands related to the binary log, in particular the SHOW BINLOG STATUS and
SHOW BINARY LOGS statements. Unlike REPLICATION CLIENT prior to MariaDB 10.5,
SHOW REPLICA STATUS isn't included in this privilege, and REPLICA MONITOR is
required.

BINLOG REPLAY
-------------

Enables replaying the binary log with the BINLOG statement (generated by
mariadb-binlog), executing SET timestamp when secure_timestamp is set to
replication, and setting the session values of system variables usually
included in BINLOG output, in particular:

* gtid_domain_id
* gtid_seq_no
* pseudo_thread_id
* server_id.

Added in MariaDB 10.5.2

CONNECTION ADMIN
----------------

Enables administering connection resource limit options. This includes
ignoring the limits specified by:

* max_connections
* max_user_connections and
* max_password_errors.

The statements specified in init_connect are not executed, killing connections
and queries owned by other users is permitted. The following
connection-related system variables can be changed:

* connect_timeout
* disconnect_on_expired_password
* extra_max_connections
* init_connect
* max_connections
* max_connect_errors
* max_password_errors
* proxy_protocol_networks
* secure_auth
* slow_launch_time
* thread_pool_exact_stats
* thread_pool_dedicated_listener
* thread_pool_idle_timeout
* thread_pool_max_threads
* thread_pool_min_threads
* thread_pool_oversubscribe
* thread_pool_prio_kickup_timer
* thread_pool_priority
* thread_pool_size, and
* thread_pool_stall_limit.

Added in MariaDB 10.5.2.

CREATE USER
-----------

Create a user using the CREATE USER statement, or implicitly create a user
with the GRANT statement.

FEDERATED ADMIN
---------------

Execute CREATE SERVER, ALTER SERVER, and DROP SERVER statements. Added in
MariaDB 10.5.2.

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, for example via SHOW PROCESSLIST
or mariadb-admin processlist. If you have the PROCESS privilege, you can see
all threads. Otherwise, you can see only your own threads (that is, threads
associated with the MariaDB account that you are using).

READ_ONLY ADMIN
---------------

User can set the read_only system variable and allows the user to perform
write operations, even when the read_only option is active. Added in MariaDB
10.5.2.

From MariaDB 10.11.0, the READ_ONLY ADMIN privilege has been removed from
SUPER. The benefit of this is that one can remove the READ_ONLY ADMIN
privilege from all users and ensure that no one can make any changes on any
non-temporary tables. This is useful on replicas when one wants to ensure that
the replica is kept identical to the primary.

RELOAD
------

Execute FLUSH statements or equivalent mariadb-admin commands.

REPLICATION CLIENT
------------------

Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements.
Renamed to BINLOG MONITOR in MariaDB 10.5.2 (but still supported as an alias
for compatibility reasons). SHOW SLAVE STATUS was part of REPLICATION CLIENT
prior to MariaDB 10.5.

REPLICATION MASTER ADMIN
------------------------

Permits administration of primary servers, including the SHOW REPLICA HOSTS
statement, and setting the gtid_binlog_state, gtid_domain_id,
master_verify_checksum and server_id system variables. Added in MariaDB 10.5.2.

REPLICA MONITOR
---------------

Permit SHOW REPLICA STATUS and SHOW RELAYLOG EVENTS. From MariaDB 10.5.9.

When a user would upgrade from an older major release to a MariaDB 10.5 minor
release prior to MariaDB 10.5.9, certain user accounts would lose
capabilities. For example, a user account that had the REPLICATION CLIENT
privilege in older major releases could run SHOW REPLICA STATUS, but after
upgrading to a MariaDB 10.5 minor release prior to MariaDB 10.5.9, they could
no longer run SHOW REPLICA STATUS, because that statement was changed to
require the REPLICATION REPLICA ADMIN privilege.

This issue is fixed in MariaDB 10.5.9 with this new privilege, which now
grants the user the ability to execute SHOW [ALL] (SLAVE | REPLICA) STATUS.

When a database is upgraded from an older major release to MariaDB Server
10.5.9 or later, any user accounts with the REPLICATION CLIENT or REPLICATION
SLAVE privileges will automatically be granted the new REPLICA MONITOR
privilege. The privilege fix occurs when the server is started up, not when
mariadb-upgrade is performed.

However, when a database is upgraded from an early 10.5 minor release to
10.5.9 and later, the user will have to fix any user account privileges
manually.

REPLICATION REPLICA
-------------------

Synonym for REPLICATION SLAVE. From MariaDB 10.5.1.

REPLICATION SLAVE
-----------------

Accounts used by replica servers on the primary need this privilege. This is
needed to get the updates made on the master. From MariaDB 10.5.1, REPLICATION
REPLICA is an alias for REPLICATION SLAVE.

REPLICATION SLAVE ADMIN
-----------------------

Permits administering replica servers, including START REPLICA/SLAVE, STOP
REPLICA/SLAVE, CHANGE MASTER, SHOW REPLICA/SLAVE STATUS, SHOW RELAYLOG EVENTS
statements, replaying the binary log with the BINLOG statement (generated by
mariadb-binlog), and setting the system variables:

* gtid_cleanup_batch_size
* gtid_ignore_duplicates
* gtid_pos_auto_engines
* gtid_slave_pos
* gtid_strict_mode
* init_slave
* read_binlog_speed_limit
* relay_log_purge
* relay_log_recovery
* replicate_do_db
* replicate_do_table
* replicate_events_marked_for_skip
* replicate_ignore_db
* replicate_ignore_table
* replicate_wild_do_table
* replicate_wild_ignore_table
* slave_compressed_protocol
* slave_ddl_exec_mode
* slave_domain_parallel_threads
* slave_exec_mode
* slave_max_allowed_packet
* slave_net_timeout
* slave_parallel_max_queued
* slave_parallel_mode
* slave_parallel_threads
* slave_parallel_workers
* slave_run_triggers_for_rbr
* slave_sql_verify_checksum
* slave_transaction_retry_interval
* slave_type_conversions
* sync_master_info