ERROR 1045 (28000): Access denied for user 'root'@'%' for mysql

673 views Asked by At

I have two servers(RHEL) and both of them have mysql installed and configured(5.7.23-enterprise-commercial). When I am login to mysql in first server and tried this:

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.02 sec)

mysql> SHOW GRANTS FOR 'root'@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

Here all the things run fine.

On the other server, I am getting the output like below:

mysql> SHOW GRANTS FOR 'root'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'


mysql> SHOW GRANTS FOR 'root'@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

Due to this, I am Access denied issue every now and then.

ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

I tried granting the previeleges using this command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'hostname' WITH GRANT OPTION;

I get the same problem. What could be the issue.

Edit: I fetched some more information. If I am running the below query in first and second server, result is this:

First Server:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Second Server:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

Also got the result of the below query in my second server(where problem exists):

mysql> SELECT * FROM mysql.user WHERE User='root'\G
*************************** 1. row ***************************
                  Host: %
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *78377EDD3733CC2048B9FBB4B0E0299C8720D4FA
      password_expired: N
 password_last_changed: 2019-08-21 00:19:40
     password_lifetime: NULL
        account_locked: N
1 row in set (0.09 sec)
0

There are 0 answers