MySQL GRANT 授权语句使用介绍-原创手记-慕课网

Summary: in this tutorial, you will learn how to use MySQL GRANT statement to grant privileges to MySQL accounts.

MySQL GRANT statement

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 GRANTstatement 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, the CREATE privilege allows an account to create databases and create tables. You can grant multiple privileges using single GRANT 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. The column_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, the GRANT statement replaces the old password by the new one. Like the CREATE USER statement, you use plain text password followed by the IDENTIFIED BY clause. The IDENTIFIED 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 with GRANT OPTION clause. In addition, you can use the WITH 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 the TO clause exists, the GRANT statement modifies its privileges, otherwise, the GRANT 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 OPTIONprivilege.

You often use the MySQL GRANT statement together with the CREATE USER statement. You use the CREATE USERstatement 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.