MySQL Bugs: #84378: Inconssistence with GRANT and REVOKE on role-based user

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:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A role-based user may have granted additional privileges but revoking privileges from a role-based user has no effect. Still REVOKE succeeds with no errors nor warnings. 

How to repeat:
DROP role rr;
DROP USER uu;
CREATE role rr;
GRANT INSERT ON *.* TO rr;
CREATE USER uu;
GRANT rr TO uu;
GRANT DELETE ON *.* TO uu;
SHOW GRANTS FOR uu;
/*
Grants for uu@%                  
---------------------------------
GRANT DELETE ON *.* TO `uu`@`%`  
GRANT `rr`@`%` TO `uu`@`%`       
*/

DROP role rr;
DROP USER uu;
CREATE role rr;
GRANT INSERT, DELETE ON *.* TO rr;
CREATE USER uu;
GRANT rr TO uu;
REVOKE DELETE ON *.* FROM uu; -- success with no warnings
SHOW GRANTS FOR uu;
/*
Grants for uu@%                 
--------------------------------
GRANT USAGE ON *.* TO `uu`@`%`  
GRANT `rr`@`%` TO `uu`@`%`   
*/ 

Suggested fix:
Don't know what best solution is or what standards say here.

Maybe something like a DENY privilege that always takes precedence would be a good idea to implement?

Hello Peter,

Thank you for the report and feedback!

Thanks,
Umesh

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)

@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)

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.

OK.