命令

SHOW GRANTS

官方文档: http://dev.mysql.com/doc/refman/5.6/en/show-grants.html

常看帮助

:::mysql
mysql> HELP SHOW GRANTS;

查看当前登陆用户在当前机器上的权限

:::mysql
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR CURRENT_USER;
mysql> SHOW GRANTS FOR CURRENT_USER();

查看特定用户在特定主机上的权限

:::mysql
# 查看test用户在主机'%'上的权限(没有指定主机时,默认是'%')
mysql> SHOW GRANTS FOR 'test';
# 查看test用户在主机'localhost'上的权限(没有指定主机时,默认是'%')
mysql> SHOW GRANTS FOR 'test'@'localhost';

查看用户在哪些主机上分配了权限

:::mysql
mysql> select user,host from mysql.user;

各种权限用法表

参考自 http://dev.mysql.com/doc/refman/5.6/en/grant.html

Table 13.1 Permissible Privileges for GRANT and REVOKE

PrivilegeMeaning and Grantable Levels
ALL [PRIVILEGES]Grant all privileges at specified access level except GRANT OPTION
ALTEREnable use of ALTER TABLE. Levels: Global, database, table.
ALTER ROUTINEEnable stored routines to be altered or dropped. Levels: Global, database, procedure.
CREATEEnable database and table creation. Levels: Global, database, table.
CREATE ROUTINEEnable stored routine creation. Levels: Global, database.
CREATE TABLESPACEEnable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
CREATE TEMPORARY TABLESEnable use of CREATE TEMPORARY TABLE. Levels: Global, database.
CREATE USEREnable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.
CREATE VIEWEnable views to be created or altered. Levels: Global, database, table.
DELETEEnable use of DELETE. Level: Global, database, table.
DROPEnable databases, tables, and views to be dropped. Levels: Global, database, table.
EVENTEnable use of events for the Event Scheduler. Levels: Global, database.
EXECUTEEnable the user to execute stored routines. Levels: Global, database, table.
FILEEnable the user to cause the server to read or write files. Level: Global.
GRANT OPTIONEnable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy.
INDEXEnable indexes to be created or dropped. Levels: Global, database, table.
INSERTEnable use of INSERT. Levels: Global, database, table, column.
LOCK TABLESEnable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.
PROCESSEnable the user to see all processes with SHOW PROCESSLIST. Level: Global.
PROXYEnable user proxying. Level: From user to user.
REFERENCESNot implemented
RELOADEnable use of FLUSH operations. Level: Global.
REPLICATION CLIENTEnable the user to ask where master or slave servers are. Level: Global.
REPLICATION SLAVEEnable replication slaves to read binary log events from the master. Level: Global.
SELECTEnable use of SELECT. Levels: Global, database, table, column.
SHOW DATABASESEnable SHOW DATABASES to show all databases. Level: Global.
SHOW VIEWEnable use of SHOW CREATE VIEW. Levels: Global, database, table.
SHUTDOWNEnable use of mysqladmin shutdown. Level: Global.
SUPEREnable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
TRIGGEREnable trigger operations. Levels: Global, database, table.
UPDATEEnable use of UPDATE. Levels: Global, database, table, column.
USAGESynonym for no privileges