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

Inconsistent grant-name usage between grant-statement and privilege tables

Details

    Description

      In MariaDB 10.3.9 the Privilege_Type returned from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES is different for the new privilege type DELETE VERSIONING ROWS from the allowed grants in grant/revoke statements.

      Example:

      MariaDB [(none)]> GRANT DELETE VERSIONING ROWS ON tests.* TO alice;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON tests.* TO alice' at line 1
      MariaDB [(none)]> GRANT DELETE HISTORY ON tests.* TO alice;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> SELECT * FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE '\'alice%';
      +-------------+---------------+--------------+------------------------+--------------+
      | GRANTEE     | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE         | IS_GRANTABLE |
      +-------------+---------------+--------------+------------------------+--------------+
      | 'alice'@'%' | def           | tests        | DELETE VERSIONING ROWS | NO           |
      +-------------+---------------+--------------+------------------------+--------------+
      1 row in set (0.010 sec)
      MariaDB [(none)]> SHOW GRANTS FOR alice;
      +----------------------------------------------------------+
      | Grants for alice@%                                       |
      +----------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'alice'@'%'                        |
      | GRANT DELETE VERSIONING ROWS ON `tests`.* TO 'alice'@'%' |
      +----------------------------------------------------------+
      2 rows in set (0.001 sec)
      

      We have some in-house scripting which compares the granted rights in the database with a config, and produces grant/revoke-statements when things are different. At this moment it cannot use the produced grants to automatically generate revoke-statements for DELETE VERSIONING ROWS. Neither can it confirm that DELETE HISTORY has been granted.

      Proposal for fix:

      • Add DELETE VERSIONING ROWS as valid grant to the grant/revoke statements

      Attachments

        Issue Links

          Activity

            I don't know whether we can add a privilege type (or a synonym) to a post-GA version. serg, krizhanovsky, opinions?

            It should, however, be possible to change the privilege type which SHOW GRANTS returns from the invalid one DELETE VERSIONING ROWS to DELETE HISTORY.

            elenst Elena Stepanova added a comment - I don't know whether we can add a privilege type (or a synonym) to a post-GA version. serg , krizhanovsky , opinions? It should, however, be possible to change the privilege type which SHOW GRANTS returns from the invalid one DELETE VERSIONING ROWS to DELETE HISTORY .

            my opinion: fix SHOW GRANTS to print the correct privilege name

            serg Sergei Golubchik added a comment - my opinion: fix SHOW GRANTS to print the correct privilege name
            mmakaay Maurice Makaay added a comment - - edited

            The ansible mysql_user module also fails because of this discrepancy.
            I agree with Sergei that the clean fix would be to have the SHOW GRANTS output represent the actual privilege name that can be used. The output suggests that one should be able to copy/paste the grant statement, but right now this is now possible.

            The error message that I end up with in Ansible is:
            fatal: [thehostname]: FAILED! => {"changed": false, "msg": "(1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON *.* FROM 'theuser'@'thehost'' at line 1\")"}

            In my case, I was able to work-around the issue in Ansible by using the following:

            {{- name: Create some database user
            mysql_user:
            name: "theusername"
            host: "thehost"
            password: "thepassword"
            priv: "theprivileges"
            append_privs: yes <--- by adding this, the bug is not hit in Ansible.
            }}

            Of course this work-around changes the actual functionality, but in my case that is not an issue (since the user that is created is a super user anyway, and no rights have to be revoked from a sync operation therefore).

            mmakaay Maurice Makaay added a comment - - edited The ansible mysql_user module also fails because of this discrepancy. I agree with Sergei that the clean fix would be to have the SHOW GRANTS output represent the actual privilege name that can be used. The output suggests that one should be able to copy/paste the grant statement, but right now this is now possible. The error message that I end up with in Ansible is: fatal: [thehostname] : FAILED! => {"changed": false, "msg": "(1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON *.* FROM 'theuser'@'thehost'' at line 1\")"} In my case, I was able to work-around the issue in Ansible by using the following: {{- name: Create some database user mysql_user: name: "theusername" host: "thehost" password: "thepassword" priv: "theprivileges" append_privs: yes <--- by adding this, the bug is not hit in Ansible. }} Of course this work-around changes the actual functionality, but in my case that is not an issue (since the user that is created is a super user anyway, and no rights have to be revoked from a sync operation therefore).
            marcolz Marc Olzheim added a comment - - edited

            The same thing fails for the puppetlabs-mysql module.

            Error: Execution of '/usr/bin/mysql --database=mysql -e REVOKE DELETE VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' at line 1
            Error: /Stage[main]/Mysql::Server::Monitor/Mysql_grant[theusername@thehost/*.*]/privileges: change from ['DELETE VERSIONING ROWS', 'SELECT'] to ['SELECT'] failed: Execution of '/usr/bin/mysql --database=mysql -e REVOKE DELETE VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' at line 1
            

            marcolz Marc Olzheim added a comment - - edited The same thing fails for the puppetlabs-mysql module. Error: Execution of '/usr/bin/mysql --database=mysql -e REVOKE DELETE VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' at line 1 Error: /Stage[main]/Mysql::Server::Monitor/Mysql_grant[theusername@thehost/*.*]/privileges: change from ['DELETE VERSIONING ROWS', 'SELECT'] to ['SELECT'] failed: Execution of '/usr/bin/mysql --database=mysql -e REVOKE DELETE VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VERSIONING ROWS ON *.* FROM 'theusername'@'thehost'' at line 1

            People

              midenok Aleksey Midenkov
              reinder Reinder Cuperus
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.