Rowan Wookey added a comment - 2021-03-02 12:05 - edited Starting the server with the --init-file option pointing to a file with GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA unix_socket WITH GRANT OPTION ; Works around the proble,

Otto Kekäläinen added a comment - 2021-03-11 07:16 I confirm this. I came across another case where in 10.5.8 there was for root: SHOW GRANTS; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*XXX' WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION and after upgrade to 10.5.9 it turned into 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, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*XXX' WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION ...and root@localhost lost the privilege to run "GRANT ALL". The root use also lost access to the `mysql` database and thus could not edit permissions either. Fixed it by stopping the server and running manually bypassing grants: Fixed it via mariadbd-safe --skip-grant-tables --skip-networking and running UPDATE global_priv SET priv='{"access":549755813887,"plugin":"mysql_native_password","authentication_string":"*xxx","auth_or":[{},{"plugin":"unix_socket"}],"version_id":100509,"password_last_changed":1614075596}' WHERE Host='localhost' and User='root'; Followed by stopping the manually started server in and restarting the actual service in normal mode with grants enabled. This reverted the root user permissions to the same as they are out-of-the-box on new installations: MariaDB [(none)]> SHOW GRANTS; +-----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +-----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +-----------------------------------------------------------------------------------------------------------------------------------------+

Jonathan Rosser added a comment - 2021-03-25 19:05 I am seeing a very similar situation with 10.5.8 -> 10.5.9, possibly caused by the root user in the 10.5.8 installation having a password set before we do the upgrade. Thats a bit speculative currently though.

Elena Stepanova added a comment - 2021-03-28 23:46 From otto 's workaround I can see how it could have happened and why it only happens to some instances, but not all. When the data in global_priv is created "from scratch", superusers get "access":18446744073709551615 . If it was so in 10.5.8, it still works fine in 10.5.9. However, if a superuser is recreated on whatever reason, or its grants are modified, then the numeric "all-inclusive" value is different, even though it represents the same access. MariaDB [test]> create user new_root@localhost IDENTIFIED VIA mysql_native_password OR unix_socket; Query OK, 0 rows affected (0.013 sec)   MariaDB [test]> GRANT ALL PRIVILEGES ON *.* TO new_root@localhost; Query OK, 0 rows affected (0.005 sec)   MariaDB [test]> GRANT PROXY ON '' @ '%' TO new_root@localhost WITH GRANT OPTION ; Query OK, 0 rows affected (0.014 sec)   MariaDB [test]> SHOW GRANTS FOR root@localhost; + -------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | + -------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password OR unix_socket WITH GRANT OPTION | | GRANT PROXY ON '' @ '%' TO 'root' @ 'localhost' WITH GRANT OPTION | + -------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)   MariaDB [test]> SHOW GRANTS FOR new_root@localhost; + -----------------------------------------------------------------------------------------------------------------------------+ | Grants for new_root@localhost | + -----------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `new_root`@`localhost` IDENTIFIED VIA mysql_native_password OR unix_socket WITH GRANT OPTION | | GRANT PROXY ON '' @ '%' TO 'new_root' @ 'localhost' WITH GRANT OPTION | + -----------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) 10.5.8 MariaDB [test]> select * from mysql.global_priv where user in ( 'root' , 'new_root' ); + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Host | User | Priv | + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | localhost | root | { "access" :18446744073709551615, "plugin" : "mysql_native_password" , "authentication_string" : "" , "auth_or" :[{},{ "plugin" : "unix_socket" }], "password_last_changed" :1616974265} | | localhost | new_root | { "access" :274877906943, "version_id" :100508, "plugin" : "mysql_native_password" , "authentication_string" : "" , "auth_or" :[{},{ "plugin" : "unix_socket" }], "password_last_changed" :1616974300} | + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.004 sec) I didn't count, but I assume it is the actual combination of all existing privileges. And with the addition of the new privilege SLAVE MONITOR , the value has become insufficient for "GRANT ALL": 10.5.9 MariaDB [test]> SHOW GRANTS FOR new_root@localhost; + ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for new_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, BINLOG MONITOR, CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, CREATE USER , EVENT, TRIGGER , CREATE TABLESPACE, DELETE HISTORY, SET USER , FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY ON *.* TO `new_root`@`localhost` IDENTIFIED VIA mysql_native_password OR unix_socket WITH GRANT OPTION | | GRANT PROXY ON '' @ '%' TO 'new_root' @ 'localhost' WITH GRANT OPTION | + ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)   MariaDB [test]> select * from mysql.global_priv where user in ( 'root' , 'new_root' ); + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Host | User | Priv | + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | localhost | root | { "access" :18446744073709551615, "plugin" : "mysql_native_password" , "authentication_string" : "" , "auth_or" :[{},{ "plugin" : "unix_socket" }], "password_last_changed" :1616974265} | | localhost | new_root | { "access" :274877906943, "version_id" :100508, "plugin" : "mysql_native_password" , "authentication_string" : "" , "auth_or" :[{},{ "plugin" : "unix_socket" }], "password_last_changed" :1616974300} | + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) Because now it should be 549755813887, as otto mentioned. MariaDB [test]> GRANT ALL PRIVILEGES ON *.* TO new_root@localhost; Query OK, 0 rows affected (0.004 sec)   MariaDB [test]> select * from mysql.global_priv where user in ( 'root' , 'new_root' ); + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Host | User | Priv | + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | localhost | root | { "access" :18446744073709551615, "plugin" : "mysql_native_password" , "authentication_string" : "" , "auth_or" :[{},{ "plugin" : "unix_socket" }], "password_last_changed" :1616974265} | | localhost | new_root | { "access" :549755813887, "version_id" :100509, "plugin" : "mysql_native_password" , "authentication_string" : "" , "auth_or" :[{},{ "plugin" : "unix_socket" }], "password_last_changed" :1616974300} | + -----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)