显示对数据库具有任何权限的用户。MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7520036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show users with any privileges to database. MySQL
提问by Micha? Skóra
i have to select all users with any privileges to database (e.g. database 'mysql'). Any suggestions? Thanks.
我必须选择对数据库具有任何权限的所有用户（例如数据库“mysql”）。有什么建议？谢谢。
采纳答案by Marc B
Look the in mysqldatabase (an actual db named mysqlinside the mysql server, just to be clear). There's three tables (db, tables_priv, and columns_priv) where the db/table/column privs are stored:
查看mysql数据库（mysql在 mysql 服务器内部命名的实际数据库，只是为了清楚）。有三个表（db、tables_priv和columns_priv），其中存储了 db/table/column privs：
SELECT 'db', User, Host
FROM db
WHERE Db='mydatabase'
UNION
SELECT 'table', User, Host
FROM tables_priv
WHERE Db='mydatabase'
UNION
SELECT 'col', User, Host
FROM columns_priv
WHERE Db='mydatabase'
should show you what you need.
应该告诉你你需要什么。
回答by Sukhjinder Singh
A good view of all users and their approximate privileges. If there is a password, it will by an encrytped string; if not, this field is blank. Select is a very general privlege; insert allows table manipulation within a database; shutdown allows major system changes, and should only be usable by root; the ability to grant permissions is separate from the others.
所有用户及其大致权限的良好视图。如果有密码，它会通过一个加密的字符串；如果不是，则此字段为空。Select 是一个非常普遍的特权；insert 允许在数据库中进行表操作；关闭允许重大系统更改，并且只能由 root 用户使用；授予权限的能力与其他能力是分开的。
SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv
FROM mysql.user
View permissions for individual databases.
查看单个数据库的权限。
SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db
回答by dnozay
database privilegesare stored in
mysql.dbtable privilegesare stored in
mysql.tables_privcolumn privilegesare stored in
mysql.columns_privroutine privilegesare stored in
mysql.proc_privs
You can define a store procedure to list the privileges:
您可以定义一个存储过程来列出权限：
delimiter //
CREATE PROCEDURE list_privileges (IN db_name CHAR(50))
BEGIN
SELECT concat(Db,'.', '*') as 'what', User, Host, '...' as 'perms'
FROM mysql.db
WHERE Db=db_name
UNION
SELECT concat(Db,'.', Table_name), User, Host, table_priv
FROM mysql.tables_priv
WHERE Db=db_name and table_priv != ''
UNION
SELECT concat(Db,'.', Table_name, '(', Column_name,')'), User, Host, Column_priv
FROM mysql.columns_priv
WHERE Db=db_name
UNION
SELECT concat(Db,'.', Routine_name, '()'), User, Host, Proc_priv
FROM mysql.procs_priv
WHERE Db=db_name;
END//
delimiter ;
example:
例子：
mysql> call list_privileges("testlink2");
+-----------------------------+-----------+-----------+---------+
| what | User | Host | perms |
+-----------------------------+-----------+-----------+---------+
| testlink2.* | testlink2 | % | ... |
| testlink2.* | testlink2 | localhost | ... |
| testlink2.executions | testlink2 | % | Select |
| testlink2.users(id) | testlink2 | % | Select |
| testlink2.list_privileges() | testlink2 | % | Execute |
+-----------------------------+-----------+-----------+---------+
5 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
