[TRAFODION-2189] user has incorrect privilege on columns if multiple roles with different column privileges are granted to the user

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: sql-security
• Assignee: Roberta Marton
• Reporter: Gao, Rui-Xian
• Created: 26/Aug/16 13:19
• Updated: 17/Oct/16 18:23
• Resolved: 17/Oct/16 18:23

Description

create role role1;
create role role2;
create schema mysch;
set schema mysch;
create table tab1(col1 int not null primary key,col2 int ,col3 varchar(10));

grant insert(col1,col2), select(col1,col2) on tab1 to role1 with grant option;
grant update(col2) on tab1 to role1 with grant option;
grant update(col2) on tab1 to role2 with grant option;

grant role role1, role2 to qauser2;

connect as qauser2:

set schema mysch;
insert into tab1(col1,col2) values(2,2); // insert should succeed because role1 has select privilege on col2.

• • • ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.MYSCH.TAB1(columns: COL2). [2016-08-24 10:04:08]
    • ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.MYSCH.TAB1(columns: COL2). [2016-08-24 10:04:08]