Theres an adage for user privileges that you should assign a user the least amount of privileges that he or she requires to perform their job function(s) and no more. That is why MySQL offers such a fine-grained access control system. While not the easiest system to grasp, once a DBA does, he or she tends to agree that it really is quite effective. In today's blog, we'll learn how to prevent a user from listing databases in MySQL.
Working with the mysql.user Table
The mysql.user table contains information about users that have permission to access the MySQL server, along with their global privileges. Although it is possible to directly query and update the user table, it's best to use GRANT and CREATE USER for adding users and privileges. To see the contents of the user table, we can use the DESC command:
The privilege that allows a user to obtain a list of databases via the SHOW_DATABASES command is Show_db_priv. Hence, on a new database, we can simply not add the user to it to prevent the user from seeing it. Otherwise, you can see which privileges a user currently has by issuing the SHOW GRANTS command:
SHOW GRANTS FOR 'bob_s'@'localhost';
Here's some example output for the "bob_s@localhost" user in Navicat Premium:
Revoking a User Privilege
The above output confirms that bob_s does have the SHOW DATABASES privilege. If we now wanted to remove that privilege, we can issue the REVOKE command:
REVOKE Show_db_priv ON sakila FROM bob_s'@'localhost;
In Navicat, we can set a user's privileges both at the server and database level on the Server Privileges and Privileges tabs of the user details. To access them, click the User button on the main button bar, select the user that you're interested in, and then click the Privilege Manager button on the Objects toolbar:
Here are bob_s@localhost's server-level privileges (including SHOW DATABASES):
To revoke the SHOW DATABASES privilege, we can simply uncheck the box beside the SHOW DATABASES label, and click the Save button.
Here are bob_s@localhost's privileges for the sakila database:
There, we can set also database-specific privileges such as those to create views, show views, drop tables, execute INSERT statements, etc. We can even manage privileges at the table and column level!
Conclusion
In today's blog, we saw how to prevent a user from listing databases in MySQL, both via the MySQL REVOKE command and using Navicat's Server Privileges and Privileges tabs. Which is the easier of the two is up for debate, but I personally find the checkbox approach more intuitive.
To learn more about managing users in Navicat, take a look at the Manage MySQL Users in Navicat Premium series:
- Part 1: Securing the Root
- Part 2: Creating a New User
- Part 3: Configuring User Privileges
- Part 4: The Privilege Manager tool
Interested in Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes!
