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

Revoking a role does not revoke corresponding grants from open sessions

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • None
    • 10.0.5
    • None
    • None

    Description

      If a session has set a role X, revoking this role X from the user does not revoke the grants it provided.
      I am not 100% sure whether it's design or oversight, but I suspect the latter because a) revoking the grants from the role does revoke them from an open session; b) dropping the role also revokes the grants from an open session. However, it's obviously not practical to do modify a role this way if you only want to deny it for a particular user, and do it immediately.

      Test case:

      --enable_connect_log
      		 
       
      		 
      create database db1;
      		 
       
      		 
      create user user1@localhost;
      		 
      create role role1;
      		 
      grant role1 to user1@localhost;
      		 
      grant all on db1.* to role1;
      		 
       
      		 
      --connect (con1,localhost,user1,,)
      		 
      set role role1;
      		 
      show grants;
      		 
      create table db1.t1 (i int);
      		 
       
      		 
      --connection default
      		 
      revoke role1 from user1@localhost;
      		 
       
      		 
      --connection con1
      		 
      show grants;
      		 
      drop table db1.t1;

      Output:

      create database db1;
      		 
      create user user1@localhost;
      		 
      create role role1;
      		 
      grant role1 to user1@localhost;
      		 
      grant all on db1.* to role1;
      		 
      connect  con1,localhost,user1,,;
      		 
      set role role1;
      		 
      show grants;
      		 
      Grants for user1@localhost
      		 
      GRANT role1 TO 'user1'@'localhost'
      		 
      GRANT USAGE ON *.* TO 'user1'@'localhost'
      		 
      GRANT USAGE ON *.* TO 'role1'
      		 
      GRANT ALL PRIVILEGES ON `db1`.* TO 'role1'
      		 
      create table db1.t1 (i int);
      		 
      connection default;
      		 
      revoke role1 from user1@localhost;
      		 
      connection con1;
      		 
      show grants;
      		 
      Grants for user1@localhost
      		 
      GRANT USAGE ON *.* TO 'user1'@'localhost'
      		 
      GRANT USAGE ON *.* TO 'role1'
      		 
      GRANT ALL PRIVILEGES ON `db1`.* TO 'role1'
      		 
      drop table db1.t1;
      		 

      revision-id: sergii@pisem.net-20131020232702-db6ekuk4lz0itg1b
      		 
      revno: 3892
      		 
      branch-nick: 10.0-serg
      		 
      BUILD/compile-pentium-debug-max-no-ndb

      Attachments

        Issue Links

          relates to

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

          • Major - Major loss of function.
          • Closed

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

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            by design. also, revoking or dropping a role which is enabled (directly or indirectly) does not affect user's global privileges or privileges on the current database, but does affect privileges on the non-current database, table, column, and routine privileges. this is identical to how revoking privileges directly from a user work ­— which also doesn't affect privileges, cached in the THD.

            serg Sergei Golubchik added a comment - by design. also, revoking or dropping a role which is enabled (directly or indirectly) does not affect user's global privileges or privileges on the current database, but does affect privileges on the non-current database, table, column, and routine privileges. this is identical to how revoking privileges directly from a user work ­— which also doesn't affect privileges, cached in the THD.
            elenst Elena Stepanova added a comment -

            >> revoking or dropping a role which is enabled (directly or indirectly) does not affect user's global privileges or privileges on the current database, but does affect privileges on the non-current database, table, column, and routine privileges

            But the example above was about a non-current database, was it not?

            >> this is identical to how revoking privileges directly from a user work

            Strange, I don't see the symmetry here. Please consider the following example. user1 is given two symmetrical grants, one for db1 through role1, another one for db2 directly. Then the grants are revoked at the same time; but access to db1 remains, while access to db2 doesn't.

            --enable_connect_log
            create database db1;
            create database db2;

            create user user1@localhost;
            create role role1;
            grant role1 to user1@localhost;
            grant all on db1.* to role1;
            grant all on db2.* to user1@localhost;

            --connect (con1,localhost,user1,,)
            set role role1;
            show grants;
            create table db1.t1 (i int);
            create table db2.t2 (i int);

            --connection default
            revoke role1 from user1@localhost;
            revoke all on db2.* from user1@localhost;

            --connection con1
            show grants;
            drop table db1.t1;
            drop table db2.t2;

            The end of output:

            Grants for user1@localhost
            GRANT USAGE ON . TO 'user1'@'localhost'
            GRANT USAGE ON . TO 'role1'
            GRANT ALL PRIVILEGES ON `db1`.* TO 'role1'
            drop table db1.t1;
            drop table db2.t2;
            mysqltest: At line 24: query 'drop table db2.t2' failed: 1142: DROP command denied to user 'user1'@'localhost' for table 't2'

            elenst Elena Stepanova added a comment - >> revoking or dropping a role which is enabled (directly or indirectly) does not affect user's global privileges or privileges on the current database, but does affect privileges on the non-current database, table, column, and routine privileges But the example above was about a non-current database, was it not? >> this is identical to how revoking privileges directly from a user work Strange, I don't see the symmetry here. Please consider the following example. user1 is given two symmetrical grants, one for db1 through role1, another one for db2 directly. Then the grants are revoked at the same time; but access to db1 remains, while access to db2 doesn't. --enable_connect_log create database db1; create database db2; create user user1@localhost; create role role1; grant role1 to user1@localhost; grant all on db1.* to role1; grant all on db2.* to user1@localhost; --connect (con1,localhost,user1,,) set role role1; show grants; create table db1.t1 (i int); create table db2.t2 (i int); --connection default revoke role1 from user1@localhost; revoke all on db2.* from user1@localhost; --connection con1 show grants; drop table db1.t1; drop table db2.t2; The end of output: Grants for user1@localhost GRANT USAGE ON . TO 'user1'@'localhost' GRANT USAGE ON . TO 'role1' GRANT ALL PRIVILEGES ON `db1`.* TO 'role1' drop table db1.t1; drop table db2.t2; mysqltest: At line 24: query 'drop table db2.t2' failed: 1142: DROP command denied to user 'user1'@'localhost' for table 't2'
            serg Sergei Golubchik added a comment -

            "access to db1 remains" was a bug that I've fixed recently, but hasn't pushed yet.

            Access to the current database will remain, access to the non-current database is affected by grant/revoke — independently whether it was a grant to a role or to a user.

            serg Sergei Golubchik added a comment - "access to db1 remains" was a bug that I've fixed recently, but hasn't pushed yet. Access to the current database will remain, access to the non-current database is affected by grant/revoke — independently whether it was a grant to a role or to a user.

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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