Recover my root user's DBA privileges on MariaDB

Question

I accidentally removed the DBA privileges of my only user from mariadb and now I can't use my bank and I was also unable to restore the privileges using the Linux SHELL CentOS 7.

Command I tried:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

return:

#1045 - Access denied for user 'root'@'localhost' (using password: YES)

Has anyone ever experienced this?

Answer 1

It's a wrong assumption that the wildcard for hostname also includes localhost, so an attempt to connect via socket (localhost) will fail:

mysql> create user 'foo'@'%' identified by 'bar';
Query OK, 0 rows affected (0,01 sec)

$mysql -ufoo -pbar -e"select current_user"
ERROR 1045 (28000): Access denied for user 'foo'@'localhost' (using password: YES)

When creating the user with localhost, everything works as expected:

mysql> create user 'foo'@'localhost' identified by 'bar';
Query OK, 0 rows affected (0,01 sec)

$ mysql -ufoo -pbar -e"select current_user\G"
*************************** 1. row ***************************
current_user: foo@localhost

Also beginning with MariaDB Server 10.4 (Posix platforms) the default authentication method for the root user (when connecting via localhost) happens via unix_socket plugin. This allows the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating system root user account:

mysql -e"show grants for root@localhost\G"
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION

So a normal login as root will fail:

georg@mozart:~/mariadb$ mysql -uroot -e"select current_user()\G"
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

While running as root (sudo) login works as expected:

georg@mozart:~/mariadb$ sudo mysql -uroot -e"select current_user()\G"
*************************** 1. row ***************************
current_user(): root@localhost