Summary: in this tutorial, you will learn how to use MySQL GRANT statement to grant privileges to MySQL accounts.
To going forward with this tutorial, we are highly recommend that you follow the Getting started with MySQL access control system and How to create a user in MySQL first.
MySQL GRANT Statement Syntax
MySQL provides you with the MySQL GRANT
statement that allows you to grant access privileges to database accounts. The following illustrates the GRANT
statement syntax:
GRANT privileges (column_list) ON [object_type] privilege_level TO account [IDENTIFIED BY 'password'] [REQUIRE encryption] WITH with_options
We will examine the MySQL GRANT statement in greater detail:
privileges
indicates the privileges that you assign to the account. For example, theCREATE
privilege allows an account to create databases and create tables. You can grant multiple privileges using singleGRANT
statement; the privileges are separated by commas.column_list
specifies the columns to which a privilege applies. The columns are separated by commas and listed within parentheses. Thecolumn_list
is optional element.privilege_level
specifies the level at which the privileges apply. You can use global privileges, database-specific privileges, table-specific privileges, column-specific privileges, etc.account
specifies which account is being granted the privileges.password
specifies the password to assign to the account. If the account exists, theGRANT
statement replaces the old password by the new one. Like theCREATE USER
statement, you use plain text password followed by theIDENTIFIED BY
clause. TheIDENTIFIED BY
clause is optional.After the
REQUIRE
clause, you specifies whether the account has to connect to the database server over secure connection using SSL.If you want the account to have the privilege that can grant its own privileges to other accounts, you need to use the
WITH
clause withGRANT OPTION
clause. In addition, you can use theWITH
clause to allocate MySQL database server’s resource e.g., to set how many connections or statements that an account can use per hour. This is very helpful in shared environments such as MySQL shared hosting.If the account that you specify in the
GRANT
statement after theTO
clause exists, theGRANT
statement modifies its privileges, otherwise, theGRANT
statement creates a new account with the specified privileges.
Besides granting privileges, the GRANT
statement also assigns other characteristics to an account such as:
Limit account on access to the database server resource.
Force account to use secure connection when connecting to the database server.
If you want to grant a particular privilege to an account, you must have at least that privilege and GRANT OPTION
privilege.
You often use the MySQL GRANT
statement together with the CREATE USER statement. You use the CREATE USER
statement to create a new account first and then use the MySQL GRANT
statement to grant privileges to the created account.
MySQL GRANT examples
Let’s practice with some examples of using MySQL GRANT
statement to have a better understanding.
If you want to create a super
account that can do anything including being able to grant privileges to other users, you can use the following statements:
CREATE USER 'super'@'localhost' IDENTIFIED BY 'SecurePass1'; GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION;
The ON *.*
clause means all databases and all objects in the databases. The only limitation of the super
user is that it can only connect to the database server from the localhost
, which makes the MySQL server more secure.
To create a user that has all access in the sample database and can connect from any host you use the following statements:
CREATE USER 'super2'@'%' IDENTIFIED BY 'SecurePass2'; GRANT ALL classicmodels.* TO 'super2'@'%' WITH GRANT OPTION;
You can grant multiple privileges using a single GRANT
statement. For example, you can create a user that can execute the SELECT, INSERT and UPDATE statements against the classicmodels
sample database using the following statements:
CREATE USER 'rfc'@'%' IDENTIFIED BY 'SecurePass3'; GRANT SELECT, UPDATE, DELETE ON classicmodels.* TO 'rfc'@'%';
Available privileges to use with MySQL GRANT
The following table illustrates all privileges available in MySQL.
Privilege | Description |
---|---|
ALL [PRIVILEGES] | Grant all privileges at specified access level except GRANT OPTION |
ALTER | Allow to use of ALTER TABLE statement |
ALTER ROUTINE | Allow user to alter or drop stored routine |
CREATE | Allow user to create database and table |
CREATE ROUTINE | Allow user to create stored routine |
CREATE TABLESPACE | Allow user to create, alter or drop tablespaces and log file groups |
CREATE TEMPORARY TABLES | Allow user to create temporary table by using CREATE TEMPORARY TABLE |
CREATE USER | Allow user to use the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements. |
CREATE VIEW | Allow user to create or modify view |
DELETE | Allow user to use DELETE |
DROP | Allow user to drop database, table and view |
EVENT | Allow user to schedule events in Event Scheduler |
EXECUTE | Allow user to execute stored routines |
FILE | Allow user to read any file in the database directory. |
GRANT OPTION | Allow user to have privileges to grant or revoke privileges from other accounts |
INDEX | Allow user to create or remove indexes. |
INSERT | Allow user to use INSERT statement |
LOCK TABLES | Allow user to use LOCK TABLES on tables for which you have the SELECT privilege |
PROCESS | Allow user to see all processes with SHOW PROCESSLIST statement. |
PROXY | Enable user proxying |
REFERENCES | Not implemented |
RELOAD | Allow user to use FLUSH operations |
REPLICATION CLIENT | Allow user to query to see where master or slave servers are |
REPLICATION SLAVE | Allow user to use replicate slaves to read binary log events from the master. |
SELECT | Allow user to use SELECT statement |
SHOW DATABASES | Allow user to show all databases |
SHOW VIEW | Allow user to use SHOW CREATE VIEW statement |
SHUTDOWN | Allow user to use mysqladmin shutdown command |
SUPER | Allow user to use other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin command |
TRIGGER | Allow user to use TRIGGER operations. |
UPDATE | Allow user to use UPDATE statement |
USAGE | Equivalent to “no privileges” |
Table 1.1
In this tutorial, you have learned how to use the MySQL GRANT
statement to grant privileges to accounts.
Related Tutorials
共同学习,写下你的评论
评论加载中...
作者其他优质文章