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

Cached role privileges are not invalidated when needed

Details

    Description

      There appears to be an inconsistency of when privileges of a role are applied indirectly via another role and when the user connects to the server.

      For instance - role1->user, role2->role1; user connects and has role2 privileges:

      create role admin;
      		 
      create role student;
      		 
      create database crm;
      		 
      grant create on crm.* to admin;
      		 
      grant select on crm.* to student;
      		 
      create user intern@localhost;
      		 
      grant student to intern@localhost;
      		 
      set default role student for intern@localhost;
      		 
      grant admin to student;
      		 
       
      		 
      connect (con1, localhost, intern,,);
      		 
      use crm;
      		 
      create table t1 (a int);
      		 
      disconnect con1;
      		 
       
      		 
      # cleanup
      		 
      connection default;
      		 
      drop user intern@localhost;
      		 
      drop role student;
      		 
      drop role admin;
      		 
      drop database crm;
      		 
      flush privileges;

      However - role1->user; user connects; role2->role1; user connects but does not have role2 privileges. FLUSH PRIVILEGES is needed before user has role2 capabilities:

      create role admin;
      		 
      create role student;
      		 
      create database crm;
      		 
      grant create on crm.* to admin;
      		 
      grant select on crm.* to student;
      		 
      create user intern@localhost;
      		 
      grant student to intern@localhost;
      		 
      set default role student for intern@localhost;
      		 
       
      		 
      connect (con1, localhost, intern,,);
      		 
      use crm;
      		 
      disconnect con1;
      		 
       
      		 
      connection default;
      		 
      grant admin to student;
      		 
       
      		 
      connect (con1, localhost, intern,,);
      		 
      use crm;
      		 
      --error ER_TABLEACCESS_DENIED_ERROR
      		 
      create table t1 (a int);
      		 
      disconnect con1;
      		 
       
      		 
      connection default;
      		 
      flush privileges;
      		 
       
      		 
      connect (con1, localhost, intern,,);
      		 
      use crm;
      		 
      create table t1 (a int);
      		 
      disconnect con1;
      		 
       
      		 
      # cleanup
      		 
      connection default;
      		 
      drop user intern@localhost;
      		 
      drop role student;
      		 
      drop role admin;
      		 
      drop database crm;
      		 
      flush privileges;

      Why this is the case is not clear to me.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5771 Privileges acquired via roles depend on the order of granting

          • Major - Major loss of function.
          • Closed

          Activity

            serg Sergei Golubchik added a comment - - edited

            The reason is intermediate

            connect (con1, localhost, intern,,);
            		 
            use crm;
            		 
            disconnect con1;

            this computes privileges that user intern has on the schema crm, the result is cached. And, apparently, the cache is not invalidated when you grant admin to student.

            serg Sergei Golubchik added a comment - - edited The reason is intermediate connect (con1, localhost, intern,,); use crm; disconnect con1; this computes privileges that user intern has on the schema crm , the result is cached. And, apparently, the cache is not invalidated when you grant admin to student .

            People

              serg Sergei Golubchik
              angelique.sklavounos Angelique Sklavounos (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.