Privileges

Syntax

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_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
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

Description

The GRANT statement allows you to create MariaDB user accounts and to grant privileges (or roles - see section below) to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

If the NO_AUTO_CREATE_USER SQL_MODE is set, users can only be created with a CREATE USER statement. In this case GRANT will produce an error when the specified user does not exist.

The user argument and the IDENTIFIED BY clause are the same as for the CREATE USER statement. If the user account does not yet exist, it will be implicitly created. You must have the privileges needed for the CREATE USER statement to implicitly create a user with GRANT. If the user account does exist and you provide the IDENTIFIED BY clause, 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.

Use the REVOKE statement to revoke privileges granted with the GRANT statement.

Use the SHOW GRANTS statement to determine what privileges an account has.

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.
  • 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.
  • 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.

PrivilegeDescription
CREATE USERCreate a user using the CREATE USER statement, or implicitly create a user with the GRANT statement.
FILERead 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 file.
GRANT OPTIONGrant global privileges. You can only grant privileges that you have.
PROCESSShow information about the active processes, via SHOW PROCESSLIST or mysqladmin processlist.
RELOADExecute FLUSH statement or equivalent mysqladmin commands.
REPLICATION CLIENTExecute SHOW MASTER STATUS and SHOW SLAVE STATUS informative statements.
REPLICATION SLAVEAccounts used by slave servers on the master need this privilege. This is needed to get the updates made on the master.
SHOW DATABASESList 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.
SHUTDOWNShut down the server using the mysqladmin shutdown command.
SUPERExecute 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 permissions allows 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.

PrivilegeDescription
CREATECreate 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 ROUTINECreate Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements.
CREATE TEMPORARY TABLESCreate temporary tables with the CREATE TEMPORARY TABLE statement.
DROPDrop 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.
EVENTCreate, drop and alter EVENTs. Added in MySQL 5.1.6.
GRANT OPTIONGrant database privileges. You can only grant privileges that you have.
LOCK TABLESAcquire explicit locks using the LOCK TABLES statement; you also need to have the SELECT privilege on a table, in order to lock it.

Table Privileges

PrivilegeDescription
ALTERChange the structure of an existing table using the ALTER TABLE statement.
CREATECreate a table using the CREATE TABLE statement. You can grant the CREATE privilege on tables that do not yet exist.
CREATE VIEWCreate a view using the CREATE_VIEW statement.
DELETERemove rows from a table using the DELETE statement.
DROPDrop a table using the DROP TABLE statement or a view using the DROP VIEW statement.
GRANT OPTIONGrant table privileges. You can only grant privileges that you have.
INDEXCreate 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.
INSERTAdd rows to a table using the INSERT statement. The INSERT privilege can also be set on individual columns; see Column Privileges below for details.
REFERENCESUnused.
SELECTRead 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 VIEWShow the CREATE VIEW statement to create a view using the SHOW CREATE VIEW statement.
TRIGGERExecute triggers associated to tables you update, execute the CREATE TRIGGER and DROP TRIGGER statements. You will still be able to see triggers.
UPDATEUpdate 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';
PrivilegeDescription
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

PrivilegeDescription
ALTER ROUTINEChange the characteristics of a stored function using the ALTER FUNCTION statement.
EXECUTEUse a stored function. You need SELECT privileges for any tables or columns accessed by the function.
GRANT OPTIONGrant function privileges. You can only grant privileges that you have.

Procedure Privileges

PrivilegeDescription
ALTER ROUTINEChange the characteristics of a stored procedure using the ALTER PROCEDURE statement.
EXECUTEExecute 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 OPTIONGrant procedure privileges. You can only grant privileges that you have.

Setting per-account resources limits

It is possible to set a per-accout limit for the server resources that can be used. The following table shows the values that can be set per account:

Limit TypeDecription
MAX_QUERIES_PER_HOURNumber of statements that the account can issue per hour (including updates)
MAX_UPDATES_PER_HOURNumber of updates (not queries) that the account can issue per hour
MAX_CONNECTIONS_PER_HOURNumber of connections that the account can start per hour
MAX_USER_CONNECTIONSNumber of simoultaneous connections that can be accepted from the same account; if it is 0, max_connections will be used instead; if max_connections is 0, there is no limit for this account's simoultaneous connections.

If value of 0 is set for any of these limits, the limit is removed.

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 more than one limits:

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

The use of resource count is made per account, which means 'user'@'server'; not per user 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).

Per-Account SSL Options

By default, MariaDB uses unencrypted connections for all client/server communications. In order to use SSL, OpenSSL or yaSSL must be present in your system. Also, the MariaDB server needs to be built with SSL support and be properly configured to use one of them.

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

OptionDescription
REQUIRE NONESSL is not required for this account, but can still be used.
REQUIRE SSLThe account must use SSL, but no valid X509 certificate is required.
REQUIRE X509The account must use SSL and must have a valid X509 certificate.
REQUIRE ISSUER 'issuer'The account must use SSL and must have a valid X509 certificate. Also, the Certificate Authority must be the one specified via the string issuer.
REQUIRE SUBJECT 'subject'The account must use SSL and must have a valid X509 certificate. Also, the certificate's Subject must be the one specified via the string subject.
REQUIRE CIPHER 'cipher'The account must use SSL and must have a valid X509 certificate. Also, the encryption used for the connection must use one of the methods specified in the string cipher.

If you require a X509 certificate, REQUIRE SSL is implicit. The ISSUER, SUBCJECT, and CIPHER options can be combined together in any order, and if you use any of them REQUIRE X509 is implicit.

The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options (at your choice).

Here is an example:

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/emailAddress=p.parker@marvel.com'
    AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

Roles

MariaDB starting with 10.0.5

Roles were introduced in MariaDB 10.0.5.

Syntax

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

The GRANT statement is also used to grant the use of one or more roles to one or more users. 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.

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.

Examples

GRANT journalist TO hulda;

GRANT journalist TO berengar WITH ADMIN OPTION;

Comments

Comments loading...