MySQL Bugs: #7191: REVOKE doens't remove permissions from a logged in user

Bug #7191	REVOKE doens't remove permissions from a logged in user
Submitted:	11 Dec 2004 9:46	Modified:	17 Jan 2014 14:56
Reporter:	Jan Kneschke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	4.1.7, 5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A privilege on DB-level is not removed from the session of a logged-in user if the admin 
is revoking it at runtime. This works in all other GRANT, REVOKE tests I made at 
runtime. Only for the DB-scope is this not working. 
 
As soon as the user closes the session and reopens it, the privileges are really lost. 
 
Even if SHOW GRANTS doesn't show the permissions anymore, the user still has all his 
old permissions active. 

How to repeat:
$ mysql -u root -p 
mysql1> GRANT ALL ON grantbug.* TO foo@localhost IDENTIFIED BY 'secret'; 
 
open another shell for the new user: 
$ mysql -u foo -p 
mysql2> CREATE DATABASE grantbug; 
mysql2> USE grantbug; 
 
revoke all permission from the user while he is logged in: 
mysql1> REVOKE ALL ON grantbug.* FROM foo@localhost; 
 
mysql2> SHOW GRANTS; 
(shows only GRANT USAGE ON *.* TO foo@localhost ... ) 
mysql2> CREATE TABLE abc ( a INT ); 
Succeeds 
 

Suggested fix:
Really revoke the permissions from logged-in user.

I didn't see any FLUSH PRIVILEGES statement.

It'll be fixed when roles will be implemented

See:

http://dev.mysql.com/doc/mysql/en/privilege-changes.html

Try a USE statement and see if the change takes effect,
as indicated in the manual.

It behaves according to the manual, but SHOW CREATE table doesn't reflect it.

Still valid on 5.6.17. Moving to verified.

Seems to be fixed in (or before) 5.7.

Now CREATE TABLE fails and SHOW GRANTS reflects the reality.