Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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'
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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