[TRAFODION-2542] Grantor is not correct when granting privileges on behalf of a role

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: sql-security
• Assignee: Roberta Marton
• Reporter: Roberta Marton
• Created: 17/Mar/17 22:22
• Updated: 15/Sep/18 07:32
• Resolved: 25/Apr/18 15:38

Description

Example:

Admin user:

register user sql_user1;
register user sql_user2;
create role role1;
create schema abc;
create table abc.table1 (a int);
grant select on abc.table1 to role1;
grant role role1 to sql_user1 with grant option.

sql_user1 can grant privileges on table abc.table1 through role role1.
sql_user1 attempts a grant:

grant select on abc.table1 to sql_user2;

This works but it shouldn't because sql_user1 does not directly have the privileges to grant select. At this time, sql_user1 becomes the grantor or the privilege (instead of role1). If the privilege is later revoked, then it must be revoked by sql_user1 or through an administrator by specifying the GRANTED BY clause:

revoke select on abc.table1 from sql_user2 granted by sql_user1;

Instead, the grant should return an error and sql_user1 use the granted by clause:

grant select on abc.table1 to sql_user2 granted by role1;

Then anyone who has been granted role1 can revoke the privilege. Like the grant, the revoke operation would need to include the GRANTED BY clause:

revoke select on abc.table1 from sql_user2 granted by role1;
or shortened to
revoke select on abc.table1 from sql_user2 by role1;

Issue Links

• links to: GitHub Pull Request #1536