首页 > 代码库 > 如何查看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 wanlong@10.10.10.% | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 wanlong@10.10.10.% | +-------------------------------------------------------+ | 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 wanlong@10.10.10.% | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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的用户及授权
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。