MySQL Forums
Forum List  »  General

User password being modified as a result of grant statements?
Posted by: Jeff Fritz
Date: May 05, 2009 07:35AM


I'm experiencing trouble with MySQL resetting passwords after running grants. I've run into this a few times. Please bear with me as I'm not formally trained in MySQL users/perms so I may be doing something wrong...

- Context: I have two users, say admin_user and web_user.
- I believe that admin_user has GRANT ALL ON *.* ... WITH GRANT OPTION to both the 'admin_user'@'localhost' and 'admin_user'@'%' variations of the admin_user.
- web_user does not exist at this point in time; I want to create it with ALL permissions on the 'web_store' database.

I now create the 'web_store' database, and then run these queries...

mysql> GRANT ALL ON web_store.* TO 'admin_user'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON web_store.* TO 'web_user'@'localhost' IDENTIFIED BY 'random-password-here';
Query OK, 0 rows affected (0.00 sec)

Now, at this point in time, I log out of MySQL. The above actions resulted in:
- the password of 'admin_user'@'localhost' is now null. I can log in that user without a password.
- Can no longer use GRANT statement using 'admin_user'@'localhost'.
- The 'web_user'@'localhost' was created successfully.

I was able to reset my password on the 'admin_user'@'localhost' account using the 'admin_user'@'%' account (via mysql administrator), however when I logged into 'admin_user'@'localhost' the user had access to a very small subset of databases that it originally had.

I'm wondering what went wrong here, and it's probably related to the fact that I'm granting perms to a database on a user that already has perms on *.* databases.

And yet another twist -- I log in via mysql administrator using 'admin_user'@'%' and check out the mysql database users' table. It looks like the 'admin_user' has two entries in the users table, one with host = 'localhost' and host = null! It looks like I've been logging into 'admin_user'@'' this whole time instead of 'admin_user'@'%', but I don't understand this because I thought % is the remote-host wildcard and I don't know the significance of a null host. This looks like the stem of a lot of my problems since the privs for the null-host user are all Y and the privs for the 'localhost' user are all N.

Could someone please clear this up for me? How do I fix it so I have an admin_user that connect locally and remotely with all perms on all DBs with grant option?

Options: ReplyQuote

Written By
User password being modified as a result of grant statements?
May 05, 2009 07:35AM

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.