Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5771

Privileges acquired via roles depend on the order of granting

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.8
        • Fix Version/s: 10.0.10
        • Component/s: None
        • Labels:
          None

          Description

          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)

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5669 Possible inconsistencies or lack of documentation in role privilege grants and propogation

                • Major - Major loss of function.
                • Closed

                Task - A task that needs to be done. MDEV-5164 Testing Roles

                • Major - Major loss of function.
                • Stalled

                Task - A task that needs to be done. MDEV-4397 Roles

                • Major - Major loss of function.
                • Closed

                  Activity

                  There are no comments yet on this issue.

                    People

                    • Assignee:
                      serg Sergei Golubchik
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Time Tracking

                        Estimated:
                        Original Estimate - Not Specified
                        Not Specified
                        Remaining:
                        Remaining Estimate - 0 minutes
                        0m
                        Logged:
                        Time Spent - 1 hour
                        1h