2

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?

Bonfim Jr
  • 53
  • 8
  • See [this question](https://dba.stackexchange.com/questions/193854/cannot-reset-mysql-default-root-password-after-initial-installation) on dba.stackexchange for details on how to reset the password. – Dave Jul 21 '20 at 12:16
  • @Dave unsuccessful, the tip is password recovery, I know the password. My problem is that my root user has no DBA but has no privileges and that is what I want to restore. – Bonfim Jr Jul 21 '20 at 13:38
  • I'd like to "bump" this question as I'm having the exact same issue. – Christian Jul 21 '20 at 17:43
  • What server version do you use? I also doubt that the error message above is returned when you send the GRANT statement, it looks more like a login attempt from command line client. – Georg Richter Jul 25 '20 at 06:29

2 Answers2

2

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
Georg Richter
  • 5,970
  • 2
  • 9
  • 15
1

Stop MariaDb server

sudo systemctl stop mariadb

Restart DB without permission checking - more

sudo mysqld_safe --skip-grant-tables --skip-networking &

mysql -u root enter password

reload the grant tables

Mariadb[MYSQL]> FLUSH PRIVILEGES

insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) 
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

Then restart database

sudo systemctl start mariadb I hope this works for you. it did work for me.

You can explore answers on Grant privileges on MariaDB

linkonabe
  • 661
  • 7
  • 23
  • 1
    Instead of using `GRANT` and `CREATE USER` statements people frequently bork their database configuration by modifying system tables directly. This is not possible anymore in recent versions of MariaDB, since these tables became non-insertable views. – Georg Richter Jul 25 '20 at 13:50