Bug #30558 Connection uses cached database privileges

• Submitted: 22 Aug 2007 9:40
• Modified: 22 Aug 2007 9:58
• Reporter: Alexander Nozdrin
• Status: Not a Bug
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• CPU Architecture: Any

[22 Aug 2007 9:40] Alexander Nozdrin

Description:
Database privileges for a user are cached in the connection. Thus, if some privieleges have been revoked, the user will not notice this until he/she reconnects.

How to repeat:
# Root connection:
(./mysql -u root mysql)

> CREATE DATABASE db1;
> CREATE TABLE t1(c INT);
> GRANT ALL PRIVILEGES ON db1.* TO u1@localhost;

# u1@localhost connection:
(./mysql -u u1 db1)

> INSERT INTO t1 VALUES(0);

# Root connection (continued):

> REVOKE ALL PRIVILEGES ON db1.* FROM u1@localhost;
> GRANT SELECT ON db1.* TO u1@localhost;

# u1@localhost connection (continued):

> INSERT INTO t1 VALUES(1);
--> Succeed.

> exit

# u1@localhost connection:
(./mysql -u u1 db1)

> INSERT INTO t1 VALUES(2);
--> Error: no DB privilege.

[22 Aug 2007 9:58] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/privilege-changes.html "Database privilege changes take effect at the next USE db_name  statement."