Mysql user can see all databases, but should only see his own

MySQL

We're using Virtualmin to do most of our database management, but apparently it has a bug that sets a user's permissions too broadly in some cases. Also, the MySQL manual mentions nothing about restricting a user's ability to see other databases with the "show databases" command.

When I run "show grants for 'user'@'localhost';" I get this:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost                                                                                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'ENCRYPTED'                                                                |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `database`.* TO 'user'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This is even after I've run "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON database.* to 'user'@'localhost';"

Best Answer

Not sure what your actual question is, but if it's why can the user still see everything, it's because those grants are additive.

You need to remove the grant you don't want (the first one) as well as include the second one.

i.e. remove

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'ENCRYPTED'

and keep,

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `database`.* TO 'user'@'localhost' WITH GRANT OPTION

Try,

REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON *.* FROM 'user'@'localhost'
Related Topic