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

drop user leaves privileges

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.32
    • 5.5.33
    • None
    • None

    Description

      It looks like in certain cases DROP USER does not properly revokes all privileges before dropping the user. Test:

      $ ./mtr --noreorder main.view_grant funcs_1.is_table_privileges

      it will fail, because two privileges of the user dan@localhost (that was created and dropped in main.view_grant) were not revoked. Or, perhaps, information_schema.table_privileges is wrong and shows privileges that aren't there.

      Attachments

        Activity

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

          MySQL BUG#13864642
          raghav.kapoor@oracle.com-20120925102846-l4cadeex5qmd5xdr

          serg Sergei Golubchik added a comment - MySQL BUG#13864642 raghav.kapoor@oracle.com-20120925102846-l4cadeex5qmd5xdr
          elenst Elena Stepanova added a comment -

          MTR test case for RENAME:

          create database db;
          create table db.t1 (i int);
          create table db.t2 (j int);
          create table db.t3 (k int, secret int);
          create view db.v1 as select * from db.t1;
          create view db.v3 as select k from db.t3;
          GRANT DROP ON db.t1 TO inga@localhost;
          GRANT INDEX ON db.t1 TO bob@localhost;
          DROP USER 'bob'@'localhost';
          GRANT DROP,SELECT ON db.v1 TO inga@localhost;
          GRANT INSERT(k) ON db.v3 TO inga@localhost;
          GRANT INSERT(j) ON db.t2 TO inga@localhost;
          GRANT SELECT(j) ON db.t2 TO karl@localhost;
          RENAME USER 'inga'@'localhost' TO han@localhost;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'inga'@'localhost'";

          elenst Elena Stepanova added a comment - MTR test case for RENAME: create database db; create table db.t1 (i int); create table db.t2 (j int); create table db.t3 (k int, secret int); create view db.v1 as select * from db.t1; create view db.v3 as select k from db.t3; GRANT DROP ON db.t1 TO inga@localhost; GRANT INDEX ON db.t1 TO bob@localhost; DROP USER 'bob'@'localhost'; GRANT DROP,SELECT ON db.v1 TO inga@localhost; GRANT INSERT(k) ON db.v3 TO inga@localhost; GRANT INSERT(j) ON db.t2 TO inga@localhost; GRANT SELECT(j) ON db.t2 TO karl@localhost; RENAME USER 'inga'@'localhost' TO han@localhost; SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'inga'@'localhost'";
          serg Sergei Golubchik added a comment -

          this test case didn't work for me. That is, it passed on vanilla 5.5.

          serg Sergei Golubchik added a comment - this test case didn't work for me. That is, it passed on vanilla 5.5.
          elenst Elena Stepanova added a comment - - edited

          Here is what it produces for me (on 5.5 revno 3862)

          worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
          create database db;
          create table db.t1 (i int);
          create table db.t2 (j int);
          create table db.t3 (k int, secret int);
          create view db.v1 as select * from db.t1;
          create view db.v3 as select k from db.t3;
          GRANT DROP ON db.t1 TO inga@localhost;
          GRANT INDEX ON db.t1 TO bob@localhost;
          DROP USER 'bob'@'localhost';
          GRANT DROP,SELECT ON db.v1 TO inga@localhost;
          GRANT INSERT(k) ON db.v3 TO inga@localhost;
          GRANT INSERT(j) ON db.t2 TO inga@localhost;
          GRANT SELECT(j) ON db.t2 TO karl@localhost;
          RENAME USER 'inga'@'localhost' TO han@localhost;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'inga'@'localhost'";
          GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
          'inga'@'localhost' def db v1 SELECT NO
          'inga'@'localhost' def db v1 DROP NO

          Also tried on the release binaries (5.5.32), got the same as above.

          So, if it's different for you, it means it's not deterministic between the machines/systems (rather than builds)...

          elenst Elena Stepanova added a comment - - edited Here is what it produces for me (on 5.5 revno 3862) worker [1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 create database db; create table db.t1 (i int); create table db.t2 (j int); create table db.t3 (k int, secret int); create view db.v1 as select * from db.t1; create view db.v3 as select k from db.t3; GRANT DROP ON db.t1 TO inga@localhost; GRANT INDEX ON db.t1 TO bob@localhost; DROP USER 'bob'@'localhost'; GRANT DROP,SELECT ON db.v1 TO inga@localhost; GRANT INSERT(k) ON db.v3 TO inga@localhost; GRANT INSERT(j) ON db.t2 TO inga@localhost; GRANT SELECT(j) ON db.t2 TO karl@localhost; RENAME USER 'inga'@'localhost' TO han@localhost; SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'inga'@'localhost'"; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'inga'@'localhost' def db v1 SELECT NO 'inga'@'localhost' def db v1 DROP NO Also tried on the release binaries (5.5.32), got the same as above. So, if it's different for you, it means it's not deterministic between the machines/systems (rather than builds)...
          elenst Elena Stepanova added a comment - - edited

          Here is another one, just in case you want to check why it fails in some environments but not others (I can set it up on perro, it fails there too):

          CREATE TABLE IF NOT EXISTS `t1` (`col1` INT, `col2` INT);
          CREATE OR REPLACE VIEW `v1` AS SELECT * FROM `t1`;
          GRANT INSERT (col1) ON `test`.`t1` TO user9@localhost;
          GRANT UPDATE (col2) , SELECT , SELECT , UPDATE , INSERT , UPDATE , UPDATE , UPDATE , UPDATE ON `v1` TO user2@localhost;
          GRANT TRIGGER ON `test`.`v1` TO user9@localhost;
          GRANT DELETE , TRIGGER ON `test`.`v1` TO user10@localhost;
          GRANT GRANT OPTION , INSERT , DROP ON `test`.`t1` TO user2@localhost;
          GRANT UPDATE (col1) , SELECT ON `test`.`t1` TO user8@localhost;
          GRANT DELETE , UPDATE ON `t1` TO user1@localhost;
          GRANT TRIGGER ON `test`.`v1` TO user1@localhost;
          GRANT INSERT (col1, col2) ON `test`.`v1` TO user6@localhost;
          GRANT SELECT (col1, col2) ON `test`.`t1` TO user5@localhost;
          GRANT ALTER , UPDATE ON `t1` TO user6@localhost;
          GRANT UPDATE (col1) , INSERT ON `test`.`v1` TO user8@localhost;
          GRANT DELETE , INSERT ON `t1` TO user3@localhost;
          RENAME USER 'user1'@'localhost' TO 'new_user1'@'localhost';
          RENAME USER 'new_user1'@'localhost' TO 'user1'@'localhost';
          RENAME USER 'user3'@'localhost' TO 'new_user3'@'localhost';
          RENAME USER 'new_user3'@'localhost' TO 'user3'@'localhost';
          RENAME USER 'user1'@'localhost' TO 'new_user1'@'localhost';
          SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'user1'@'localhost'";

          It returns

          SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'user1'@'localhost'";
          GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
          'user1'@'localhost' def test t1 UPDATE NO
          'user1'@'localhost' def test t1 DELETE NO

          But it probably makes no sense to add it to the MTR suite if it's not deterministic.

          elenst Elena Stepanova added a comment - - edited Here is another one, just in case you want to check why it fails in some environments but not others (I can set it up on perro, it fails there too): CREATE TABLE IF NOT EXISTS `t1` (`col1` INT, `col2` INT); CREATE OR REPLACE VIEW `v1` AS SELECT * FROM `t1`; GRANT INSERT (col1) ON `test`.`t1` TO user9@localhost; GRANT UPDATE (col2) , SELECT , SELECT , UPDATE , INSERT , UPDATE , UPDATE , UPDATE , UPDATE ON `v1` TO user2@localhost; GRANT TRIGGER ON `test`.`v1` TO user9@localhost; GRANT DELETE , TRIGGER ON `test`.`v1` TO user10@localhost; GRANT GRANT OPTION , INSERT , DROP ON `test`.`t1` TO user2@localhost; GRANT UPDATE (col1) , SELECT ON `test`.`t1` TO user8@localhost; GRANT DELETE , UPDATE ON `t1` TO user1@localhost; GRANT TRIGGER ON `test`.`v1` TO user1@localhost; GRANT INSERT (col1, col2) ON `test`.`v1` TO user6@localhost; GRANT SELECT (col1, col2) ON `test`.`t1` TO user5@localhost; GRANT ALTER , UPDATE ON `t1` TO user6@localhost; GRANT UPDATE (col1) , INSERT ON `test`.`v1` TO user8@localhost; GRANT DELETE , INSERT ON `t1` TO user3@localhost; RENAME USER 'user1'@'localhost' TO 'new_user1'@'localhost'; RENAME USER 'new_user1'@'localhost' TO 'user1'@'localhost'; RENAME USER 'user3'@'localhost' TO 'new_user3'@'localhost'; RENAME USER 'new_user3'@'localhost' TO 'user3'@'localhost'; RENAME USER 'user1'@'localhost' TO 'new_user1'@'localhost'; SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'user1'@'localhost'"; It returns SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = "'user1'@'localhost'"; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' def test t1 UPDATE NO 'user1'@'localhost' def test t1 DELETE NO But it probably makes no sense to add it to the MTR suite if it's not deterministic.
          serg Sergei Golubchik added a comment -

          weird. now the first one (with inga) worked. the second — did not.

          serg Sergei Golubchik added a comment - weird. now the first one (with inga) worked. the second — did not.

          People

            serg Sergei Golubchik
            serg Sergei Golubchik
            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.