授予几乎所有MySQL权限，但对一个/几个表除外

Question

问题：

如何给一个新用户几乎所有的权限，但仍然保护其中一个或多个表/数据库。

详细信息：

如果我有一个数据库，

• life

和三个表

• passwords
• friends
• hobbies

如何给这个用户以下权限：

• 插入
• 更新
• 删除
• 创建
• 删除
• 更改

对于前三个权限，可以从以下方式开始：

GRANT INSERT, UPDATE, DELETE ON life.friends TO username@'localhost' IDENTIFIED BY 'password';
GRANT INSERT, UPDATE, DELETE ON life.hobbies TO username@'localhost' IDENTIFIED BY 'password';

但是我对如何使用CREATE和DROP感到困惑。如果我像这样授予整个数据库的删除权限：

GRANT DROP ON life TO username@'localhost' IDENTIFIED BY 'password';

然后用户可以删除 passwords 表，但我不希望这样。相反，我可以根据表授予权限，如下所示：

GRANT DROP ON life.friends TO username@'localhost' IDENTIFIED BY 'password';
GRANT DROP ON life.hobbies TO username@'localhost' IDENTIFIED BY 'password';

如果我授予CREATE权限，会发生什么情况：

GRANT CREATE ON life TO username@'localhost' IDENTIFIED BY 'password';

这是否意味着用户甚至无法删除他/她创建的表？我的问题还涉及创建/删除数据库。如果我想允许用户创建和删除尽可能多的他们自己的数据库，但不包括life数据库怎么办？

难道我应该改变方法，将passwords表移动到另一个数据库吗？

提前感谢。

- puk

---

分离数据库是正确的选择。MySql 权限有限，不允许拒绝权限。 - The Scrum Meister

@TheScrumMeister 如果你发布它，我将接受它作为答案。 - puk

作为一个处理过 MySql 权限的人，这是我遇到的（众多）限制之一。 - The Scrum Meister

你在这方面有任何进展吗？ - Mike Purcell

@MikePurcell 有点。我正在使用单独的数据库来进行CREATE/DROP权限，使用GRANT来给用户访问特定列的权限，并使用VIEWS来仅给用户访问特定行的权限。 - puk

Answer 1

@TheScrumMeister提出的使用独立数据库的方法可以有替代方案，即定义一个过程来包装“CREATE TABLE”，同时授予调用用户“DROP”权限：

DELIMITER ;;

CREATE PROCEDURE create_table(
  tbl_name VARCHAR(64) -- maximum length of a table name
) BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
    SET @usr = SUBSTRING_INDEX(USER(), '@', 1);  -- invoking username
    SET @tbl = REPLACE(tbl_name, "`", "``");     -- prevent SQL injection

    -- just create some dummy table initially - the user can modify it after
    SET @qry = CONCAT("
      CREATE TABLE `", @tbl, "` (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
      );
    ");
    PREPARE stmt FROM  @qry;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- now grant DROP to the invoking user but only from localhost
    SET @qry = CONCAT("
      GRANT DROP ON life.`", @tbl, "`
      TO CONCAT(?, \"@'localhost'\") IDENTIFIED BY 'password';
    ");
    PREPARE stmt FROM  @qry;
    EXECUTE stmt USING @usr;
    DEALLOCATE PREPARE stmt;

    -- clean up
    SET @qry = NULL;
    SET @usr = NULL;
  COMMIT;
END;;

DELIMITER ;

为确保用户不会以其他方式创建表，他们不应该拥有CREATE TABLE权限。

对于数据库也可以采取类似的措施。

- eggyal

---

我从未想过那个。谢谢。 - puk