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

["Gao, Rui-Xian (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15838931#comment-15838931 ] 

Gao, Rui-Xian commented on TRAFODION-2409:
------------------------------------------


double checked on R2.2.1 , we support column privileges for hive tables now. column privilege for select works well, for insert we may need warning or error at grant since insert data into specific column for hive table is not supported in trafodion.

create hive table
==========================================
create database qa_test4;
use qa_test4;
create external table ext_t1(c1 varchar(20), c2 int);

dbroot user -
===========================================
SQL>update statistics for table hive.qa_test4.ext_t1 on every column;

--- SQL operation complete.
SQL>grant select(c2) on hive.qa_test4.ext_t1 to qauser99;

--- SQL operation complete.

SQL>grant insert(c2) on hive.qa_test4.ext_t1 to qauser99; // grant successfully

--- SQL operation complete.

user qauser99 -
================================================================
SQL>cqd query_cache '0';

--- SQL operation complete.

SQL>cqd TRAF_RELOAD_NATABLE_CACHE 'on';

--- SQL operation complete.

SQL>select * from hive.qa_test4.ext_t1;

*** ERROR[4481] The user does not have SELECT privilege on table or view HIVE.QA_TEST4.EXT_T1(columns: C1). [2017-01-26 08:39:58]

SQL>select c1 from hive.qa_test4.ext_t1;

*** ERROR[4481] The user does not have SELECT privilege on table or view HIVE.QA_TEST4.EXT_T1(columns: C1). [2017-01-26 08:40:21]

SQL>select c2 from hive.qa_test4.ext_t1;

C2
-----------
          3
          1
          2

--- 3 row(s) selected.

SQL>insert into hive.qa_test4.ext_t1 values('qauser99',3); // insert into table successfully

--- 1 row(s) inserted.

SQL>insert into hive.qa_test4.ext_t1(c1) values('qauser99'); // insert specific will get ERROR[4223]

*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version. [2017-01-26 08:46:05]


As we don't support insert for single column, can we add warning/error for grant that column privilege is not granted ? Or user would think the grant worked but still can insert data into all columns.



> support privilege control(column privileges) for hive tables
> ------------------------------------------------------------
>
>                 Key: TRAFODION-2409
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2409
>             Project: Apache Trafodion
>          Issue Type: Improvement
>          Components: sql-security
>            Reporter: Gao, Rui-Xian
>            Assignee: Roberta Marton
>
> 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.



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