☰

如何知道所有可以访问数据库（MySQL）的用户？

tag-icon • tag-icon mysql users
如何知道所有可以访问数据库（MySQL）的用户？

如何知道所有可以访问数据库的用户？

答案1

以管理员用户（通常是 root）身份连接到 mysql 实例并输入以下命令...

select user from mysql.db where db='DB_NAME';

答案2

user79644 的答案会获取具有数据库级权限的用户，但会错过仅具有表级、列级或过程级权限的用户。要查找所有这些，请使用以下语句：

SELECT user,host FROM db WHERE db='name';
SELECT user,host FROM tables_priv WHERE db='name';
SELECT user,host FROM columns_priv WHERE db='name';
SELECT user,host FROM procs_priv WHERE db='name';

至少在 MySQL 5.5 中，拥有列级权限似乎意味着您拥有表级权限。拥有表级权限并不意味着您拥有数据库级权限。不确定过程级权限是否如此。

答案3

# current users that access the db
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 214 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+

# who can access what at anytime and his privilege level
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

# what privileges are available
mysql> show privileges;

答案4

你必须记住，MySQLGRANT数据库可以包含通配符LIKE。这必须通过在查询中使用来解决：

SELECT user,host FROM db WHERE 'name' LIKE db;
SELECT user,host FROM tables_priv WHERE db='name';
SELECT user,host FROM columns_priv WHERE db='name';
SELECT user,host FROM procs_priv WHERE db='name';

相关内容