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.
Use the REVOKE statement to revoke privileges granted
with the GRANT statement.
To determine what privileges an account has, use the
SHOW GRANTS statement.
Privilege Levels
Privileges can be set globally, for an entire database, for a table or function, or for individual columns in a table. Certain privileges can only be set at certain levels.
- Global privileges are set using
*.*for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables and functions. - Database privileges are set 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 and functions in the database. - Table privileges are set using
db_name.tbl_namefor priv_level, or using justtbl_nameto specify a table in the default database. 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 set 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.
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 database, tables, or functions, including those created later.
To set a global privilege, use *.* for priv_level.
| Privilege | Description |
|---|---|
CREATE USER | Create a user with the CREATE USER statement, or implicitly create a user with the GRANT statement. |
FILE | |
PROCESS | |
RELOAD | |
REPLICATION CLIENT | |
REPLICATION SLAVE | |
SHOW DATABASES | List all databases with 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 | |
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 ROUTINE | |
CREATE TEMPORARY TABLES | |
EVENT | |
LOCK TABLES |
Table Privileges
| Privilege | Description |
|---|---|
ALTER | Change the structure of an existing table using the ALTER TABLE statement. |
CREATE | |
CREATE VIEW | |
DELETE | Remove rows from a table using the DELETE statement. |
DROP | Drop a table using the DROP statement. |
GRANT | |
INDEX | |
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 | |
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 procedure using the ALTER PROCEDURE statement. |
EXECUTE | |
GRANT |
