[ https://issues.apache.org/jira/browse/TRAFODION-2441?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15868068#comment-15868068 ]
ASF GitHub Bot commented on TRAFODION-2441: ------------------------------------------- Github user asfgit closed the pull request at: https://github.com/apache/incubator-trafodion/pull/957 > 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.15#6346)