MySQL查看用户权限

Tags mysql user grants By crazygit On 2014-12-17

命令

SHOW GRANTS

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

常看帮助

mysql> HELP SHOW GRANTS;

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

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

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

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

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

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

Privilege	Meaning and Grantable Levels
`ALL [PRIVILEGES]`	Grant all privileges at specified access level except `GRANT OPTION`
`ALTER`	Enable use of `ALTER TABLE`. Levels: Global, database, table.
`ALTER ROUTINE`	Enable stored routines to be altered or dropped. Levels: Global, database, procedure.
`CREATE`	Enable database and table creation. Levels: Global, database, table.
`CREATE ROUTINE`	Enable stored routine creation. Levels: Global, database.
`CREATE TABLESPACE`	Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
`CREATE TEMPORARY TABLES`	Enable use of `CREATE TEMPORARY TABLE`. Levels: Global, database.
`CREATE USER`	Enable use of `CREATE USER`, `DROP USER`, `RENAME USER`, and `REVOKE ALL PRIVILEGES`. Level: Global.
`CREATE VIEW`	Enable views to be created or altered. Levels: Global, database, table.
`DELETE`	Enable use of `DELETE`. Level: Global, database, table.
`DROP`	Enable databases, tables, and views to be dropped. Levels: Global, database, table.
`EVENT`	Enable use of events for the Event Scheduler. Levels: Global, database.
`EXECUTE`	Enable the user to execute stored routines. Levels: Global, database, table.
`FILE`	Enable the user to cause the server to read or write files. Level: Global.
`GRANT OPTION`	Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy.
`INDEX`	Enable indexes to be created or dropped. Levels: Global, database, table.
`INSERT`	Enable use of `INSERT`. Levels: Global, database, table, column.
`LOCK TABLES`	Enable use of `LOCK TABLES` on tables for which you have the `SELECT` privilege. Levels: Global, database.
`PROCESS`	Enable the user to see all processes with `SHOW PROCESSLIST`. Level: Global.
`PROXY`	Enable user proxying. Level: From user to user.
`REFERENCES`	Not implemented
`RELOAD`	Enable use of `FLUSH` operations. Level: Global.
`REPLICATION CLIENT`	Enable the user to ask where master or slave servers are. Level: Global.
`REPLICATION SLAVE`	Enable replication slaves to read binary log events from the master. Level: Global.
`SELECT`	Enable use of `SELECT`. Levels: Global, database, table, column.
`SHOW DATABASES`	Enable `SHOW DATABASES` to show all databases. Level: Global.
`SHOW VIEW`	Enable use of `SHOW CREATE VIEW`. Levels: Global, database, table.
`SHUTDOWN`	Enable use of mysqladmin shutdown. Level: Global.
`SUPER`	Enable use of other administrative operations such as `CHANGE MASTER TO`, `KILL`, `PURGE BINARY LOGS`, `SET GLOBAL`, and mysqladmin debug command. Level: Global.
`TRIGGER`	Enable trigger operations. Levels: Global, database, table.
`UPDATE`	Enable use of `UPDATE`. Levels: Global, database, table, column.
`USAGE`	Synonym for “no privileges”