GRANT
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 to accounts. To use GRANT
, you must have the GRANT OPTION
privilege, and you must have the privileges that you are granting.
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 justtbl_name
to specify a table in the default database. TheTABLE
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 justFUNCTION 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 justPROCEDURE 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 | |
GRANT OPTION | Grant global privileges. You can only grant privileges that you have. |
PROCESS | |
RELOAD | |
REPLICATION CLIENT | |
REPLICATION SLAVE | |
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 the mysqladmin shutdown command. |
SUPER |
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 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 | |
GRANT OPTION | Grant database privileges. You can only grant privileges that you have. |
LOCK TABLES |
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. |
DROP | Drop a table using the DROP TABLE statement or a view using the DROP VIEW 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 | |
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 | |
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) | |
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. |