MySQL Forums
Forum List  »  Security

I accidentally revoked all privileges from root user, now unable to login to my mysql server
Posted by: Vicky Akana
Date: November 22, 2018 03:38AM

Hello,

[SORRY FOR THE LONG MESSAGE, I TRIED A LOT OF STAFF SO I WANTED TO GIVE YOU A CONTEXT]

Please help me, I have done a silly mistake; I accidentally revoked all privileges from root user from the same server.

- I first granted all privileges to root from the same server [because I was getting access denied error through a browser on some application I am working on, and I thought this could solve it].
I ran this command:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'my-server-ip-address' IDENTIFIED BY 'server-password';

=> This didn't solve anything, it rather affected another application hosting on the same server. So, without thinking too much, I wanted to undo what I had done, by revoking all the privileges.
I ran this command:
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'my-server-ip-address' IDENTIFIED BY 'server-password';


- As a result, I now cannot log in to MySQL server even from the server itself (I revoked privileges from exactly that server ip-address), I am getting access denied error.

=> Fortunately, I am able to access the MySQL database through a client (Navicat).
Through Navicat I have done the following:

- Checked 'information-schema' database, 'user_privileges' table, I tried to add a record with root privileges, BUT I get access denied (can't save anything to that database)
- Checked 'MySQL' database, 'user' table: to give privileges ==> in this table I saw a record with root user & my-server-ip-address as host, and changed all privileges from 'N' to 'Y'. [meaning for all privileges it had 'N']
-I restarted MySQL server

=> After changing all these, I still get access denied error when trying to login to Mysql server.


Please help me, I have done all I could do!

Options: ReplyQuote


Subject
Views
Written By
Posted
I accidentally revoked all privileges from root user, now unable to login to my mysql server
4107
November 22, 2018 03:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.