[MDEV-5771] Privileges acquired via roles depend on the order of granting - Jira

XML

Word

Printable

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)

relates to

MDEV-4397 Roles

MDEV-5164 Testing Roles

MDEV-5669 Possible inconsistencies or lack of documentation in role privilege grants and propogation

MDEV-29851 Cached role privileges are not invalidated when needed

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.