用户在 MySQL 中具有 SELECT 权限的表列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5024668/
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
List of tables that a user has SELECT privilege for in MySQL
提问by Scott Deerwester
Short version: How can I write an SQL procedure to list which of several tables in a MySQL database a particular user has access to?
简短版本：如何编写 SQL 过程来列出特定用户可以访问的 MySQL 数据库中的几个表？
Longer version:
更长的版本：
I'm writing a multi-user app that accesses a database with data for several branches of a company. The database has a number of lookup tables that any user can access, and a table for each branch that only authorized users can access. My strategy is:
我正在编写一个多用户应用程序，该应用程序访问包含公司多个分支机构数据的数据库。数据库有若干个任何用户都可以访问的查找表，每个分支都有一个只有授权用户才能访问的表。我的策略是：
- Write a stored procecure that returns a list of the relevant tables for which the user has SELECT privilege.
- From the app, call the procedure. If there's only one table returned, use it, otherwise let the user select which branch they want to access (e.g. for managers).
- 编写一个存储过程，返回用户具有 SELECT 权限的相关表的列表。
- 从应用程序中，调用该过程。如果只有一个表返回，使用它，否则让用户选择他们想要访问的分支（例如，对于经理）。
I'm having trouble figuring out how to write such a stored procedure. SHOW GRANTS FOR CURRENT_USERis an obvious possibility, but parsing something like:
我在弄清楚如何编写这样一个存储过程时遇到了麻烦。SHOW GRANTS FOR CURRENT_USER是一个明显的可能性，但解析如下：
GRANT SELECT ON Company.BranchABC TO 'auser'@'%clientdomain.com'
in SQL to figure out what the tables are seems waytoo messy. Doing a SELECTfrom the actual tables that hold the permissions also seems problematic, because I'd have to duplicate MySQL's logic for combining the permissions from the various tables (user, db, host, etc.)
在SQL弄清楚表是什么，似乎这样太乱了。SELECT从拥有权限的实际表中执行 a似乎也有问题，因为我必须复制 MySQL 的逻辑来组合来自各种表（用户、数据库、主机等）的权限。
Any words of wisdom?
有什么智慧的话吗？
采纳答案by Jesse Bunch
I have never granted per table permissions to MySQL users before, but to do this, you would check that the TABLE_PRIVILEGEStable in the information_schemadatabase.
我以前从未向 MySQL 用户授予每个表的权限，但要做到这一点，您需要检查数据库中的TABLE_PRIVILEGES表information_schema。
That should point you in the right direction.
这应该为您指明正确的方向。
回答by Mohsen Abasi
You can see what privileges on which tables a user has:
您可以查看用户对哪些表具有哪些权限：
show grants for 'user'@'host';
For example, to see the privileges of user1 (all machines in the network 10.25), run:
例如，要查看 user1（网络 10.25 中的所有机器）的权限，请运行：
show grants for 'user'@'10.25.%.%';
回答by Vikash Sinha
MySQL users list and its privilege can be check with the Query.
MySQL 用户列表及其权限可以通过 Query 查询。
select * from mysql.user\G;
http://www.thedevheaven.com/2012/04/retrieve-mysql-users-list-and-its.html
http://www.thedevheaven.com/2012/04/retrieve-mysql-users-list-and-its.html
