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

问题描述

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

我希望有一个用户对一个数据库(或一系列具有相同架构的数据库)拥有所有权限，除了一个表，他们只有 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;")来自 information_schema.TABLESWHERE 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屋！