[MDEV-23816] GRANT role TO user denied to ALL PRIVILEGES user Created: 2020-09-25  Updated: 2021-03-09  Resolved: 2020-09-25

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.46, 10.2.34
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

roles.grant/grant.test

 
SHOW GRANTS;
 
 
 
CREATE USER 'bob'@'%';
 
CREATE USER 'judy'@'%';
 
CREATE ROLE 'role_1' WITH ADMIN 'bob'@'%';
 
GRANT SELECT ON mysql.user TO 'bob'@'%';
 
GRANT role_1 TO 'judy'@'%';
 
GRANT role_1 TO 'bob'@'%' WITH ADMIN OPTION;

results

 
SHOW GRANTS;
 
Grants for root@localhost
 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
 
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
 
CREATE USER 'bob'@'%';
 
CREATE USER 'judy'@'%';
 
CREATE ROLE 'role_1' WITH ADMIN 'bob'@'%';
 
GRANT SELECT ON mysql.user TO 'bob'@'%';
 
GRANT role_1 TO 'judy'@'%';
 
roles.grant                              [ fail ]
 
        Test ended at 2020-09-25 17:59:58
 
 
 
CURRENT_TEST: roles.grant
 
mysqltest: At line 8: query 'GRANT role_1 TO 'judy'@'%'' failed: 1698: Access denied for user 'root'@'localhost'



 Comments   
Comment by Sergei Golubchik [ 2020-09-25 ]

I don't think it's a bug. there is no such privilege "all privileges" that allows to do just everything. ALL PRIVILEGES is a short name for the list of all individual privileges. And there is no special privilege that allows you to grant roles you aren't admin of.

As far as MDEV-23630 is concerned, you can do, like

CREATE ROLE 'role_1';
 
GRANT 'role_1' TO 'bob'@'%' WITH ADMIN;
 
GRANT SELECT ON mysql.user TO 'bob'@'%';
 
GRANT role_1 TO 'judy'@'%';
 
REVOKE role_1 FROM CURRENT_USER;

Comment by Daniel Black [ 2020-09-28 ]

probably was meaning `create user` priv.

Thanks for the hint. Modified slightly by using a mariadb_dump_import_role that has the admin of the role, and just drop the mariadb_dump_import_role at the end. Easier than revoking in bulk.

Generated at Thu Feb 08 09:25:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.