[MDEV-17655] Inconsistent grant-name usage between grant-statement and privilege tables Created: 2018-11-09  Updated: 2019-08-19  Resolved: 2019-05-02

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Versioned Tables
Affects Version/s: 10.3.9, 10.3
Fix Version/s: 10.3.15, 10.4.5

Type: Bug Priority: Critical
Reporter: Reinder Cuperus Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-20382 SHOW PRIVILEGES displays "Delete vers... Closed

 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

 Comments   
Comment by Elena Stepanova [ 2018-11-09 ]

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.

Comment by Sergei Golubchik [ 2018-11-10 ]

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

Comment by Maurice Makaay [ 2019-01-18 ]

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).

Comment by Marc Olzheim [ 2019-04-11 ]

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

Generated at Thu Feb 08 08:38:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.