User with select and insert priv unable to UNLOAD

[Paul Low]

The following error is returned when a user (granted SELECT and INSERT on tables) is unable to UNLOAD.

*** ERROR[4082] Object TRAFODION.SCHEMA1.DUMMY does not exist or is inaccessible

User1 creates a shared schema and two tables. User1 is able to perform LOAD and UNLOAD.
User1 grants SELECT and INSERT privileges on both tables to User2.
User2 is able to LOAD, but UNLOAD fails.
See log below:

Connected to Trafodion

SQL>create shared schema schema1;

--- SQL operation complete.

SQL>set schema schema1;

--- SQL operation complete.

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

--- SQL operation complete.

SQL>create table tab2(c int, d int)no partition;

--- SQL operation complete.

SQL>insert into tab1 values(1,2),(4,5);

--- 2 row(s) inserted.

SQL>load into tab2 select * from tab1;

UTIL_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Task: LOAD Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: CLEANUP Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: CLEANUP Status: Ended Object: TRAFODION.SCHEMA1.TAB2
Task: DISABLE INDEXE Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: DISABLE INDEXE Status: Ended Object: TRAFODION.SCHEMA1.TAB2
Task: PREPARATION Status: Started Object: TRAFODION.SCHEMA1.TAB2
       Rows Processed: 2
Task: PREPARATION Status: Ended ET: 00:00:00.788
Task: COMPLETION Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: COMPLETION Status: Ended ET: 00:00:00.574
Task: POPULATE INDEX Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: POPULATE INDEX Status: Ended ET: 00:00:01.656

--- SQL operation complete.

SQL>unload with purgedata from target into '/bulkload/4security' select * from tab2;

UTIL_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Task: UNLOAD Status: Started
Task: EMPTY TARGET Status: Started
Task: EMPTY TARGET Status: Ended ET: 00:00:00.005
Task: EXTRACT Status: Started
       Rows Processed: 2
Task: EXTRACT Status: Ended ET: 00:00:00.717

--- 5 row(s) unloaded.

SQL>grant select, insert on tab1 to USERNAME2;

--- SQL operation complete.

SQL>grant select, insert on tab2 to USERNAME2;

--- SQL operation complete.

SQL>connect USERNAME2/PASSWORD2;
Connected to Trafodion

SQL>set schema schema1;

--- SQL operation complete.

SQL>load into tab2 select * from tab1;

UTIL_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Task: LOAD Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: CLEANUP Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: CLEANUP Status: Ended Object: TRAFODION.SCHEMA1.TAB2
Task: DISABLE INDEXE Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: DISABLE INDEXE Status: Ended Object: TRAFODION.SCHEMA1.TAB2
Task: PREPARATION Status: Started Object: TRAFODION.SCHEMA1.TAB2
       Rows Processed: 2
Task: PREPARATION Status: Ended ET: 00:00:01.047
Task: COMPLETION Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: COMPLETION Status: Ended ET: 00:00:00.764
Task: POPULATE INDEX Status: Started Object: TRAFODION.SCHEMA1.TAB2
Task: POPULATE INDEX Status: Ended ET: 00:00:01.784

--- SQL operation complete.

SQL>unload with purgedata from target into '/bulkload/4security' select * from tab2;

*** ERROR[4082] Object TRAFODION.SCHEMA1.DUMMY does not exist or is inaccessible.

[Roberta Marton (roberta-marton)]

This problem occurs during the generator phase when privileges are being
checked. When an unload statement is parsed, the parser creates the
ExeUtilHBaseBulkUnload class and set the table name to DUMMY. When the
privilege checks are later performed, the DUMMY table is checked which does not
exist.

The fix moves authorization checks from the generation phase into the binder.
A bindNode method was added to the bulk unload code to verify privileges. The
bindNode code, first checks to see if the user has the MANAGE_LOAD privilege.
If so, no additional checks are required. If not, it grabs the query expression
attached the the ExeUtilHBaseBulkUnLoad class and binds it. Binding the query
expression calls checkPrivileges and reports any violations.

This change requires that the query expression created during parsing be stored
in a new class member.

[Paul Low (paul-low-x)]

verified on 0417 build