如何编写好的Mysql grants脚本

Question

我正在使用脚本来创建 Mysql 数据库和表。这些脚本包含如下所示的授予部分：

GRANT SELECT ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
REVOKE ALL PRIVILEGES ON my_database.* FROM my_user@"%";
GRANT SELECT, UPDATE ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';

最初，我只使用了第三行，但遇到了以下问题：每当我从用户中删除权限 Q 并重新运行该脚本时，该用户在数据库中仍然具有该权限。所以我在授予行之前添加了撤销行。

然后我遇到了以下问题：每当我在“全新”Mysql 安装上运行脚本时，撤销都会失败，因为用户尚不存在。因此，我在撤销之前添加了“虚拟”补助金。

问题：有没有更好的方法来实现这个目标？我的“真实”脚本包含大量用户和大量数据库，并且很难阅读，因为我需要三行来表示我想要分配的每组权限。我只想用一根线。

编辑（基于答案和评论的反馈）：

我正在寻找最短的方式来表达诸如

SET PRIVILEGES SELECT, UPDATE
ON my_database.*
TO my_user@"%"
IDENTIFIED BY 'my_password';

my_user 可能

• 已经存在（但可能是新的）
• 当前拥有的内容扩展我希望他拥有的
• 其他数据库的权限，这些权限必须不受影响

Answer 1

如有必要，您可以使用过程创建新用户并授予数据库权限。我使用了 准备好的语句 和 GRANT 语句。 MySQL 5.5中的Prepared statements支持GRANT，如果您使用的是较低版本，那么您可以将GRANT命令重写为INSERT INTO。

使用测试；

DELIMITER $

CREATE PROCEDURE procedure_user(
  IN host_name VARCHAR(60),  IN user_name VARCHAR(60),
  IN db_name   VARCHAR(255),
  IN db_privs  VARCHAR(255))
BEGIN
  SELECT 1 INTO @exist FROM mysql.user WHERE user = user_name AND host = host_name;

  -- Create new user, generate command like this: CREATE USER 'user1'@'%';;
  IF @exist IS NULL THEN

    SET @sql = CONCAT('CREATE USER ''', user_name, '''@''', host_name, '''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

  -- Generate command like this: GRANT INSERT, UPDATE ON database1.* TO 'user1'@'%';
  SET @sql = CONCAT('GRANT ', db_privs, ' ON ', db_name, '.* TO ''', user_name, '''@''', host_name, '''');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$

DELIMITER ;

使用示例：

-- First command will create new user user1@% and will grant SELECT, INSERT, UPDATE privileges to database1.
CALL procedure_user('%', 'user1', 'database1', 'SELECT, INSERT, UPDATE');

-- Second command just will grant SELECT, INSERT, UPDATE privileges to database2 to that user.
CALL procedure_user('%', 'user1', 'database2', 'SELECT, INSERT, UPDATE');

Answer 2

确保用户存在而不授予任何权限：

GRANT USAGE ON *.* TO my_user@"%" IDENTIFIED BY 'my_password';

如果您确实想一步完成授予和撤销，您可能必须直接修改内部权限存储表：

INSERT INTO `mysql`.`db` (
    `Host`, `Db`, `User`,
    `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`,
    `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`,
    `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`,
    `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`)
VALUES (
    'my_user', '%', 'my_database',
    'Y', 'N', 'Y', 'N',
    'N', 'N', 'N', 'N', 'N', 'N',
    'N', 'N', 'N', 'N',
    'N', 'N', 'N')
ON DUPLICATE KEY UPDATE
    `Select_priv` = 'Y', `Insert_priv` = 'N', `Update_priv` = 'Y', `Delete_priv` = 'N',
    `Create_priv` = 'N', `Drop_priv` = 'N', `Grant_priv` = 'N', `References_priv` = 'N', `Index_priv` = 'N', `Alter_priv` = 'N',
    `Create_tmp_table_priv` = 'N', `Lock_tables_priv` = 'N', `Create_view_priv` = 'N', `Show_view_priv` = 'N',
    `Create_routine_priv` = 'N', `Alter_routine_priv` = 'N', `Execute_priv` = 'N';

但是，这样的可移植性较差，需要更多权限，并且不必要时不要创建用户帐户，因此您最好使用三语句方法。

为了帮助解决可读性问题，您可以创建某种包含帐户和权限的 CSV，并从中生成 SQL 脚本。

Answer 3

抱歉，答案很长，实际上是一条评论，但我不明白。你的“第三行”GRANT 命令对我来说效果很好。这是应该有效的两种情况。如果您可以发布一些重现该错误的测试命令，那就太好了。至少我可以从中学习:)

情况 #1，用户不存在：

mysql> SHOW GRANTS FOR my_user@"%";
ERROR 1141 (42000): There is no such grant defined for user 'my_user' on host '%'

好的，用户不存在。

mysql> create database my_database;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT SELECT ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR my_user@"%";
+-----------------------------------------------------------------------+
| Grants for my_user@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY PASSWORD '*CC...18' | 
| GRANT SELECT ON `my_database`.* TO 'my_user'@'%'                      | 
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

好的，他有 SELECT 权限。

情况 #2，用户存在并且对 other_database 和 my_database 也有权限：

mysql> SHOW GRANTS FOR my_user@"%";
ERROR 1141 (42000): There is no such grant defined for user 'my_user' on host '%'

好的，用户不存在。

mysql> create database my_database;
Query OK, 1 row affected (0.00 sec)

mysql> create database other_database;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT SELECT ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON other_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR my_user@"%";
+-----------------------------------------------------------------------+
| Grants for my_user@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY PASSWORD '*CC...18' | 
| GRANT SELECT ON `other_database`.* TO 'my_user'@'%'                   | 
| GRANT SELECT ON `my_database`.* TO 'my_user'@'%'                      | 
+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

上面是测试装置，现在我们向用户授予新的 UPDATE 权限：

mysql> GRANT UPDATE ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR my_user@"%";
+-----------------------------------------------------------------------+
| Grants for my_user@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY PASSWORD '*CC...18' | 
| GRANT SELECT ON `other_database`.* TO 'my_user'@'%'                   | 
| GRANT SELECT, UPDATE ON `my_database`.* TO 'my_user'@'%'              | 
+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

他的权限在 other_database 上没有更改，并且他获得了新的 UPDATE 对 my_database 和之前的 SELECT 的权限。

---

根据评论，它应该只是 UPDATE 而没有 SELECT。

不幸的是，对于当前的 MySQL 版本，仅使用一个命令无法做到这一点。 GRANT 没有 REMOVE EXISTING 子句。

我认为最好的解决方案是 @eswald 的 GRANT USAGE ON ... 但它仍然是 3 个命令。另一个解决方案是 a

DELETE FROM mysql.db WHERE user = 'my_user' AND host ='%' AND db = 'my_database'

，但它需要一个 FLUSH PRIVILEGES，所以它也是 3 个命令。

解决方法可能是一个 bash 脚本，它生成问题中的三个命令：

#!/bin/bash

function grant {
    USER=$1
    PASSWORD=$2
    DB=$3
    PERMISSIONS=$4

    echo "GRANT USAGE ON $DB TO $USER IDENTIFIED BY '$PASSWORD';"
    echo "REVOKE ALL PRIVILEGES ON $DB FROM $USER;"
    echo "GRANT $PERMISSIONS ON $DB TO $USER IDENTIFIED BY '$PASSWORD';"
}

grant "my_user@'%'" "my_password" "my_database.*" "SELECT, UPDATE"

它打印：（

GRANT USAGE ON my_database.* TO my_user@'%' IDENTIFIED BY 'my_password';
REVOKE ALL PRIVILEGES ON my_database.* FROM my_user@'%';
GRANT SELECT, UPDATE ON my_database.* TO my_user@'%' IDENTIFIED BY 'my_password';

我已将第一个 GRANT SELECT 更改为 USAGE。）

Answer 4

我只是想知道，你运行过FLUSH PRIVILEGES吗？