[MDEV-16712] Allow multiple roles at once Created: 2018-07-09  Updated: 2020-10-01  Resolved: 2020-10-01

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

Type: Task Priority: Minor
Reporter: Manjot Singh (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 1
Labels: None


 Description   

And for roles to take on roles.

Currently a user can only "wear one hat at a time"

GRANT USAGE ON *.* TO ‘myuser’@‘%’ IDENTIFIED BY PASSWORD ‘*abcdefxxxxxxxxx’;
 
GRANT mysqlread TO ‘myuser'@‘%’ ;                                                                      
GRANT mysqldml TO ‘myuser’@‘%’ ;                                                     
GRANT mysqlddl TO ‘myuser’@‘%’;                             
 
 
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO ‘mysqlread’;
 
 
 
GRANT INSERT, UPDATE, DELETE, EXECUTE ON `mydb`.* TO ‘mysqldml’;
 
                                                                                                
GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `mydb`.* TO ‘mysqlddl’;

Currently, SET ROLE mysqlread; SET ROLE mysqldml; means I can no longer issue SHOW DATABASES;

Ideally, both sets should apply. This would also mean we need UNSET ROLE mysqldml; available to remove current roles.

Additionally, roles should be able to stack:

GRANT mysqlread to mysqldml;

now SET ROLE mysqldml; should have the additional grants of SELECT, SHOW DATABASES, SHOW VIEW

 Comments   
Comment by Sergei Golubchik [ 2018-07-11 ]

You can, of course, grant roles to roles. This is standard feature.

But SQL standard doesn't allow to have more than one role being enabled. For example, it conflicts with the CURRENT_ROLE function and definitions like CREATE VIEW ... DEFINER CURRENT_ROLE ....

If you need to enable two rows at the same time, the standard answer is to create a new role, grant these two roles to the new role, and then enable this new role.

Comment by Manjot Singh (Inactive) [ 2018-07-11 ]

My testing showed that I could stack roles by granting roles to roles BUT I wasn't certain if this is a feature or a coincidence as it is not documented, therefore I was hesitant to recommend it to the customer.

serg When a role is granted 2 roles, can it utilize the permissions of both roles at the same time?

Additionally, the customer is coming from Oracle and feels they need to have the ability to apply multiple roles directly to a user.

Comment by Sergei Golubchik [ 2018-07-11 ]

It is a feature, and it is documented.

Yes, it can.

Comment by Manjot Singh (Inactive) [ 2018-07-11 ]

I was not looking at the correct docs. I think it should be clearer in our roles docs.

I think we can close this ticket, as I spoke with the customer and current functionality is enough.

Comment by Daniel Black [ 2020-10-01 ]

closed as requested.

Generated at Thu Feb 08 08:30:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.