本文共 9891 字，大约阅读时间需要 32 分钟。
global privilegesOR (database privileges AND host privileges)OR table privilegesOR column privilegesOR routine privileges
Table 12.1. Permissible Privileges for GRANT and REVOKEPrivilege MeaningALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTIONALTER Enable use of ALTER TABLEALTER ROUTINE Enable stored routines to be altered or droppedCREATE Enable database and table creationCREATE ROUTINE Enable stored routine creationCREATE TABLESPACE Enable tablespaces and log file groups to be created, altered, or droppedCREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLECREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGESCREATE VIEW Enable views to be created or alteredDELETE Enable use of DELETEDROP Enable databases, tables, and views to be droppedEVENT Enable use of events for the Event SchedulerEXECUTE Enable the user to execute stored routinesFILE Enable the user to cause the server to read or write filesGRANT OPTION Enable privileges to be granted to or removed from other accountsINDEX Enable indexes to be created or droppedINSERT Enable use of INSERTLOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilegePROCESS Enable the user to see all processes with SHOW PROCESSLISTPROXY Enable user proxyingREFERENCES Not implementedRELOAD Enable use of FLUSH operationsREPLICATION CLIENT Enable the user to ask where master or slave servers areREPLICATION SLAVE Enable replication slaves to read binary log events from the masterSELECT Enable use of SELECTSHOW DATABASES Enable SHOW DATABASES to show all databasesSHOW VIEW Enable use of SHOW CREATE VIEWSHUTDOWN Enable use of mysqladmin shutdownSUPER Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug commandTRIGGER Enable trigger operationsUPDATE Enable use of UPDATEUSAGE Synonym for “no privileges”
REPLICATION CLIENT 与 REPLICATION SLAVE区别，前者只能使用SHOW MASTER STATUS和SHOW SLAVE STATUS命令监控复制状态，后者才能从主库复制binlog.
mysql> SHOW GRANTS FOR 'root'@'localhost';+---------------------------------------------------------------------+| Grants for root@localhost |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |+---------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> show grants;+---------------------------------------------------------------------------------+| Grants for root@localhost |+---------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |+---------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> show privileges;+-------------------------+---------------------------------------+-------------------------------------------------------+| Privilege | Context | Comment |+-------------------------+---------------------------------------+-------------------------------------------------------+| Alter | Tables | To alter the table || Alter routine | Functions,Procedures | To alter or drop stored functions/procedures || Create | Databases,Tables,Indexes | To create new databases and tables || Create routine | Databases | To use CREATE FUNCTION/PROCEDURE || Create temporary tables | Databases | To use CREATE TEMPORARY TABLE || Create view | Tables | To create new views || Create user | Server Admin | To create new users || Delete | Tables | To delete existing rows || Drop | Databases,Tables | To drop databases, tables, and views || Event | Server Admin | To create, alter, drop and execute events || Execute | Functions,Procedures | To execute stored routines || File | File access on server | To read and write files on the server || Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess || Index | Tables | To create or drop indexes || Insert | Tables | To insert data into tables || Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) || Process | Server Admin | To view the plain text of currently executing queries || Proxy | Server Admin | To make proxy user possible || References | Databases,Tables | To have references on tables || Reload | Server Admin | To reload or refresh tables, logs and privileges || Replication client | Server Admin | To ask where the slave or master servers are || Replication slave | Server Admin | To read binary log events from the master || Select | Tables | To retrieve rows from table || Show databases | Server Admin | To see all databases with SHOW DATABASES || Show view | Tables | To see views with SHOW CREATE VIEW || Shutdown | Server Admin | To shut down the server || Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. || Trigger | Tables | To use triggers || Create tablespace | Server Admin | To create/alter/drop tablespaces || Update | Tables | To update existing rows || Usage | Server Admin | No privileges - allow connect only |+-------------------------+---------------------------------------+-------------------------------------------------------+31 rows in set (0.00 sec)
Global privileges
GRANT ALL ON *.* TO 'someuser'@'somehost';GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
Database privileges
GRANT ALL ON mydb.* TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
Table privileges
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
Column privileges
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
Routine privileges
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
mysql> select * from user where user = 'neo'\G*************************** 1. row *************************** Host: 192.168.0.5 User: neo Password: *7564B7B0A062C9523700601CBA1DCE1F861D6270 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 01 row in set (0.00 sec)mysql>
GRANT USAGE ON *.* TO ... WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
授权tmp用户只能访问tabname表
GRANT ALL PRIVILEGES ON tmp.tabname TO 'tmp'@'%' IDENTIFIED BY 'chen' WITH GRANT OPTION;
如果用户已经存在仅仅是分配权限可以使用下面方法
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
mydb.mytbl 表 col1字段允许查询，col1,col2允许插入
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';