[TRAFODION-2409] support privilege control(column privileges) for hive tables

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: sql-security
• Assignee: Roberta Marton
• Reporter: Gao, Rui-Xian
• Created: 21/Dec/16 02:08
• Updated: 15/Feb/17 15:58
• Resolved: 15/Feb/17 15:58

Description

we need support column privileges for hive tables.

1. Currently, we have problem accessing hive native tables with users that is not trafodion –
1). create table from hive
2). connect with user1, select from hive table will get internal error
SQL>select * from hive.hive.mytest;

• • • ERROR[1001] An internal error occurred in module ../sqlcomp/PrivMgrPrivileges.cpp on line 4149. DETAILS(objectUID is 0 for get privileges command). [2016-12-20 12:31:55]
    • ERROR[1034] Unable to obtain privileges [2016-12-20 12:31:55]

2. after creating external table for hive table, we can grant/revoke on hive tables, but don't support column privileges, a user will have privilege on all columns though only granted privileges on one column.
1). create table from hive
2). do 'update statistics' for hive table from trafodion
3). grant column privilge on the hive table to a user
4). the user still have privileges on all columns

User trafodion—
**********************************************************************************************
>>grant select(a) on hive.hive.inttab1 to qauser1;

— SQL operation complete.

User qauser1 –
**********************************************************************************************
SQL>select * from hive.hive.inttab1; // qauser1 should not have select privilege on column b

— 0 row(s) selected.

SQL>insert into hive.hive.inttab1 values(1,1);

• • • ERROR[4481] The user does not have INSERT privilege on table or view HIVE.HIVE.INTTAB1. [2016-12-20 15:12:40]

User trafodion –
**********************************************************************************************
>>grant insert(a) on hive.hive.inttab1 to qauser1;

— SQL operation complete.

User qauser1 –
**********************************************************************************************
SQL>insert into hive.hive.inttab1 values(2,2); // qauser1 only have privilege to insert data into column a, but can insert data into all columns.

— 1 row(s) inserted.