mysql grant by_MySQL GRANT 授权语句使用介绍

Summary: in this tutorial, you will learn how to use MySQL GRANTstatement 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 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:privilegesindicates the privileges that you assign to the account. For example, the CREATEprivilege allows an account to create databases and create tables. You can grant multiple privileges using single GRANTstatement; the privileges are separated by commas.

column_listspecifies the columns to which a privilege applies. The columns are separated by commas and listed within parentheses. The column_list is optional element.

privilege_levelspecifies the level at which the privileges apply. You can use global privileges, database-specific privileges, table-specific privileges, column-specific privileges, etc.

accountspecifies which account is being granted the privileges.

passwordspecifies the password to assign to the account. If the account exists, the GRANTstatement replaces the old password by the new one. Like the CREATE USER statement, you use plain text password followed by the IDENTIFIED BYclause. The IDENTIFIED BY clause is optional.

After the REQUIREclause, 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 WITHclause with GRANT OPTION clause. In addition, you can use the WITHclause 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 GRANTstatement after the TOclause exists, the GRANTstatement modifies its privileges, otherwise, the GRANTstatement creates a new account with the specified privileges.

Besides granting privileges, the GRANTstatement 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 GRANTstatement together with the CREATE USER statement. You use the CREATE USERstatement to create a new account first and then use the MySQL GRANTstatement to grant privileges to the created account.

MySQL GRANT examples

Let’s practice with some examples of using MySQL GRANTstatement to have a better understanding.

If you want to create a superaccount 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 superuser 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, INSERTand UPDATEstatements against the classicmodelssample 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.PrivilegeDescriptionALL [PRIVILEGES]Grant all privileges at specified access level except GRANT OPTION

ALTERAllow to use of ALTER TABLE statement

ALTER ROUTINEAllow user to alter or drop stored routine

CREATEAllow user to create database and table

CREATE ROUTINEAllow user to create stored routine

CREATE TABLESPACEAllow user to create, alter or drop tablespaces and log file groups

CREATE TEMPORARY TABLESAllow user to create temporary table by using CREATE TEMPORARY TABLE

CREATE USERAllow user to use the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.

CREATE VIEWAllow user to create or modify view

DELETEAllow user to use DELETE

DROPAllow user to drop database, table and view

EVENTAllow user to schedule events in Event Scheduler

EXECUTEAllow user to execute stored routines

FILEAllow user to read any file in the database directory.

GRANT OPTIONAllow user to have privileges to grant or revoke privileges from other accounts

INDEXAllow user to create or remove indexes.

INSERTAllow user to use INSERT statement

LOCK TABLESAllow user to use LOCK TABLES on tables for which you have the SELECT privilege

PROCESSAllow user to see all processes with SHOW PROCESSLIST statement.

PROXYEnable user proxying

REFERENCESNot implemented

RELOADAllow user to use FLUSH operations

REPLICATION CLIENTAllow user to query to see where master or slave servers are

REPLICATION SLAVEAllow user to use replicate slaves to read binary log events from the master.

SELECTAllow user to use SELECT statement

SHOW DATABASESAllow user to show all databases

SHOW VIEWAllow user to use SHOW CREATE VIEW statement

SHUTDOWNAllow user to use mysqladmin shutdown command

SUPERAllow user to use other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin command

TRIGGERAllow user to use TRIGGER operations.

UPDATEAllow user to use UPDATE statement

USAGEEquivalent 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