mysql基本语句grant_MySQL GRANT 授权语句使用介绍

Summary: in this tutorial, you will learn how to useMySQL GRANTstatement to grant privileges to MySQL accounts.

To going forward with this tutorial, we are highly recommend that you follow theGetting started with MySQL access control systemandHow to create a user in MySQLfirst.

MySQL GRANT Statement Syntax

MySQL provides you with theMySQL GRANTstatement that allows you to grant access privileges to database accounts. The following illustrates theGRANTstatement syntax:

GRANTprivileges(column_list)ON[object_type]privilege_levelTOaccount[IDENTIFIEDBY'password'][REQUIREencryption]WITHwith_options

We will examine the MySQL GRANT statement in greater detail:privilegesindicates the privileges that you assign to the account. For example, theCREATEprivilege allows an account tocreate databasesandcreate tables. You can grant multiple privileges using singleGRANTstatement; 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. Thecolumn_listis 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, theGRANTstatement replaces the old password by the new one. Like theCREATE USERstatement, you use plain text password followed by theIDENTIFIED BYclause. TheIDENTIFIED BYclause is optional.

After theREQUIREclause, 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 theWITHclause withGRANT OPTIONclause. In addition, you can use theWITHclause 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 theGRANTstatement after theTOclause exists, theGRANTstatement modifies its privileges, otherwise,theGRANTstatement creates a new account with the specified privileges.

Besides granting privileges, theGRANTstatement 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 andGRANT OPTIONprivilege.

You often use the MySQLGRANTstatement together with theCREATE USER statement. You use theCREATE USERstatement to create a new account first and then use the MySQLGRANTstatement to grant privileges to the created account.

MySQL GRANT examples

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

If you want to create asuperaccount that can do anything including being able to grant privileges to other users, you can use the following statements:

CREATEUSER'super'@'localhost'IDENTIFIEDBY'SecurePass1';GRANTALLON*.*TO'super'@'localhost'WITHGRANTOPTION;

TheON *.*clause means all databases and all objects in the databases. The only limitation of thesuperuser is that itcan only connect to the database server from thelocalhost, 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:

CREATEUSER'super2'@'%'IDENTIFIEDBY'SecurePass2';GRANTALLclassicmodels.*TO'super2'@'%'WITHGRANTOPTION;

You can grant multiple privileges using a singleGRANTstatement. For example, you can create a user that can execute theSELECT,INSERTandUPDATEstatements against theclassicmodelssample database using the following statements:

CREATEUSER'rfc'@'%'IDENTIFIEDBY'SecurePass3';GRANTSELECT,UPDATE,DELETEONclassicmodels.*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 MySQLGRANTstatement to grant privileges to accounts.

Related TutorialsMySQL Changing Password for Accounts

MySQL Create User

Getting Started with MySQL Access Control System

原文链接：http://outofmemory.cn/mysql/administration/mysql-grant