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

GRANT ALL returns Access denied for user with GRANT OPTION

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.13
    • N/A

    Description

      We have some shell script to create new databases and users for new projects. This script has dedicated user account "sys_dbcreator" with slightly lower privileges than root user. It worked fine for all versions from MySQL 4.1 but stopped with MariaDB 10.3.

      We create new database in this shell script and then call GRANT ALL ON `newdb`. TO 'newuser'@'localhost';*

      Expected result:
      User 'test000' will have ALL privileges which user 'sys_dbcreator' has on database `test000`.

      But we got an error:
      Access denied

      sys_dbcreator @ localhost [test000]:
      mysql> GRANT ALL ON `test000`.* TO 'test000'@'localhost';
      ERROR 1044 (42000): Access denied for user 'sys_dbcreator'@'localhost' to database 'test000'
      

      Privileges which are granted to user sys_dbcreator:

      sys_dbcreator @ localhost [test000]:
      mysql> SHOW GRANTS FOR 'sys_dbcreator'@'localhost'\G
      *************************** 1. row ***************************
      Grants for sys_dbcreator@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'sys_dbcreator'@'localhost' IDENTIFIED BY PASSWORD '*87*****************************DE' WITH GRANT OPTION
      1 row in set (0.000 sec)
      

      If we use these listed privileges for user 'test000' on database `test000` then it works.

      sys_dbcreator @ localhost [test000]:
      mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test000`.* TO 'test000'@'localhost';
      Query OK, 0 rows affected (0.000 sec)
      

      GRANT ALL ON `newdb`. TO 'newuser'@'localhost';* worked on all previous versions of MySQL from 4.1 to 5.5, then we migrated to MariaDB and it also worked for all tested versions to 10.2. MariaDB 10.3 does not work so I think this is a regression on how GRANT ALL works.

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            Lachman Miroslav Lachman
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.