案例#2,用户存在并且在other_database和my_database上也是正确的: h3>
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上没有改变,他获得了my_database和前一个SELECT的新UPDATE许可。
根据评论,它应该只有UPDATE而没有SELECT。
不幸的是,对于当前的MySQL版本,只用一个命令就不可能做到这一点。 GRANT没有REMOVE EXISTING子句。
我认为最好的解决方案是@eswald的GRANT USAGE ON ...,但它仍然有3个命令。另一个解决方案是
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。)