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

Privileges acquired via roles depend on the order of granting

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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)

        Attachments

          Issue Links

            Activity

              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: