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)