|
Privilege propogation depends on the order in which the privileges were granted.
Here, I first grant the roles, and then grant privileges to the roles:
MariaDB [test]> create role r1, r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant r1 to r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant r2 to foo@localhost;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant all on db1.* to r1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant all on db2.* to r2;
|
Query OK, 0 rows affected (0.00 sec)
|
As a result, the user with active role r2 has access to both db1 and db2:
MariaDB [test]> select current_user;
|
+---------------+
|
| current_user |
|
+---------------+
|
| foo@localhost |
|
+---------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show tables in db1;
|
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db1'
|
|
MariaDB [test]> show tables in db2;
|
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db2'
|
|
MariaDB [test]> set role r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> show tables in db1;
|
Empty set (0.00 sec)
|
|
MariaDB [test]> show tables in db2;
|
Empty set (0.00 sec)
|
|
MariaDB [test]> show grants;
|
+-----------------------------------------+
|
| Grants for foo@localhost |
|
+-----------------------------------------+
|
| GRANT r2 TO 'foo'@'localhost' |
|
| GRANT USAGE ON *.* TO 'foo'@'localhost' |
|
| GRANT r1 TO 'r2' |
|
| GRANT USAGE ON *.* TO 'r2' |
|
| GRANT ALL PRIVILEGES ON `db2`.* TO 'r2' |
|
| GRANT USAGE ON *.* TO 'r1' |
|
| GRANT ALL PRIVILEGES ON `db1`.* TO 'r1' |
|
+-----------------------------------------+
|
7 rows in set (0.00 sec)
|
|
Now, I first grant privileges to the roles, and then grant roles:
MariaDB [test]> create role r1, r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant all on db1.* to r1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant all on db2.* to r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant r1 to r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> grant r2 to foo@localhost;
|
Query OK, 0 rows affected (0.00 sec)
|
As a result, the user with active role r2 only has access to db2, but not to db1:
MariaDB [test]> select current_user;
|
+---------------+
|
| current_user |
|
+---------------+
|
| foo@localhost |
|
+---------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show tables in db1;
|
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db1'
|
MariaDB [test]> show tables in db2;
|
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db2'
|
MariaDB [test]> set role r2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> show tables in db1;
|
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db1'
|
|
MariaDB [test]> show tables in db2;
|
Empty set (0.00 sec)
|
|
MariaDB [test]> show grants;
|
+-----------------------------------------+
|
| Grants for foo@localhost |
|
+-----------------------------------------+
|
| GRANT r2 TO 'foo'@'localhost' |
|
| GRANT USAGE ON *.* TO 'foo'@'localhost' |
|
| GRANT r1 TO 'r2' |
|
| GRANT USAGE ON *.* TO 'r2' |
|
| GRANT ALL PRIVILEGES ON `db2`.* TO 'r2' |
|
| GRANT USAGE ON *.* TO 'r1' |
|
| GRANT ALL PRIVILEGES ON `db1`.* TO 'r1' |
|
+-----------------------------------------+
|
7 rows in set (0.01 sec)
|
|