Bug #86178 Database privilege changes are not updated after flush privileges
Submitted: 4 May 2017 8:56 Modified: 26 Jun 2017 12:50
Reporter: Steven Hartland Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.15 OS:Any
Assigned to: CPU Architecture:Any

[4 May 2017 8:56] Steven Hartland
Description:
"When Privilege Changes Take Effect" states that database privileges effect existing client connections when privileges are flushed, this is not the case.

"A grant table reload affects privileges for each existing client connection as follows:
...
* Database privilege changes take effect the next time the client executes a USE db_name statement.
    Note:
      Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database or flushing the privileges.
"

https://dev.mysql.com/doc/refman/5.7/en/privilege-changes.html

How to repeat:
Grant or drop a database level permission and try to perform an action on an existing connection which would require the change.

Suggested fix:
Either:
1. Remove the portion of the Note which references "flushing the privileges"
2. Fix privileges so that existing connections honour privileges after flush privileges.
[4 May 2017 14:01] MySQL Verification Team
Hi!

Thank you for your bug report. However, our manual is quite correct on this issue.

First of all, flushing privileges should be done only if you have changed privileged tables with DML statements, which is totally unnecessary, since our GRANT/REVOKE (etc ...) set of commands is quite sufficient to change anything in the privilege system.

Next, a grant table reload does not mean that database privileges are revoked and then granted, while still being using the same database. No, grant reload means something like this (let us imagine that db1 and db2 are names of two databases):

use db1
// revoke or grant privileges for database db1
use db2
use db1

Try and you will see that your connection will reflect the changes. You could also do:

use db2
// revoke or grant privileges for database db1
use db1

Another way of checking this is to close the change privilege, close the connection, re-establish it and you will  see new privileges for all databases.

This is what our manual meant with privilege reload.

If that does not work with you, please, let us know which version are you using.
[4 May 2017 16:11] Steven Hartland
Thanks for the feedback Sinisa.

We understand how its meant to work and it does work as expected, however the terminology used, specifically "flushing the privileges", in the note infers that if a "flush privileges" command is executed; that existing connections will detect a database privilege change.

This is not the case, as reinforced by your comments. The confusion comes from the fact that the wording is so close the MySQL command, mentioned above, namely "flush privileges"

We believe simply changing this statement from:

"Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database or flushing the privileges."

to:
"Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database."

Eliminates this ambiguity.
[4 May 2017 16:14] MySQL Verification Team
Thank you Mr. Hartland,

Executing "FLUSH PRIVILEGES" should also update privileges for the connection. You do not have to change table with DML statements. It should work. Let me know if it does not.
[4 May 2017 17:28] Steven Hartland
Calling "flush privileges" (from the different connection) does not make already connected clients notice database level permission changes.
[4 May 2017 17:40] MySQL Verification Team
Hi Mr. Hartland,

I have tried the same and you are correct.

Verified as the documentation bug.

Thanks.
[26 Jun 2017 12:50] Paul DuBois
Posted by developer:
 
Change made as suggested, thanks.