[TRAFODION-3218] User still has privilege after user's role has been revoked or privilege has been revoked from the user's role

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: sql-security
• Assignee: Roberta Marton
• Reporter: Roberta Marton
• Created: 03/Oct/18 15:36
• Updated: 24/Oct/18 16:23
• Resolved: 24/Oct/18 16:23

Description

Grant a select column privilege on a table to a user and then grant select object privilege to one of the user’s role to the same table.  User can select all columns from the table.  Next revoke object select privilege from the role (or revoke role from user), the user still has the select object privilege on the table.
The issue can’t be reproduced if there is no column priv granted to the user.

Steps to recreate:

Start a session for db__root

register user sql_user1;
create role role1;
grant role role1 to sql_user1;

create schema traf_ht;
set schema traf_ht;
create table traft1(a varchar(10) not null primary key, b varchar(20));
grant select(a) on traft1 to sql_user1;
grant select on traft1 to role1;
showddl traft1;

Start a session for sql_user1:

set schema traf_ht;
prepare s1 from select * from traft1;

explain s1;

Go to db__root session:

revoke select on traft1 from role_a;

Go to sql_user1 session:

set schema traf_ht;
select * from traft1;  -> should fail because no longer has select priv, but it works

Log out and reconnect as sql_user1 and the request fails as expected.