1

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';"

5

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'
  • And how does one remove the old GRANT and keep the new one? :) – Ernie Aug 25 '11 at 18:05
  • You Revoke it. – Zoredache Aug 25 '11 at 18:12
  • Added the revoke – EightBitTony Aug 25 '11 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.