CODEWITHSUNDEEP
mysql
dev.e.loper
dev.e.loper

Reputation: 36044

Granting rights to additional MySQL database causing issues

I have a user 'myuser' and two databases. 'db1' and 'db2'.

'myuser' already has rights to use db1 and I wanted to grant him additional permission to use 'db2' by using the following query:

GRANT ALL ON db2.* TO 'myuser'@'localhost';

After I ran the grant statement, 'myuser' lost the connection to the first database 'db1'

I'm afraid that I used a wrong host name. Instead of 'localhost' I should have used '%'. When I do:

 select host from mysql.user where user = 'myuser';

Now I see two host records '%' and 'localhost' for that user

When I run SHOW GRANT statements I get permissions for different hosts. 'localhost' and '%'. 

mysql> SHOW GRANTS FOR 'myuser'@'localhost';
+----------------------------------------------------------------------+
| Grants for myuser@localhost                                      |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost'                       |
| GRANT ALL PRIVILEGES ON `beta_version`.* TO 'myuser'@'localhost' |
+----------------------------------------------------------------------+

and 

mysql> SHOW GRANTS FOR 'myuser'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*6ASDFASDFASDF...' 
| GRANT ALL PRIVILEGES ON `myuser`.* TO 'myuser'@'%'                                                
+-----------------------------------------------------------------------------------------------------------+

What happened that caused 'myuser' to not work in my PHP scripts and in phpMyAdmin?

Upvotes: 1

Views: 149

Answers (2)

spencer7593
spencer7593

Reputation: 108400

MySQL identifies a user by BOTH the username and the host. When MySQL does the authentication at login, MySQL first looks for a hostname that is an exact match. If it doesn't find an exact match, then it looks for a host containing a '%' wildcard.

When you did the GRANT ... TO myuser@localhost, MySQL created a new user (with no password, because there was no IDENTIFIED BY given in the statement.

Then what happened, when you attempted to login as myuser from the localhost, mysqld tried to find an entry that matched 'myuser'@'localhost' in the mysql.user table and it found it. And the session got the privileges assigned to that user.

(To be a little more precise, mysqld doesn't really look at the contents of the mysql.user table, what it really looks at the in-memory structure, which was populated from the table when it was built. A rebuild of the memory structure is triggered by a GRANT, a REVOKE or a FLUSH PRIVILEGES statement.)

What was happening BEFORE you added that new user, mysqld was looking for an exact match on user and hostname, and didn't find one. But it did find an entry with the '%' wildcard, so it matched to that, so the session got all the privileges granted to the 'myuser'@'%' user.

The two users 'u'@'%' and 'u'@'localhost' are separate and distinct from each other. Privileges must be granted to each user individually. Any privileges granted to 'u'@'%' apply ONLY to that user and NOT to 'u'@'localhost'.

Upvotes: 2

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39981

USAGE means "no privileges and that is what you have given your user when connecting from localhost.

I think localhost takes precedence over % so when the user is connecting from localhost that is the grant that will be used.

Grant access to both schemas to % (or localhost if that is what you prefer) and it should be clear and work better.

Upvotes: 0

Related Questions