[MySQL] - Root lost grant privileges

wabby · 02-13-2011, 04:23 PM

Hello,

for some reason root lost grant privileges and I can't get it back.

The user got grant_priv = 'Y' in the usertable.
I've tried to reset all the priviliges to the user by killing MySQL process and started up again without grant options with no luck.

I've also tried lots of other things but can't seem to get it work. Here is what I get:

Quote:

[root@xxxxxxxxx mysqldba]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried so many different things now that I'm all out of ideas

tenmei08 · 02-13-2011, 05:35 PM

Initially, try logging in as root and executing the following:

Quote:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

(In the code you quoted you tried granting ALL PRIVILEGES to 'dev'@'localhost'.)

If this won't work and the user account has grant privileges, try logging in as the user and running the same instructions above.
Fix root, then tackle correcting the user account.

wabby · 02-13-2011, 05:44 PM

Quote:

Originally Posted by tenmei08

Initially, try logging in as root and executing the following:

(In the code you quoted you tried granting ALL PRIVILEGES to 'dev'@'localhost'.)

If this won't work and the user account has grant privileges, try logging in as the user and running the same instructions above.
Fix root, then tackle correcting the user account.

Hi,
I know i tried to grant to 'dev' user. Thats because the root user should be abel to.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Problem is as you stated the root user. Everything also looks ok mysql.user table.
No users is abel to grant, even when everything looks ok. It's like the mysql server ignores FLUSH, and restart of Mysqld.

fordeck · 02-13-2011, 06:41 PM

What does the following command show you:

Code:

mysql> show grants;

Regards,

Fordeck

wabby · 02-14-2011, 01:32 AM

Quote:

Originally Posted by fordeck

What does the following command show you:

Code:

mysql> show grants;

Regards,

Fordeck

The wierdes thing.....

Quote:

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SUPER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxx' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql>

wabby · 02-15-2011, 02:20 AM

Hummm, getting out of ideas now :S Going to order a reboot on that box now.
Any ideas?

fordeck · 02-15-2011, 08:29 AM

So do you have complete root/administrative control of the machine? Given your last post it is not clear

Quote:

Originally Posted by wabby

Hummm, getting out of ideas now :S Going to order a reboot on that box now.
Any ideas?

If you have complete root access you could always try restoring the corrupt grant tables from a backup. Or create a fresh one.

Is MySQL running on a windows or a linux platform?

On linux I believe you can create a fresh grant table using mysql_install_db. However you should be aware that if the user table has to be created, this will re-create the mysql database which includes your users (i.e. root ...) so root would end up with a blank password and other users would need to be re-created.

mysql_install_db may not be in your system path but is usually found in the scripts directory under the MySQL install directory.

Anyway you may want to look into mysql_install_db as it may take care of your problem.

Regards,

Fordeck

wabby · 02-15-2011, 02:44 PM

Hello,

thanks for your reply. I have root access, but I need to announce restart on a server that is used by all developers.
But we found the problem. When this machine was installed up (virtual OS - Centos) it was in a network with a very restricted firewall.
During the MySQL installations it does a internal lookup that failed. For some reason that screwed up the whole installation and made alot of wierd error.

So the solution was actually a re-install of MySQL, luckily this is a developer box, so restoring all the databases from live didnt take long. But I wanted to try to figure out and fix the problem without reinstall just in case if a similar problem would occur in the future on a live box.

Anyway, thanks for the replies.