[MDEV-18884] GRANT ALL returns Access denied for user with GRANT OPTION Created: 2019-03-11  Updated: 2019-03-12  Resolved: 2019-03-12

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Documentation
Affects Version/s: 10.3.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Miroslav Lachman Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: GRANT, privileges, regression
Environment:

FreeBSD 11.2-RELEASE-p7 amd64 GENERIC

mariadb103-server-10.3.13
Name : mariadb103-server
Version : 10.3.13
Installed on : Sun Mar 3 21:48:39 2019 CET
Origin : databases/mariadb103-server
Architecture : FreeBSD:11:amd64


 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.

 Comments   
Comment by Elena Stepanova [ 2019-03-11 ]

It still works. However, in 10.3 there is a new privilege DELETE HISTORY which your sys_dbcreator is missing, so it doesn't have ALL on the database level anymore, and thus cannot grant it.
For real super-users, the upgrade script takes care of adding the privilege, but since your user has custom configuration, the script cannot guess that it needs to get the new one as well, you have to take care of it.

greenman, please see if you find it worth specifically mentioning somewhere in the KB. The confusion is understandable.

Comment by Miroslav Lachman [ 2019-03-11 ]

Ah, thanks for pointing me to DELETE HISTORY!
Somewhat confusing is this - I added DELETE HISTORY to the list of privileges:

GRANT SELECT, INSERT, UPDATE, DELETE, DELETE HISTORY, CREATE, DROP, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'sys_dbcreator'@'localhost' WITH GRANT OPTION;

But when I check privileges it shows DELETE VERSIONING ROWS

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, EVENT, TRIGGER, DELETE VERSIONING ROWS ON *.* TO 'sys_dbcreator'@'localhost' IDENTIFIED BY PASSWORD '*9A************2' WITH GRANT OPTION
 
1 row in set (0.000 sec)

I see it is mentioned in https://mariadb.com/kb/en/library/grant/ but it still is confusing if the table with privileges has DELETE HISTORY in the left column, but SHOW GRANTS shows DELETE VERSIONING ROWS ...

Comment by Elena Stepanova [ 2019-03-11 ]

I agree, we also find it a bit confusing. There is a separate bug report about it, MDEV-17655.

Comment by Ian Gilfillan [ 2019-03-12 ]

Have clarified and added the specific bug report to the docs

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