Bug #84378 | Inconssistence with GRANT and REVOKE on role-based user | ||
---|---|---|---|
Submitted: | 30 Dec 2016 15:52 | Modified: | 30 Jan 2017 10:57 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[30 Dec 2016 15:52]
Peter Laursen
[1 Jan 2017 10:53]
MySQL Verification Team
Maybe something like a DENY privilege that always takes precedence would be a good idea to implement?
[2 Jan 2017 7:21]
MySQL Verification Team
Hello Peter, Thank you for the report and feedback! Thanks, Umesh
[2 Jan 2017 15:48]
Georgi Kodinov
This is not a roles related bug. And definitely not 8.0 only. E.g. on a 5.7: mysql> create user uu; -------------- create user uu -------------- Query OK, 0 rows affected (0.00 sec) mysql> grant insert on *.* to uu; -------------- grant insert on *.* to uu -------------- Query OK, 0 rows affected (0.00 sec) mysql> revoke select on *.* from uu; -------------- revoke select on *.* from uu -------------- Query OK, 0 rows affected (0.00 sec)
[2 Jan 2017 16:13]
Peter Laursen
@Georgi .. I already figured out myself. But I think that the occurrence of the line GRANT USAGE ON *.* TO `uu`@`%` .. in my 2nd testcase clearly tells that the server does not understand what is going on here. There is no need to grant USAGE after REVOKE in this case, as the user already has a privilege based on a role. The server does not see that. USAGE applies to users having no other privilege and should only occur in the return of SHOW GRANTS when all privilges - whether roles or simple privileges - are revoked from the user. USAGE indicates that user may connect and may execute certain statements that do not read nor write data. But access to data is forbidden for a user with USAGE. (and besides a warning would be nice when revoking a nonexistent privelege and also when granting an already granted privilege, IMO)
[30 Jan 2017 10:33]
Georgi Kodinov
Peter, OK, so your problem is that you get 2 lines in "SHOW GRANTS FOR uu;", right ? IMHO this is to be expected. Privileges granted directly are in a different set from the ones granted via roles. And the USAGE privilege is defined a bit differently than you are describing it: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_usage. Basically it's a privilege that *every* existing user account has. And it authorizes no specific operations. So, in absence of any other directly granted privileges, USAGE is what you get in the set of privileges directly granted to the user account. We've selected to always report the set of privileges directly granted to users. And we're doing it in a pre- roles supported way. So presence or absence of role grants does not affect it. Please feel free to convert this bug to a documentation one so that a version of the above is added to the docs.
[30 Jan 2017 10:57]
Peter Laursen
OK.