Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Related to my MySQL issue

I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database.

I can't find any information about how this mysql.db works, is it like some kind of cached permission in there ? Will a mysqld restart flush it ?

mysql> show grants for user@'xx.xx.xx.%';
+-------------------------------------------------------------------------------------------------------------------------------+ 
| Grants for user@xx.xx.xx.%                                      |
+-------------------------------------------------------------------------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'user'@'xx.xx.xx.%' IDENTIFIED BY PASSWORD 'xxx' REQUIRE SSL |



 mysql> select host,db,user, select_priv,update_priv,delete_priv  from
 mysql.db where  user='user'; 
 | host         | db   | user    | select_priv | update_priv |  delete_priv | 
 | xx.xx.xx.1   | myDB | user    | Y           | Y           | Y
share|improve this question
    
Can you add the SHOW GRANTS FOR user@host to your question? –  Derek Downey Feb 17 '12 at 14:59
add comment

3 Answers

up vote 4 down vote accepted

From the docs, mysql.db is the table that handles database-specific GRANTS. That is to say, if you explicitly indicated a database in your GRANT command, it would show up in this table:

GRANT SELECT, INSERT ON foo.* TO `bar`@`localhost`;

So the user bar@localhost would have SELECT and INSERT marked as 'Y' in the mysql.db table.

To remove entries from this table, likewise you need to specify the database:

REVOKE SELECT, INSERT ON foo.* FROM `bar`@`localhost`;

Issuing a REVOKE INSERT ON *.* statement (all databases) will not affect this table.

Also, a DROP USER statement will clean up all the entries in the various mysql.* tables that deal with user authentication, such as mysql.db.

share|improve this answer
    
So, that means that if I grant a user on * .* , mysql.db will explicity show me those grants for each database ? Meaning a grant select, update, delete on * .* has been made in the past but than revoked ? –  Bastien974 Feb 17 '12 at 16:13
    
If you do a GRANT x ON *.*, it should not update the mysql.db table...the table is only updated if you specifically mention the database in the GRANT statement. –  Derek Downey Feb 17 '12 at 16:16
    
So the only way grant on multiple databases are in mysql.db is when it's explicitly granted on each one of them. Then after a revoke on * .* , I would see USAGE in mysql.user BUT still database-specific permission in mysql.db ? –  Bastien974 Feb 17 '12 at 16:21
    
@Bastien974 Yes. You can remove them from mysql.db by issuing multiple REVOKE statements on each database.* –  Derek Downey Feb 17 '12 at 16:24
    
Good and concise (aka quick and dirty) explanation. +1 !!! –  RolandoMySQLDBA Feb 17 '12 at 17:50
add comment

It's not exactly a cache, but rather the physical store of your system internals. Permissions are one thing that's stored in there. You will also find tables that house information for stored procedures, functions and events.

If you have query logging or profiling enabled and configured in a certain way, you will also find that information in mysql db tables.

If your question is about users being able to query the mysql db tables, avoid . grants. Instead give the appropriate perms to specific db.* or db.tablename.

share|improve this answer
add comment

I have a great precaution you must exercise for your mysql.db

Run this query, please:

SELECT COUNT(1) test_db_count FROM mysql.db WHERE SUBSTR(db,4) = 'test';

If you get test_db_count = 2, get rid of them immediately !!!

Here is why : Anonymous users have access to any database whose first 4 letters are test. You can perform lots of CRUD intensive things in a test database. You may also want to rename the test databases to something completely different. Please read these links because I have addressed this issue before in the DBA StackExchange.

To confirm the need to do this, please note what MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

  • Set a password for the root accounts
  • Remove any remotely accessible root accounts.
  • Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
  • Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

To get rid of those bad entries, run this please:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;
share|improve this answer
    
Thanks for the advice ;) Great best-practice –  Bastien974 Feb 17 '12 at 18:31
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.