Root lost privileges

The root user of mysql server lost all privilages, Even GRANT ALL ON *.* TO 'root'@'localhost' doesnt work.

This is my local machine running windows XP SP2, Mysql 5.xx.

This is not a question on how to fix it, i know how, this is a question of why it happened.

I have a 3 year old that apparently was messing around with the machine a couple of days ago, but i kind of find it difficult to believe he could have revoked all privileges from mysql.

There was 1 other computer that connected to the network 3 days ago, could it be a virus ??

Could it be a hardware issue ?? One of the HDD failing somehow ?
LVL 20
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aleksandar BradarićSoftware DeveloperCommented:
If you take a look at the mysql.user table, you'll see that there are a couple of entries for the root user. Each of these has a different host (%, localhost, somehost, ...). It may be the one of these is blocking the others, e.g. you are not logging in as root@localhost, but rather as root@somehost which has limited privileges...
steelseth12Author Commented:
Hi leannonn thanks for the quick reply.
I dont believe that is the case as i have php applications on apache that have the connection hard coded.



and they are not working.

Also it all was working fine for a couple of months now until last night.

I dont have access to the machine right now but if i remember correctly i dont even have SELECT privileges on the mysql table. The only table that i have access to is information_schema.

I have all the hole thing backed up so i could easy restore mysql.user.MYI and everything goes back to normal, its just that i dont understand why it happened and its driving me crazy.
Aleksandar BradarićSoftware DeveloperCommented:
I see. No more ideas, I'm afraid :(
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

A couple of reasons that explain leannon's theory (with which I tottaly agree) can mostly be:

1) Change the IP on which mysql binds
2) Change of the Hostname of your localhost combined with binding to other than localhost
3) Change of the Hostname of your localhost combined with binding to other than localhost
4) Other similar reasons...

To push it a bit further:
If you are not binding on localhost (and you will not if you wish network acess to your db) then mysql does not understand the host as localhost. Each change in your system from name,ip even dns in some cases can cause such behaviour.

steelseth12Author Commented:
Nellios i said above that is not the case. I already replaced the user.MYI and user.MYD and now it works fine. Which means it has nothing to do with the environment but somehow privileges were revoked from root. The question is what could have caused this.
The fact that you restored user table doesn't mean that this is not the case.
Unless of course you checked the old tables and the priviledges are trully revoked.

The above example explains leannons point. What you prolly did by replacing the user table was to remove the conflicting rows and not just restoring the priviledges. What I explained you above is "what could have caused" conflicting rows.


Aleksandar BradarićSoftware DeveloperCommented:
> somehow privileges were revoked from root. The question is what could have caused this.

This is very strange. I can think of only three reasons for this:

1. Someone with root privileges (or someone who knows the root password) has been tampering with the privileges;
2. Your root account (or one of them if there are multiple entries in the mysql.user table) has no password and somebody used this to tamper with the privileges;
3. mysql.user table got corrupted (very unlikely)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Point 1: Is likely but only you can estimate how likely is having your root password compromised.
Point 2: Is likely since on windows by default user root@% has no password set (verified on previous versions of mysql). This means that anyone can acess your database with empty password.
Point 3: Seems almost impossible.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.