CentOS Question posted in
The official documentation can be found here.

Recover my root user's DBA privileges on MariaDB – CentOS

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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_userG"
*************************** 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@localhostG"
*************************** 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search