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

[Gao, Rui-Xian (JIRA)]

Gao, Rui-Xian created TRAFODION-2441:
----------------------------------------

             Summary: user has only select privilege on a table can do 
insert/update/delete on the view
                 Key: TRAFODION-2441
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2441
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-security
    Affects Versions: 2.2-incubating
         Environment: hadoop - CDH 5.6
OS -- CentOS 6.7
            Reporter: Gao, Rui-Xian
            Assignee: Roberta Marton


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.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)