GRANT
You are viewing an old version of this article. View
the current version here.
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 command allows you to create MariaDB user accounts
and to grant rights 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 command to revoke privileges granted
with the GRANT command.
To determine what privileges an account has, use the
SHOW GRANTS command.
Global Privileges
| Privilege | Description |
|---|---|
CREATE USER | Create a user with the CREATE USER command, or implicitly create a user with the GRANT command. |
SHOW DATABASES | List all databases with the SHOW DATABASES command. Without the SHOW DATABASES privilege, you can still issue the SHOW DATABASES command, but it will only list databases containing tables on which you have privileges. |
Table Privileges
| Privilege | Description |
|---|---|
ALTER | Change the structure of an existing table using the ALTER TABLE command. |
DELETE | Remove rows from a table using the DELETE command. |
DROP | Drop a table using the DROP command. |
INSERT | Add rows to a table using the INSERT command. 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 command. The SELECT privilege can also be set on individual columns; see Column Privileges below for details. |
UPDATE | Update existing rows in a table using the UPDATE command. UPDATE commands 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 command 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 command. If you only have column-level INSERT privileges, you must specify the columns you are setting with the INSERT command. 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 command. 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 command. UPDATE commands 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. |
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
