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.
MySQL – How to Create Database Level User
MySQLusers
Related Question
- MySQL – Grant All Privileges Except ‘Create User’
- MySQL – Troubleshooting Access Issues with Created User Through DLL
- SSRS 2014 Exec Log gives the Service Account as UserName instead of Actual User name
- Mysql – Access denied error in trying to connect to a MySQL db in Python
- Mysql – Why can’t I create a MySQL user even if i have ‘all priviliges’
- PostgreSQL – Administration as Non-Superuser
Best Answer
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 user@10.0.0.1 connects, and there are users user@10.0.0.1 and user@10.0.0.* 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';