mysql - 如何在 mariaDB 中创建为新用户授予数据库访问权限的角色？

Question

创建角色，然后将新用户分配给角色以授予对所需数据库的访问权限的正确方法是什么？

它没有按照我预期的方式工作。

如果我尝试创建读/写角色和只读角色，然后向这些角色授予权限，然后创建具有默认角色的用户，我会收到该用户的数据库访问被拒绝错误:

CREATE DATABASE testDb;

CREATE ROLE readOnly;
CREATE ROLE readWrite;

GRANT SELECT ON testDB . * TO readOnly;
GRANT ALL ON testDB . * TO readWrite;

CREATE USER 'testUser'@'%' IDENTIFIED BY 'testPass';

GRANT readOnly TO testUser;
GRANT readWrite TO testUser;

SET DEFAULT ROLE readOnly FOR testUser;
\q

然后，当我尝试以 testUser 身份连接到数据库时:

/mysql -u testUser -p -D testDb
ERROR 1044 (42000): Access denied for user 'testUser'@'%' to database 'testDb'

另一方面，如果我不使用角色并将权限直接授予用户(没有角色的用户)，我不会收到数据库访问被拒绝的错误:

DROP USER testUser;
DROP ROLE readWrite;
DROP ROLE readOnly;

GRANT ALL ON testDb . * TO testUser@'%' IDENTIFIED BY 'testPass';
\q

现在，可以以 testUser 身份进行连接:

mysql -u testUser -p -D testDb
Enter password:
MariaDB [testDb]> 

Answer 1

我无法重现该问题(请小心在数据库对象名称中使用大写和小写):

$ mysql -u root -p
Enter password:

MariaDB [(none)]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 10.3.2-MariaDB |
+----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> CREATE DATABASE `testDb`;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> CREATE ROLE `readOnly`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> CREATE ROLE `readWrite`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT SELECT ON `testDb`.* TO `readOnly`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT ALL ON `testDb`.* TO `readWrite`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> CREATE USER 'testUser'@'%' IDENTIFIED BY '*********';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT `readOnly` TO `testUser`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT `readWrite` TO `testUser`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET DEFAULT ROLE `readOnly` FOR `testUser`;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> \q
Bye

$ mysql -u testUser -p
Enter password:

MariaDB [(none)]> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| testUser@%     |
+----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testDb             |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| readOnly     |
+--------------+
1 row in set (0.000 sec)