Creating database level user in MYSQL

Question

Our MySql production server has two different databases in it. For reports I want the user to only be able to see reports database and not main database. I created A user 'username@%' for the reports plugin in the server and gave it vie,select,execute privileges on reports db. Now when the developer connects to database using those credentials from reports plugin, they report that they are able to see the other database and all it's tables as well. What should I do to prevent them from seeing the other database. In short: when developer logs in from reporting plugin, they should only see the report database.

Answer 1

I would suggest starting by checking you haven't created multiple users with different host portions accidentally:

select user,host from mysql.user where user='usernameinquestion';

If you have multiple rows returned, you've created multiple users with different host portions. Check these and remove/fix if necessary.

Remember that MySQL grants go from most specific to most general (e.g. if [email protected] connects, and there are users [email protected] and [email protected].* which have different grants, the most specific matching username is used).

Check the permissions granted to these users:

SHOW GRANTS FOR 'user'@'host';

And revoke them as necessary:

REVOKE permission on db2.* from 'user'@'host';

• user3756152: Thanks This was v helpful. I tried the select query and it returned only 1 row so that confirms that there's only one user. The grants sql gave two rows with first one as GRANT USAGE ON . TO 'username'@'%'.

Answer 2

you can create user on database level by using the following command:

grant all privileges on MyDB.* to MyUser@'host' identified by 'password';

Note: you can change all privileges to any privileges you want, but you need to make sure its enough for the user to log in and get needed data.