You are not logged in.
Hi all,
latest MariaDB (11.3.2). I'm not using it from months, so that issue may very well be much older. I'm unable to grant all anymore, e.g.:
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0,000 sec)
MariaDB [(none)]> create user testuser;
Query OK, 0 rows affected (0,004 sec)
MariaDB [(none)]> grant usage on test.* to testuser;
Query OK, 0 rows affected (0,004 sec)
MariaDB [(none)]> grant alter,create,delete,drop,grant option,index,insert,select,update on test.* to testuser;
Query OK, 0 rows affected (0,006 sec)
MariaDB [(none)]> grant all on test.* to testuser;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'test'
MariaDB [(none)]> grant all privileges on `test`.* to 'testuser'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'test'In the past I used grant all all the time without issues. To my knowledge, the grant options for root are correct:
MariaDB [(none)]> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*...' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,000 sec)I tried to reset the root password and run mariadb-upgrade multiple times but nothing has changed.
Last edited by entidi (Yesterday 14:28:06)
Offline
After more investigation, I found the only problematic privilege is "SHOW CREATE ROUTINE" (whatever that is):
MariaDB [(none)]> grant show create routine on test.* to testuser;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'test'It think it is related to the following upstream bug: https://jira.mariadb.org/browse/MDEV-29167
Offline
This bug has been already fixed upstream. In the meantime one can manually fix it by:
start with skip-grant-tables
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
Kudos to Daniel Black for the solution.
Offline