如何查看mysql的使用者及授權

標籤：mysql 許可權

1）查看mysql中存在的使用者

mysql> select user,host from mysql.user;+------------+--------------------------+| user       | host                     |+------------+--------------------------+| oldgirl    | %                        || wan        | %                        || wanlong    | %                        || rep        | 10.10.10.%               || root       | 10.10.10.%               || wan        | 10.10.10.%               || wanlong    | 10.10.10.%               || oldgril123 | 10.10.10.0/255.255.255.0 || root       | 127.0.0.1                || root       | ::1                      || root       | C67-X64-A8               || backup     | localhost                || root       | localhost                |+------------+--------------------------+13 rows in set (0.00 sec)

2）如何查看使用者的授權

mysql> show grants for ‘wanlong‘@‘10.10.10.%‘;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                                                                                                                                                                                                                                                              |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO ‘wanlong‘@‘10.10.10.%‘ |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

3）all privileges具備哪些許可權

a、建立測試帳號並進行授權：

mysql> create user wanlong identified by ‘wanlong‘;Query OK, 0 rows affected (0.01 sec)mysql> grant all privileges on *.* to ‘wanlong‘@‘10.10.10.%‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for ‘wanlong‘@‘10.10.10.%‘;+-------------------------------------------------------+| Grants for [email protected]%                         |+-------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘wanlong‘@‘10.10.10.%‘ |+-------------------------------------------------------+1 row in set (0.00 sec)

b、回收insert許可權，並查看使用者的許可權

mysql> revoke insert on *.* from ‘wanlong‘@‘10.10.10.%‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> show grants for ‘wanlong‘@‘10.10.10.%‘;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                                                                                                                                                                                                                                                              |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO ‘wanlong‘@‘10.10.10.%‘ |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

c、匯出檔案，並查看授權（all privileges還需要添加剛才使用revoke取消的insert授權）

# mysql -uroot -predhat12345 -S /data/3306/mysql.sock -e "show grants for ‘wanlong‘@‘10.10.10.%‘;"|grep -i grant|tail -1|tr ‘,‘ ‘\n‘>all.privileges查看all privileges具備的許可權：# cat all.privileges GRANT SELECT UPDATE DELETE CREATE DROP RELOAD SHUTDOWN PROCESS FILE REFERENCES INDEX ALTER SHOW DATABASES SUPER CREATE TEMPORARY TABLES LOCK TABLES EXECUTE REPLICATION SLAVE REPLICATION CLIENT CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER CREATE TABLESPACE ON *.* TO ‘wanlong‘@‘10.10.10.%‘

本文出自 “冰凍vs西瓜” 部落格，請務必保留此出處http://molewan.blog.51cto.com/287340/1861834

如何查看mysql的使用者及授權