怎样查看mysql授权_如何查看mysql的用户及授权

最新推荐文章于 2024-04-18 15:42:41 发布

一个很无聊的人

最新推荐文章于 2024-04-18 15:42:41 发布

阅读量1.6k

点赞数 2

文章标签：怎样查看mysql授权

本文链接：https://blog.youkuaiyun.com/weixin_31939167/article/details/113289651

版权

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.% |

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