MySQL向数据库授予除一个表以外的所有特权 [英] MySQL grant all privileges to database except one table

问题描述

我一直无法找到实现以下目标的合理解决方案:

我希望有一个对数据库(或具有相同模式的一系列数据库)具有ALL特权的用户，对于一个表，除外，它们将仅具有SELECT特权. >

本质上，我希望用户可以自由支配数据库，但不能更新特定的表.

到目前为止，我已经尝试了，但无济于事:

• 授予该数据库(db_name.*)的所有特权，然后专门仅授予该所需表的选择特权(希望它会覆盖全部"，我知道这很愚蠢).

• 授予该数据库(db_name.*)的所有特权，然后撤消插入，更新和删除.但这产生了一个错误，表明db_name.table_name没有授予规则.

根据我的能力，我将不得不分别授予数据库每个表(只读表除外)的所有特权.

请有人告诉我有一种更简单的方法

注意:我正在运行MySQL 5.1.最新的版本可在Ubuntu 10.04上获得.

解决方案

我知道这是一篇过时的文章，但我想我应该补充@tdammers问题，以供其他人参考.您还可以在information_schema.tables上执行SELECT CONCAT来创建授权命令，而不必编写单独的脚本.

首先撤消该数据库的所有特权:

REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;  

然后创建您的GRANT语句:

SELECT CONCAT("GRANT UPDATE ON db.", table_name, " TO user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "YourDB" AND table_name <> "table_to_skip";

将结果复制并粘贴到MySQL客户端中，然后全部运行.

I've been unable to find a reasonable solution to achieve the following:

I wish to have a user that has ALL privileges on a database (or series of databases with the same schema), except for one table, to which they will only have SELECT privileges.

Essentially I want the user to have free reign over a database but not to be able to update a specific table.

So far I have tried, to no avail:

• Granting all privileges on that database (db_name.*) and then specifically granting only select privileges on that desired table (hoping it would overwrite the "all", stupid I know).

• Granting all privileges on that database (db_name.*) then revoking insert, update, and delete. But this produced an error saying there was no grant rule for db_name.table_name.

From what I've been able to gather I'll have to individually grant all privileges on each table of the database except the read only table.

Please someone tell me there is a easier way

Note: I'm running MySQL 5.1. The latest available on Ubuntu 10.04.

解决方案

I know this is an old post, but I thought I'd add on to @tdammers question for others to see. You can also perform a SELECT CONCAT on information_schema.tables to create your grant commands, and not have to write a separate script.

First revoke all privileges from that db:

REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;  

Then create your GRANT statements:

SELECT CONCAT("GRANT UPDATE ON db.", table_name, " TO user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "YourDB" AND table_name <> "table_to_skip";

Copy and paste the results into your MySQL client and run them all.

这篇关于MySQL向数据库授予除一个表以外的所有特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！