Mysql – Help granting MySQL user privileges to one database only

MySQLmysql-5.6

I'm having trouble granting permissions to a user on a single database. The user can't log in once I've done what's below. I'm running as root on MySQL 5.6

SELECT USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

Here's my database list (abridged)

list databases;

+-------------------------+
| Database                |
+-------------------------+
| hr_test                 |

First I create the new user

CREATE USER 'hr_test_user'@'localhost' IDENTIFIED BY 'password';

This seems to work (i'm not showing all the other users).

select * from information_schema.user_privileges;
| 'hr_test_user'@'localhost'                | def           | USAGE                   | NO           |

I then try to grant privileges to one database

GRANT ALL PRIVILEGES ON hr_test.* TO 'hr_test_user'@'localhost'  IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

When I run "select * from information_schema.user_privileges;" nothing has changed, the user just has "usage". When the user tries to log in they get rejected.

However when I grant wide privileges as such the line below I get all privileges.

GRANT ALL PRIVILEGES ON *.* TO 'hr_test_user'@'localhost'  IDENTIFIED BY 'password';

The table shows these privileges (abridged)

| 'hr_test_user'@'localhost'                | def           | SELECT                  | NO           |
| 'hr_test_user'@'localhost'                | def           | INSERT                  | NO           |
| 'hr_test_user'@'localhost'                | def           | UPDATE                  | NO           |
| 'hr_test_user'@'localhost'                | def           | DELETE                  | NO           |
| 'hr_test_user'@'localhost'                | def           | CREATE                  | NO           |
| 'hr_test_user'@'localhost'                | def           | DROP                    | NO           |

I can drop the user and do it again, but I get the same result. I've done this before to create other users with no problems, I even recorded exactly what I did last time when it worked, so I'm puzzled.

It looks like I'm identifying the database I want to grant privileges on incorrectly, but I can't spot what I'm doing wrong. Can anyone else?

Update
Based on the answer from @SQL.RK I recreated the user with the privileges required, then ran this additional command

select * from information_schema.SCHEMA_PRIVILEGES;

This is the relevant part of the output

| 'hr_test_user'@'localhost'     | def           | hr_test            | SELECT                  | NO           |
| 'hr_test_user'@'localhost'     | def           | hr_test            | INSERT                  | NO           |
| 'hr_test_user'@'localhost'     | def           | hr_test            | UPDATE                  | NO           |
| 'hr_test_user'@'localhost'     | def           | hr_test            | DELETE                  | NO           |
| 'hr_test_user'@'localhost'     | def           | hr_test            | CREATE                  | NO           |

That looks fine to me. I tried to log in and it worked, when I did what seems exactly the same as before!

Theory
I copied this text out of my password manager where I keep database authentication information and the commands I use to create databases. I noticed when I copied out of there I didn't have to hit enter in MySQL. I wonder if the grant command wasn't actually executing.

I don't think this is it because "grant all privileges" was also in there and that worked. I'm a bit stumped. I won't mark this as answered just yet, I'd like to hear any theories or hear if I've done anything clearly incorrect first.

Update 2

show grants;
+-------------------------------------------------------------------------------------------+
| Grants for hr_test_user@localhost                                                             |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hr_test_user'@'localhost' IDENTIFIED BY PASSWORD <secret>          |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `hr_test`.* TO 'hr_test_user'@'localhost' |
+-------------------------------------------------------------------------------------------+

Best Answer

Based on your comment, I say don't worry.

The output for SHOW GRANTS; is correct. The user hr_test_user can only access the hr_test database, and no other database.

Why would the output be split like that ?

  • mysql.user stores the user,host,password of hr_test_user@localhost
  • mysql.db stores the privileges of hr_test_user@localhost

You will have to run the grants for each individual database to access them.

For example, to add grants for db1 and db2, you would run

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `db1`.* TO 'hr_test_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `db2`.* TO 'hr_test_user'@'localhost';

Now, I have some bad news. If you have a test database, you should drop it. Do not create any test databases because mysql installations will secretly give away full access to a test database that is named test or any database name starting with test_. I wrote all about that in MySQL : Why are there "test" entries in mysql.db?

If you need any test databases, don't name it test and don't name it with test_ as prefix.

Related Question