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.

我希望有一个用户拥有数据库（或具有相同模式的一系列数据库）的所有权限，除了一个表，他们只有SELECT权限。

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).

授予该数据库的所有权限（db_name。*），然后专门授予该所需表的select权限（希望它会覆盖“all”，我知道这个愚蠢）。
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.

授予该数据库的所有权限（db_name。*），然后撤消插入，更新和删除。但是这产生了一个错误，表明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.

注意：我正在运行MySQL 5.1。 Ubuntu 10.04上的最新版本。

3 个解决方案

#1

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.

我知道这是一个老帖子，但我想我会加入@tdammers问题让别人看。您还可以在information_schema.tables上执行SELECT CONCAT以创建grant命令，而不必编写单独的脚本。

First revoke all privileges from that db:

首先撤消该db的所有权限：

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

Then create your GRANT statements:

然后创建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";

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

将结果复制并粘贴到MySQL客户端并运行它们。

#2

AFAIK, yes, you need to grant individually per table. But hey, you have a computer there. Computers are great at automating repetitive tasks for you, so why don't you make a script that does the following:

AFAIK，是的，您需要为每个表单独授予。但是，嘿，你那里有一台电脑。计算机非常适合为您自动执行重复性任务，那么为什么不制作执行以下操作的脚本：

Get a list of all tables in the database (SHOW TABLES;)
获取数据库中所有表的列表（SHOW TABLES;）
For each item on the list, grant all permissions
对于列表中的每个项目，授予所有权限
Revoke permissions on the special table
撤消特殊表的权限

Or, alternatively: 2. For each item on the list, check if it is the special table; if it's not, grant all permissions

或者，或者：2。对于列表中的每个项目，检查它是否是特殊表格;如果不是，则授予所有权限

The reason I'm not giving code is that it can be done in any scripting language with MySQL facilities, even shell script; use what you're most comfortable using.

我不提供代码的原因是它可以使用MySQL设备的任何脚本语言完成，甚至是shell脚本;使用你最舒服的东西。

#3

Here is a draft of what I use to grant roles in MariaDB. Maybe setting an EVENT would make it more cool :-)

这是我用于在MariaDB中授予角色的草稿。也许设置一个EVENT会让它变得更酷:-)

DELIMITER $$

DROP PROCEDURE IF EXISTS refreshRoles $$
CREATE PROCEDURE refreshRoles ()
  COMMENT 'Grant SELECT on new databases/tables, revoke on deleted'
BEGIN
  DECLARE done BOOL;
  DECLARE db VARCHAR(128);
  DECLARE tb VARCHAR(128);
  DECLARE rl VARCHAR(128);
  DECLARE tables CURSOR FOR
    SELECT table_schema, table_name, '_bob_live_sg' FROM information_schema.tables
    WHERE table_schema LIKE '%bob\_live\_sg' AND
      (  false
      OR table_name LIKE 'bundle%'
      OR table_name LIKE 'cart%'
      OR table_name LIKE 'catalog%'
      OR table_name LIKE 'url%'
      );

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;

  CREATE ROLE IF NOT EXISTS '_bob_live_sg';
  REVOKE ALL, GRANT OPTION FROM '_bob_live_sg';

  OPEN tables;
  SET done = false;
  grant_loop: LOOP
    FETCH tables INTO db, tb, rl;
    IF done THEN
      LEAVE grant_loop;
    END IF;
    SET @g = CONCAT('GRANT SELECT ON `', db, '`.`', tb, '` TO ', rl);
    PREPARE g FROM @g;
    EXECUTE g;
    DEALLOCATE PREPARE g;
  END LOOP;
  CLOSE tables;
END $$

DELIMITER ;

CALL refreshRoles;

#1