[TRAFODION-2441] user has only select privilege on a table can do insert/update/delete on the view

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.2.0
• Component/s: sql-security
• Environment: hadoop - CDH 5.6; OS -- CentOS 6.7
• Assignee: Roberta Marton
• Reporter: Gao, Rui-Xian
• Created: 12/Jan/17 02:59
• Updated: 23/Feb/17 16:58
• Resolved: 15/Feb/17 15:59

Description

a user has only select privilege on the table can do insert/update/delete on the view, then data in base table also get inserted/updated/deleted

Reproduce Steps
=======================================

1. connect as trafodion –

create table testtab1(a int, b int);
select * from testtab1;
grant select on testtab1 to qauser_sqlqaa;
showddl testtab1;

2. connect as qauser_sqlqaa –

select * from testtab1;
create view v_tab1 as select * from testtab1;
showddl v_tab1;
insert into v_tab1 values(1,1);
select * from v_tab1;
select * from testtab1;
delete from testtab1;
delete from v_tab1;
select * from testtab1;

Test OutPut
========================================
User Name: trafodion
Password:

Role Name [Primary Role]:

Connected to EsgynDB Advanced

SQL>create table testtab1(a int, b int);

— SQL operation complete.

SQL>select * from testtab1;

— 0 row(s) selected.

SQL>grant select on testtab1 to qauser_sqlqaa;

— SQL operation complete.

SQL>showddl testtab1;

CREATE TABLE TRAFODION.SEABASE.TESTTAB1
(
A INT DEFAULT NULL NOT SERIALIZED
, B INT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;

– GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TESTTAB1 TO DB__ROOT WITH GRANT OPTION;
GRANT SELECT ON TRAFODION.SEABASE.TESTTAB1 TO QAUSER_SQLQAA;

— SQL operation complete.

SQL>connect qauser_sqlqaa/QAPassword;
Connected to EsgynDB Advanced

SQL>select * from testtab1;

— 0 row(s) selected.

SQL>create view v_tab1 as select * from testtab1;

— SQL operation complete.

SQL>showddl v_tab1; // user only has SELECT privilege on the view

CREATE VIEW TRAFODION.SEABASE.V_TAB1 AS
SELECT TRAFODION.SEABASE.TESTTAB1.A, TRAFODION.SEABASE.TESTTAB1.B FROM
TRAFODION.SEABASE.TESTTAB1 ;

– GRANT SELECT ON TRAFODION.SEABASE.V_TAB1 TO QAUSER_SQLQAA;

— SQL operation complete.

SQL>insert into v_tab1 values(1,1); // user can insert data into view

— 1 row(s) inserted.

SQL>select * from v_tab1;

A B
----------- -----------
1 1

— 1 row(s) selected.

SQL>select * from testtab1; // data in base table

A B
----------- -----------
1 1

— 1 row(s) selected.

SQL>delete from testtab1; // expected, user doesn’t have privilege to delete data from base table

• • • ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.SEABASE.TESTTAB1. [2017-01-12 10:39:11]

SQL>delete from v_tab1; // user doesn’t have delete privilege but can delete data from the view

— 1 row(s) deleted.

SQL>select * from testtab1; // data in base table got deleted

— 0 row(s) selected.

Issue Links

• links to: GitHub Pull Request #957
• links to: GitHub Pull Request #975