GRANT

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_plugin
  | IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} 'authentication_string'
  | IDENTIFIED {VIA|WITH} 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 with a CREATE USER statement. In that case, GRANT will produce an error when the specified account does not exist.

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.

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.

Table Privileges

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';

Function Privileges

Procedure Privileges

Proxy Privileges

PROXY grants only work with authentication plugins that support it. The mysql_native_password plugin does not support proxying of users.

The PROXY privilege is commonly used with the PAM authentication plugin. It is used 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:

CREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;

CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';

Authentication Options

The authentication options for the GRANT statement are the same as those for the CREATE USER statement.

IDENTIFIED BY 'password'

The optional IDENTIFIED BY clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored to the mysql.user table.

For example, if our password is mariadb, then we can create the user with:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 'mariadb';

If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.

If the user account already exists and if you provide the IDENTIFIED BY clause, then the user's password will be changed. You must have the privileges needed for the SET PASSWORD statement to change a user's password with GRANT.

The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

IDENTIFIED BY PASSWORD 'password_hash'

The optional IDENTIFIED BY PASSWORD clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD function. It will be stored to the mysql.user table as-is.

For example, if our password is mariadb, then we can find the hash with:

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)

And then we can create a user with the hash:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.

If the user account already exists and if you provide the IDENTIFIED BY clause, then the user's password will be changed. You must have the privileges needed for the SET PASSWORD statement to change a user's password with GRANT.

The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.

IDENTIFIED {VIA|WITH} authentication_plugin

The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME.

For example, this could be used with the PAM authentication plugin:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam;

Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the PAM authentication plugin accepts a service name:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam USING 'mariadb';

The exact meaning of the additional argument would depend on the specific authentication plugin.

In MariaDB 10.4 and later, the USING or AS keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this:

GRANT USAGE ON *.* TO safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');

Resource Limit Options

It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:

If any of these limits are set to 0, then there is no limit for that resource for that user.

To set resource limits for an account, if you do not want to change that account's privileges, you can issue a GRANT statement with the USAGE privilege, which has no meaning. The statement can name some or all limit types, in any order.

Here is an example showing how to set resource limits:

GRANT USAGE ON *.* TO 'someone'@'localhost' WITH
    MAX_USER_CONNECTIONS 0
    MAX_QUERIES_PER_HOUR 200;

The resources are tracked per account, which means 'user'@'server'; not per user name or per connection.

The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mysqladmin reload.

Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions, max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for MAX_USER_CONNECTIONS).

TLS Options

By default, MariaDB uses unencrypted connections for all client/server communications. In order to use encrypted connections, OpenSSL or yaSSL must be present in your system. Also, the MariaDB server needs to be built with TLS support and be properly configured to use one of them. See Secure Connections Overview for more information about that.

TLS is the modern term to describe the encryption protocol. SSL refers to the old, now insecure, protocol that preceded TLS. Many of the variable names and options still refer to SSL, rather than TLS, for compatibility reasons. However, MariaDB only uses its more secure successors.

You can require TLS connections on a per-account basis, using the REQUIRE clause. The following options are available:

If you set REQUIRE X509, then REQUIRE SSL is implicitly set. The ISSUER, SUBJECT, and CIPHER options can be set together in any order, and if you set any of them, then REQUIRE X509 is also implicitly set.

The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required.

If any of these options are set for a specific user, then any client who tries to connect with that user will have to be configured to connect with TLS. For example, the mysql client can be configured to do so by setting options like --ssl, --ssl-ca, --ssl-cert, --ssl-cipher, --ssl-key, and --ssl-verify-server-cert.

For example, you can create a user that uses TLS options with the following:

GRANT USAGE ON *.* TO 'someone'@'localhost'
    REQUIRE SUBJECT '/CN=www.mydom.com/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
    AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/[email protected]'
    AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

Roles

Syntax

GRANT role TO grantee [, grantee2 ... ]
[ WITH ADMIN OPTION ]

The GRANT statement is also used to grant the use a role to one or more users or other roles. In order to be able to grant a role, the grantor doing so must have permission to do so (see WITH ADMIN in the CREATE ROLE article).

Specifying the WITH ADMIN OPTION permits the grantee to in turn grant the role to another.

For example, the following commands show how to grant the same role to a couple different users.

GRANT journalist TO hulda;

GRANT journalist TO berengar WITH ADMIN OPTION;

If a user has been granted a role, they do not automatically obtain all permissions associated with that role. These permissions are only in use when the user activates the role with the SET ROLE statement.

Grant Examples

Granting Root-like Privileges

You can create a user that has privileges similar to the default root accounts by executing the following:

CREATE USER 'alexander'@'localhost';
GRANT ALL PRIVILEGES ON  *.* to 'alexander'@'localhost' WITH GRANT OPTION;

See Also

• --skip-grant-tables allows you to start MariaDB without GRANT. This is useful if you lost your root password.
• CREATE USER
• ALTER USER
• DROP USER
• SET PASSWORD
• SHOW CREATE USER
• mysql.user table
• Password Validation Plugins - permits the setting of basic criteria for passwords
• Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.