| 知乎專欄 ｜ 多維度架構 |
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges OR routine privileges
Table 12.1. Permissible Privileges for GRANT and REVOKE Privilege Meaning ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION ALTER Enable use of ALTER TABLE ALTER ROUTINE Enable stored routines to be altered or dropped CREATE Enable database and table creation CREATE ROUTINE Enable stored routine creation CREATE TABLESPACE Enable tablespaces and log file groups to be created, altered, or dropped CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES CREATE VIEW Enable views to be created or altered DELETE Enable use of DELETE DROP Enable databases, tables, and views to be dropped EVENT Enable use of events for the Event Scheduler EXECUTE Enable the user to execute stored routines FILE Enable the user to cause the server to read or write files GRANT OPTION Enable privileges to be granted to or removed from other accounts INDEX Enable indexes to be created or dropped INSERT Enable use of INSERT LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege PROCESS Enable the user to see all processes with SHOW PROCESSLIST PROXY Enable user proxying REFERENCES Not implemented RELOAD Enable use of FLUSH operations REPLICATION CLIENT Enable the user to ask where master or slave servers are REPLICATION SLAVE Enable replication slaves to read binary log events from the master SELECT Enable use of SELECT SHOW DATABASES Enable SHOW DATABASES to show all databases SHOW VIEW Enable use of SHOW CREATE VIEW SHUTDOWN Enable use of mysqladmin shutdown SUPER Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command TRIGGER Enable trigger operations UPDATE Enable use of UPDATE USAGE Synonym for “no privileges”http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-table-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: Y
Create_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: 0
1 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';