MySQL授予通配符数据库名称的权限给新用户。

Question

我尽可能地希望将MySQL用户帐户的权限限制为最小，仅授予我所需的最基本权限。

我有一个名为creator的用户，我想用它来创建新用户、创建新数据库，并将这些用户的权限分配给那些数据库，但仅限于特定格式的数据库。

要创建creator用户，我需要以下命令（以root身份运行）:

GRANT CREATE USER, CREATE ON *.* TO 'creator'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON `my_prefix\_%`.* TO 'creator'@'localhost' WITH GRANT OPTION;

我原以为这样做可以让我创建一个新用户，并将其分配到与 my_prefix_% 匹配的表上的任何权限，但事实并非如此。以 creator 用户身份运行以下查询会导致错误。

CREATE DATABASE IF NOT EXISTS `my_prefix_test`;
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';

这些工作都能完成，我可以成功创建用户和数据库。但是，当我试图在新数据库上为新用户分配权限时，我遇到了以下问题：

GRANT ALL PRIVILEGES ON `my_prefix_test`.* TO 'test'@'localhost';

> ERROR 1044 (42000): Access denied for user 'creator'@'localhost' to database 'my_prefix_test'

为了安全起见，我还刷新了权限。

有人对这个问题有什么见解吗？使用存储过程是否更容易实现？谢谢！

- Ahorner

---

有趣的是，这对我似乎有效，尽管它不应该。因为正如你所说，下划线作为字面意义上的字符表示一个单字符通配符。我想知道这是否可能是MySQL的一个错误？ - Ahorner

不知道这是否是一个错误。下划线似乎像通配符一样工作，因为我可以创建数据库my_prefixxtest并相应地授予权限。但是，通过反斜杠转义两个下划线无法使其正常工作。 - Michael Berkowski

我成功创建了一个名为 myotherdb 的数据库，并试图授予 test 用户所有权限，但未成功（这也是我想要的结果）。我认为这应该足够了，谢谢！如果您发布回答的话，我会很高兴接受它。 - Ahorner

Answer 1

根据MySQL GRANT文档，通配符_和%需要进行反斜杠转义才能在数据库名称中用作文字。在您的尝试中，您已经将一个_转义为\_，但没有转义另一个。如果您转义了两个下划线，就可以获得正确的授权。但是，关键是，当creator用户向test用户发出GRANT语句时，您必须同时转义它们。请以root（或完全特权用户）身份登录：

-- As root:
GRANT CREATE USER, CREATE ON *.* TO 'creator'@'localhost' IDENTIFIED BY 'password';
-- Escape both underscores here
GRANT ALL PRIVILEGES ON `my\_prefix\_%`.* TO 'creator'@'localhost' WITH GRANT OPTION;

以 creator 身份登录：

CREATE DATABASE IF NOT EXISTS `my_prefix_test`;
Query OK, 1 row affected (0.00 sec)
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

-- Issue the GRANT but escape the underscores.
-- because creator@localhost does not have permission to grant on 
-- a pattern like myXprefixXtest, as the unescaped _ wildcards would allow
GRANT ALL PRIVILEGES ON `my\_prefix\_test`.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

使用用户名test登录，授权工作正常：

USE my_prefix_test;
CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.00 sec)

- Michael Berkowski

---

Michael，你太棒了。我想我真正需要的是在第二个授权（作为创建者）中转义下划线。非常感谢你的时间！ - Ahorner

这个技巧允许用户创建没有以其它前缀（如other_prefix_）开头的数据库。 - abkrim