[TRAFODION-2294] need add privilege checking for explain statement

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.2.0
• Component/s: sql-security
• Environment: CDH5.4.8; Centos6.7
• Assignee: Gao, Rui-Xian
• Reporter: Gao, Rui-Xian
• Created: 19/Oct/16 08:29
• Updated: 15/May/17 03:13
• Resolved: 01/May/17 15:24

Description

we don't have privilege checking for 'explain' statements.If a user doesn't have privilege to execute a statement, then after explain, the user will be able to execute the statement.

Testuser1:
SQL>create schema testsch1;

— SQL operation complete.

SQL>set schema testsch1;

— SQL operation complete.

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

— SQL operation complete.

SQL>insert into tab1 values(1,1);

— 1 row(s) inserted.

SQL>select * from tab1;

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

— 1 row(s) selected.

Tesuser2:
SQL>set schema testsch1;

— SQL operation complete.

SQL>get tables;

Tables in Schema TRAFODION.TESTSCH1
===================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
TAB1

— SQL operation complete.

SQL>select * from tab1;

• • • ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:07:32]

SQL>explain options 'f' select * from tab1;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root 1.00E+002
. . 1 trafodion_scan TAB1 1.00E+002

— SQL operation complete.

SQL>select * from tab1;

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

— 1 row(s) selected.

SQL>insert into tab1 values(20,20);

• • • ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:11:03]

SQL>explain options 'f' insert into tab1 values(20,20);

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root o 1.00E+000
. . 1 trafodion_insert TAB1 1.00E+000

— SQL operation complete.

SQL>insert into tab1 values(20,20);

— 1 row(s) inserted.

SQL>select * from tab1;

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

— 2 row(s) selected.

SQL>showddl tab1;

CREATE TABLE TRAFODION.TESTSCH1.TAB1
(
A INT DEFAULT NULL SERIALIZED
, B INT DEFAULT NULL SERIALIZED
)
;

– GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.TESTSCH1.TAB1 TO TESTUSER1 WITH GRANT OPTION;

— SQL operation complete.

Issue Links

• links to: GitHub Pull Request #1074
• links to: GitHub Pull Request #1082