授予用户访问 MySQL 中有限数量的表的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8369253/
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
Grant user access to limited number of tables in MySQL
提问by enchance
I'm running some tests and I'm trying to see if I can create a user with access to only 1 or 2 tables in my db. Does anyone know how this is done? My code below fails:
我正在运行一些测试，我正在尝试查看是否可以创建一个用户，该用户只能访问我的数据库中的 1 或 2 个表。有谁知道这是怎么做的？我下面的代码失败：
GRANT SELECT ON testdb.fruits, testdb.sports TO joe@localhost IDENTIFIED BY 'pass';
The error says I have an error in my syntax.
错误说我的语法有错误。
回答by Michael Berkowski
Run them as two individual GRANTstatements:
将它们作为两个单独的GRANT语句运行：
GRANT SELECT ON testdb.fruits TO joe@localhost IDENTIFIED BY 'pass';
GRANT SELECT ON testdb.sports TO joe@localhost IDENTIFIED BY 'pass';
The MySQL GRANT syntaxonly permits one object in the priv_levelposition:, though it may use a *as a wildcard:
MySQL GRANT 语法只允许一个对象在priv_level位置：，尽管它可以使用 a*作为通配符：
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
The part below does not appear to work on MySQL 5.5. How to "subtract" privileges in MySQLaddresses why.
下面的部分似乎不适用于 MySQL 5.5。如何在 MySQL 中“减去”权限解决了原因。
To grant SELECTon all tables then selectively revoke, you could do:
要授予SELECT所有表然后有选择地撤销，您可以执行以下操作：
GRANT SELECT ON testdb.* TO joe@localhost IDENTIFIED BY 'pass';
REVOKE ALL PRIVILEGES ON testdb.tblname FROM joe@localhost;
GRANT SELECT ON testdb.* TO joe@localhost IDENTIFIED BY 'pass';
REVOKE ALL PRIVILEGES ON testdb.tblname FROM joe@localhost;
This seems to be an odd method though, and I think I would individually GRANT rather than individually REVOKE.
不过，这似乎是一种奇怪的方法，我认为我会单独授予而不是单独撤销。
回答by Kip
You can use the mysql.tables_priv tabledirectly:
您可以直接使用mysql.tables_priv 表：
INSERT INTO mysql.tables_priv (`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Table_priv`)
VALUES
('%', DATABASE(), 'someuser', 'mytable1', CURRENT_USER, 'Select,Insert,Update,Delete'),
('%', DATABASE(), 'someuser', 'mytable2', CURRENT_USER, 'Select,Insert,Update,Delete')
After a manual update to these tables, you will need to explicitly run FLUSH PRIVILEGESquery to tell MySQL to update its permissions cache (not required when using GRANT)
手动更新这些表后，您将需要显式运行FLUSH PRIVILEGES查询以告诉 MySQL 更新其权限缓存（使用时不需要GRANT）
