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

REVOKE ALL is not allowed for user with GRANT OPTION

    XMLWordPrintable

    Details

      Description

      If a user with restricted privileges but including GRANT OPTION tries to revoke privileges from the user, which he has just created, the REVOKE ALL query fails with ERROR 1044 (42000) at line 1: Access denied for user 'admin'@'%' to database 'db'.
      In my case Amazon RDS is used, where the master user has restricted access.
      I created the script for reproducing the issue with docker:

      #!/bin/bash -e
       
      VERSION=${1:-"10.5.8"}
      docker run -d --name mariadb -e MYSQL_ROOT_PASSWORD=secret -it mariadb:$VERSION
      trap "docker rm -f mariadb" EXIT
       
      while ! (
          docker exec mariadb mysqladmin ping --silent && \
          docker exec mariadb mysqladmin -uroot -psecret status --silent
      ); do
          sleep 1
      done
       
      echo "Create 'admin' user"
      docker exec -i mariadb mysql -uroot -psecret <<SQL
      CREATE USER 'admin'@'%' IDENTIFIED BY 'secret';
      GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin'@'%' WITH GRANT OPTION;
      SQL
       
      echo "Check 'admin' can create 'user' and 'db'"
      docker exec -i mariadb mysql -uadmin -psecret <<SQL
      CREATE USER 'user'@'%';
      CREATE DATABASE db;
      GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, ALTER, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER ON db.* TO 'user'@'%';
      SQL
       
      echo "Check 'admin' can detach 'user' from 'db'"
      docker exec -i mariadb mysql -uadmin -psecret <<SQL
      REVOKE ALL ON db.* FROM 'user'@'%';
      SQL
      

      If I change to "GRANT ALL ON . TO 'admin'@'%' WITH GRANT OPTION;", it works, but it is impossible for Amazon RDS.
      If I change to "REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, ALTER, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER ON db.* FROM 'user'@'%';" it works, but it is inconvenient for my production code because I expect to revoke all previously granted privileges.
      I checked the script with different versions and the issue reproduces starting from 10.3.4
      The issue does not reproduce with MySQL image.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            ekazakov Eugene Kazakov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: