trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rmar...@apache.org
Subject [2/4] trafodion git commit: TRAFODION-3046: Privilege support for native HBase tables
Date Fri, 11 May 2018 18:33:12 GMT
TRAFODION-3046: Privilege support for native HBase tables

-- Grants and revokes against native HBase tables are enforced similar to hive
-- Privilege checking added when creating and dropping native HBase tables
-- Removing dependent Trafodion metadata when native HBase tables are dropped
-- Added regression test (privs2/TEST146)
-- Reorg - split PrivMgrComponents into 2 files:
      PrivMgrComponents and PrivMgrUserPrivs

An hbase table can be referenced using one of the following types:
  "_CELL_" - references cell data
  "_ROW_"  - references row data
  "_MAP_"  - references data defined by the mapped (external) table

Privileges are granted against each of these types, so if you:
  select * from hbase."_CELL_".hbase1;
You must have the select privileges on this table
For example:  "grant select on hbase."_CELL_".hbase1 to user1"

Likewise for other hbase types, grants are required to gain accessibility
  grant select on hbase."_MAP_".hbase1 to user1
  grant select on hbase."_ROW_".hbase1 to role1


Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/b178915d
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/b178915d
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/b178915d

Branch: refs/heads/master
Commit: b178915d214d6bbdd5f7de49900f61a26abf652b
Parents: dd051ea
Author: Roberta Marton <roberta.marton@apache.org>
Authored: Thu May 10 15:15:50 2018 +0000
Committer: Roberta Marton <roberta.marton@apache.org>
Committed: Thu May 10 15:15:50 2018 +0000

----------------------------------------------------------------------
 core/sql/nskgmake/sqlcomp/Makefile              |    1 +
 core/sql/optimizer/NATable.cpp                  |   15 +-
 core/sql/regress/privs1/EXPECTED137             |    7 -
 core/sql/regress/privs1/TEST137                 |    3 -
 core/sql/regress/privs2/EXPECTED146             | 1656 ++++++++++++++++++
 core/sql/regress/privs2/FILTER146               |   38 +
 core/sql/regress/privs2/TEST146                 |  248 +++
 core/sql/regress/seabase/EXPECTED022            |  124 +-
 core/sql/regress/seabase/EXPECTED026            |   25 +-
 core/sql/regress/seabase/TEST022                |   10 +
 core/sql/regress/seabase/TEST026                |    3 +-
 core/sql/regress/seabase/TEST026_drop_hbase     |   26 +
 core/sql/sqlcomp/CmpDDLCatErrorCodes.h          |    2 +-
 core/sql/sqlcomp/CmpDescribe.cpp                |   26 +-
 core/sql/sqlcomp/CmpSeabaseDDL.h                |   10 +-
 core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp        |    4 +-
 core/sql/sqlcomp/CmpSeabaseDDLtable.cpp         |  132 +-
 core/sql/sqlcomp/PrivMgr.cpp                    |    9 +
 core/sql/sqlcomp/PrivMgr.h                      |    4 +
 core/sql/sqlcomp/PrivMgrCommands.cpp            |  160 +-
 core/sql/sqlcomp/PrivMgrCommands.h              |  360 +---
 core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp |    6 +
 core/sql/sqlcomp/PrivMgrComponentPrivileges.h   |    4 +-
 core/sql/sqlcomp/PrivMgrUserPrivs.cpp           |  208 +++
 core/sql/sqlcomp/PrivMgrUserPrivs.h             |  400 +++++
 core/sql/ustat/hs_globals.cpp                   |    5 +-
 26 files changed, 2876 insertions(+), 610 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/nskgmake/sqlcomp/Makefile
----------------------------------------------------------------------
diff --git a/core/sql/nskgmake/sqlcomp/Makefile b/core/sql/nskgmake/sqlcomp/Makefile
index c579ea6..0205ebd 100755
--- a/core/sql/nskgmake/sqlcomp/Makefile
+++ b/core/sql/nskgmake/sqlcomp/Makefile
@@ -35,6 +35,7 @@ CPPSRC := CmpDescribe.cpp \
 	CmpSeabaseDDLview.cpp \
 	CmpSeabaseDDLcommentOn.cpp \
 	PrivMgr.cpp \
+        PrivMgrUserPrivs.cpp \
         PrivMgrCommands.cpp \
         PrivMgrDesc.cpp \
         PrivMgrComponents.cpp \

http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/optimizer/NATable.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NATable.cpp b/core/sql/optimizer/NATable.cpp
index 6a959ec..327fcd7 100644
--- a/core/sql/optimizer/NATable.cpp
+++ b/core/sql/optimizer/NATable.cpp
@@ -5081,7 +5081,6 @@ NABoolean NATable::fetchObjectUIDForNativeTable(const CorrName& corrName,
        setHbaseDataFormatString(TRUE);
        break;
      }
-
    if (table_desc->tableDesc()->isInMemoryObject())
      {
        setInMemoryObjectDefn( TRUE );
@@ -6771,10 +6770,11 @@ void NATable::getPrivileges(TrafDesc * priv_desc)
 
   // If current user is root, object owner, or this is a volatile table
   // automatically have owner default privileges.
- if ((!isSeabaseTable() && !isHiveTable()) ||
-       !CmpCommon::context()->isAuthorizationEnabled() ||
-       isVolatileTable() ||
-       (ComUser::isRootUserID() && !isHiveTable()) )
+ if (!CmpCommon::context()->isAuthorizationEnabled() ||
+      isVolatileTable() ||
+      (ComUser::isRootUserID() && 
+        (!isHiveTable() && !isHbaseCellTable() && 
+         !isHbaseRowTable() && !isHbaseMapTable()) ))
   {
     privInfo_ = new(heap_) PrivMgrUserPrivs;
     privInfo_->setOwnerDefaultPrivs();
@@ -6794,7 +6794,8 @@ void NATable::getPrivileges(TrafDesc * priv_desc)
   ComSecurityKeySet secKeyVec(heap_);
   if (priv_desc == NULL)
   {
-    if (isHiveTable())
+    if (isHiveTable() || isHbaseCellTable() ||
+        isHbaseRowTable() || isHbaseMapTable())
       readPrivileges();
     else
       privInfo_ = NULL;
@@ -9149,6 +9150,8 @@ NATableDB::free_entries_with_QI_key(Int32 numKeys, SQL_QIKEY* qiKeyArray)
     NABoolean toRemove = FALSE;
     if ((currTable->isSeabaseTable()) ||
         (currTable->isHiveTable()) ||
+        (currTable->isHbaseCellTable()) ||
+        (currTable->isHbaseRowTable()) ||
         (currTable->hasExternalTable()))
       toRemove = TRUE;
     if (! toRemove)

http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/regress/privs1/EXPECTED137
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED137 b/core/sql/regress/privs1/EXPECTED137
index 811fbb2..208beb0 100755
--- a/core/sql/regress/privs1/EXPECTED137
+++ b/core/sql/regress/privs1/EXPECTED137
@@ -1,8 +1,4 @@
 >>obey TEST137(set_up);
->>grant component privilege "SHOW" on sql_operations to "PUBLIC";
-
---- SQL operation complete.
->>
 >>prepare get_roles from
 +>select substring (auth_db_name,1,20) as role_name
 +>from "_MD_".AUTHS
@@ -1989,9 +1985,6 @@ drop component privilege lib_view_benefactors cascade;
 *** ERROR[1008] Authorization identifier LIB_ROLE_TEST does not exist.
 
 --- SQL operation failed with errors.
->>revoke component privilege "SHOW" on sql_operations from "PUBLIC";
-
---- SQL operation complete.
 >>
 >>revoke role lib_role_test from sql_user5;
 

http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/regress/privs1/TEST137
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST137 b/core/sql/regress/privs1/TEST137
index 125ece3..f192d4e 100755
--- a/core/sql/regress/privs1/TEST137
+++ b/core/sql/regress/privs1/TEST137
@@ -110,7 +110,6 @@ revoke role library_ckout_clerks from sql_user3, sql_user4, sql_user5;
 drop role library_ckout_clerks;
 
 revoke component privilege lib_view_checkouts on library_books from lib_role_test;
-revoke component privilege "SHOW" on sql_operations from "PUBLIC";
 
 revoke role lib_role_test from sql_user5;
 revoke role db__rootrole from sql_user5;
@@ -124,8 +123,6 @@ revoke select on "_PRIVMGR_MD_".component_privileges from md_access;
 drop role md_access;
 
 ?section set_up
-grant component privilege "SHOW" on sql_operations to "PUBLIC";
-
 prepare get_roles from
 select substring (auth_db_name,1,20) as role_name
 from "_MD_".AUTHS

http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/regress/privs2/EXPECTED146
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED146 b/core/sql/regress/privs2/EXPECTED146
new file mode 100644
index 0000000..2baa2f8
--- /dev/null
+++ b/core/sql/regress/privs2/EXPECTED146
@@ -0,0 +1,1656 @@
+>>obey TEST146(create_db);
+>>-- =================================================================
+>>-- create native hbase table and roles
+>>-- =================================================================
+>>
+>>-- create hbase table 
+>>create hbase table t146t1 ( column family 'cf');
+
+--- SQL operation complete.
+>>insert into hbase."_CELL_".t146t1 values ('1', 'cf', 'team_number', -1, '1');
+
+--- 1 row(s) inserted.
+>>insert into hbase."_CELL_".t146t1 values ('1', 'cf', 'visitor_team', -1, '2');
+
+--- 1 row(s) inserted.
+>>insert into hbase."_CELL_".t146t1 values ('1', 'cf', 'game_location', -1, 'California');
+
+--- 1 row(s) inserted.
+>>insert into hbase."_CELL_".t146t1 values ('2', 'cf', 'team_number', -1, '1');
+
+--- 1 row(s) inserted.
+>>insert into hbase."_CELL_".t146t1 values ('2', 'cf', 'visitor_team', -1, '3');
+
+--- 1 row(s) inserted.
+>>insert into hbase."_CELL_".t146t1 values ('2', 'cf', 'game_location', -1, 'Oklahoma');
+
+--- 1 row(s) inserted.
+>>select count(*) from hbase."_CELL_".t146t1;
+
+(EXPR)              
+--------------------
+
+                   6
+
+--- 1 row(s) selected.
+>>select count(*) from hbase."_ROW_".t146t1;
+
+(EXPR)              
+--------------------
+
+                   2
+
+--- 1 row(s) selected.
+>>
+>>create external table t146t1 
++>  (row_id varchar(20) not null primary key, 
++>   row_value varchar (1000))
++> attribute default column family 'cf'
++> map to hbase table t146t1;
+
+--- SQL operation complete.
+>>
+>>-- These operations are not allowed on hbase tables
+>>update hbase."_CELL_".t146t1 set col_value = '4' where row_id = '2' and col_name = 'visitor_team';
+
+*** ERROR[1425] This operation could not be performed on HBASE."_CELL_".T146T1. Reason: Cannot update an hbase table in CELL format. Use ROW format for this operation.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>delete from hbase."_CELL_".t146t1 where row_id = '3';
+
+*** ERROR[1425] This operation could not be performed on HBASE."_CELL_".T146T1. Reason: Cannot delete from an hbase table in CELL format. Use ROW format for this operation.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>showddl hbase."_CELL_".t146t1;
+
+/*
+CREATE TABLE HBASE."_CELL_".T146T1
+  (
+    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_VALUE                        VARCHAR(1000) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (ROW_ID ASC)
+  )
+;
+*/
+
+/* HBase DDL */
+CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1')
+
+REGISTER /*INTERNAL*/ HBASE TABLE T146T1;
+/* ObjectUID = 2009891765934276385 */
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_CELL_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl hbase."_ROW_".t146t1;
+
+/*
+CREATE TABLE HBASE."_ROW_".T146T1
+  (
+    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COLUMN_DETAILS                   VARCHAR(10000) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (ROW_ID ASC)
+  )
+;
+*/
+
+/* HBase DDL */
+CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1')
+
+REGISTER /*INTERNAL*/ HBASE TABLE T146T1;
+/* ObjectUID = 2009891765934276410 */
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_ROW_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl hbase."_MAP_".t146t1;
+
+CREATE EXTERNAL TABLE T146T1
+  (
+    "cf".ROW_ID                      VARCHAR(20) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , "cf".ROW_VALUE                   VARCHAR(1000) CHARACTER SET ISO88591
+      COLLATE DEFAULT DEFAULT NULL
+  , PRIMARY KEY NOT SERIALIZED (ROW_ID ASC)
+  )
+ ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
+  MAP TO HBASE TABLE T146T1 DATA FORMAT VARCHAR
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_MAP_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>get hbase registered tables in catalog trafodion, match '%T146%';
+
+HBase Registered Tables in Catalog TRAFODION
+============================================
+
+"_CELL_".T146T1
+"_ROW_".T146T1
+
+--- SQL operation complete.
+>>
+>>create role t146_role1;
+
+--- SQL operation complete.
+>>grant role t146_role1 to sql_user1;
+
+--- SQL operation complete.
+>>grant role db__hbaserole to sql_user5;
+
+--- SQL operation complete.
+>>
+>>revoke component privilege "SHOW" on sql_operations from "PUBLIC";
+
+--- SQL operation complete.
+>>get privileges on component sql_operations for "PUBLIC";
+
+Privilege information on Component SQL_OPERATIONS for PUBLIC
+============================================================
+
+CREATE_SCHEMA
+
+--- SQL operation complete.
+>>
+>>obey TEST146(test_grants);
+>>-- =================================================================
+>>-- this set of tests run basic grant tests for native Hive tables
+>>-- =================================================================
+>>
+>>-- grant a privilege where user has no grant privilege
+>>sh sqlci -i "TEST146(perform_grants)" -u sql_user3;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>grant all on hbase."_CELL_".t146t1 to t146_role1 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>grant select, update, insert, delete on hbase."_ROW_".t146t1 to t146_role1 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>grant select(row_id, col_value) on hbase."_CELL_".t146t1 to sql_user2 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>grant select on hbase."_CELL_".t146t1 to sql_user3 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>grant select (row_id) on hbase."_ROW_".t146t1 to sql_user2 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>grant select on hbase."_ROW_".t146t1 to sql_user3 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>grant select on t146t1 to sql_user3 by db__hbaserole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl hbase."_CELL_".t146t1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl hbase."_ROW_".t146t1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl hbase."_MAP_".t146t1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- grant privileges by the hbase admin user
+>>sh sqlci -i "TEST146(perform_grants)" -u sql_user5;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>grant all on hbase."_CELL_".t146t1 to t146_role1 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select, update, insert, delete on hbase."_ROW_".t146t1 to t146_role1 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select(row_id, col_value) on hbase."_CELL_".t146t1 to sql_user2 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on hbase."_CELL_".t146t1 to sql_user3 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select (row_id) on hbase."_ROW_".t146t1 to sql_user2 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on hbase."_ROW_".t146t1 to sql_user3 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on t146t1 to sql_user3 by db__hbaserole;
+
+--- SQL operation complete.
+>>showddl hbase."_CELL_".t146t1;
+
+/*
+CREATE TABLE HBASE."_CELL_".T146T1
+  (
+    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COL_VALUE                        VARCHAR(1000) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (ROW_ID ASC)
+  )
+;
+*/
+
+/* HBase DDL */
+CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1')
+
+REGISTER /*INTERNAL*/ HBASE TABLE T146T1;
+/* ObjectUID = 2009891765934276385 */
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_CELL_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION;
+  GRANT SELECT ON HBASE."_CELL_".T146T1 TO SQL_USER3 GRANTED BY DB__HBASEROLE;
+  GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_CELL_".T146T1 TO
+  T146_ROLE1 GRANTED BY DB__HBASEROLE;
+GRANT SELECT(ROW_ID, COL_VALUE) ON
+  HBASE."_CELL_".T146T1 TO SQL_USER2 GRANTED BY DB__HBASEROLE;
+
+--- SQL operation complete.
+>>showddl hbase."_ROW_".t146t1;
+
+/*
+CREATE TABLE HBASE."_ROW_".T146T1
+  (
+    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , COLUMN_DETAILS                   VARCHAR(10000) CHARACTER SET ISO88591
+      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (ROW_ID ASC)
+  )
+;
+*/
+
+/* HBase DDL */
+CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1')
+
+REGISTER /*INTERNAL*/ HBASE TABLE T146T1;
+/* ObjectUID = 2009891765934276410 */
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_ROW_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION;
+  GRANT SELECT ON HBASE."_ROW_".T146T1 TO SQL_USER3 GRANTED BY DB__HBASEROLE;
+  GRANT SELECT, INSERT, DELETE, UPDATE ON HBASE."_ROW_".T146T1 TO T146_ROLE1
+  GRANTED BY DB__HBASEROLE;
+GRANT SELECT(ROW_ID) ON HBASE."_ROW_".T146T1 TO
+  SQL_USER2 GRANTED BY DB__HBASEROLE;
+
+--- SQL operation complete.
+>>showddl hbase."_MAP_".t146t1;
+
+CREATE EXTERNAL TABLE T146T1
+  (
+    "cf".ROW_ID                      VARCHAR(20) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , "cf".ROW_VALUE                   VARCHAR(1000) CHARACTER SET ISO88591
+      COLLATE DEFAULT DEFAULT NULL
+  , PRIMARY KEY NOT SERIALIZED (ROW_ID ASC)
+  )
+ ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
+  MAP TO HBASE TABLE T146T1 DATA FORMAT VARCHAR
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_MAP_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION;
+  GRANT SELECT ON HBASE."_MAP_".T146T1 TO SQL_USER3;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user1 can select and update table via t146_role1, no select from map table
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user1;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER1                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+ROW_ID  COL_FAMILY                                                                                            COL_NAME              COL_VALUE
+------  ----------------------------------------------------------------------------------------------------  --------------------  --------------------
+
+1       cf                                                                                                    game_location         California          
+1       cf                                                                                                    visitor_team          2                   
+1       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    game_location         Oklahoma            
+2       cf                                                                                                    visitor_team          3                   
+
+--- 6 row(s) selected.
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+--- SQL command prepared.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>select row_id from t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_HB_MAP_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+ROW_ID  COLUMN_DETAILS
+------  --------------
+
+1       cf:game_      
+2       cf:game_      
+
+--- 2 row(s) selected.
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+ROW_ID
+------
+
+1     
+2     
+
+--- 2 row(s) selected.
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+--- 3 row(s) inserted.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       1                                       
+1       2                                       
+2       3                                       
+2       1                                       
+2       Oklahoma                                
+3       Ohio                                    
+3       1                                       
+3       3                                       
+
+--- 9 row(s) selected.
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+--- 1 row(s) updated.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       1                                       
+1       2                                       
+2       3                                       
+2       1                                       
+2       Oklahoma                                
+3       1                                       
+3       2018-04-30                              
+3       Ohio                                    
+3       3                                       
+
+--- 10 row(s) selected.
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+--- 1 row(s) deleted.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user2 can select subset of columns
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user2;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER2                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_CELL_".T146T1(columns: COL_FAMILY, COL_NAME).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+--- SQL command prepared.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>select row_id from t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_HB_MAP_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1(columns: COLUMN_DETAILS).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+ROW_ID
+------
+
+1     
+2     
+
+--- 2 row(s) selected.
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1(columns: COLUMN_DETAILS).
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+*** ERROR[4481] The user does not have DELETE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user3 can select all 
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user3;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+ROW_ID  COL_FAMILY                                                                                            COL_NAME              COL_VALUE
+------  ----------------------------------------------------------------------------------------------------  --------------------  --------------------
+
+1       cf                                                                                                    game_location         California          
+1       cf                                                                                                    visitor_team          2                   
+1       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    game_location         Oklahoma            
+2       cf                                                                                                    visitor_team          3                   
+
+--- 6 row(s) selected.
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+--- SQL command prepared.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>select row_id from t146t1;
+
+--- 0 row(s) selected.
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+ROW_ID  COLUMN_DETAILS
+------  --------------
+
+1       cf:game_      
+2       cf:game_      
+
+--- 2 row(s) selected.
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+ROW_ID
+------
+
+1     
+2     
+
+--- 2 row(s) selected.
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+*** ERROR[4481] The user does not have DELETE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user4 has no privileges
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user4;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER4                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>select row_id from t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_HB_MAP_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[4481] The user does not have DELETE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user5 has all privileges through role db__hbaserole
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user5;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+ROW_ID  COL_FAMILY                                                                                            COL_NAME              COL_VALUE
+------  ----------------------------------------------------------------------------------------------------  --------------------  --------------------
+
+1       cf                                                                                                    game_location         California          
+1       cf                                                                                                    visitor_team          2                   
+1       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    game_location         Oklahoma            
+2       cf                                                                                                    visitor_team          3                   
+
+--- 6 row(s) selected.
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+--- SQL command prepared.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>select row_id from t146t1;
+
+--- 0 row(s) selected.
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+ROW_ID  COLUMN_DETAILS
+------  --------------
+
+1       cf:game_      
+2       cf:game_      
+
+--- 2 row(s) selected.
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+ROW_ID
+------
+
+1     
+2     
+
+--- 2 row(s) selected.
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+--- 3 row(s) inserted.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       1                                       
+1       2                                       
+2       3                                       
+2       1                                       
+2       Oklahoma                                
+3       Ohio                                    
+3       1                                       
+3       3                                       
+
+--- 9 row(s) selected.
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+--- 1 row(s) updated.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       1                                       
+1       2                                       
+2       3                                       
+2       1                                       
+2       Oklahoma                                
+3       1                                       
+3       2018-04-30                              
+3       Ohio                                    
+3       3                                       
+
+--- 10 row(s) selected.
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+--- 1 row(s) deleted.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>obey TEST146(test_revokes);
+>>-- ============================================================================
+>>-- verify that revoking privileges work as expected on native Hive tables
+>>-- ============================================================================
+>>
+>>-- revoke update from t146_role1
+>>revoke update on hbase."_ROW_".t146t1 from t146_role1 by db__hbaserole;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user1;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER1                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+ROW_ID  COL_FAMILY                                                                                            COL_NAME              COL_VALUE
+------  ----------------------------------------------------------------------------------------------------  --------------------  --------------------
+
+1       cf                                                                                                    game_location         California          
+1       cf                                                                                                    visitor_team          2                   
+1       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    team_number           1                   
+2       cf                                                                                                    game_location         Oklahoma            
+2       cf                                                                                                    visitor_team          3                   
+
+--- 6 row(s) selected.
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+--- SQL command prepared.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>select row_id from t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_HB_MAP_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+ROW_ID  COLUMN_DETAILS
+------  --------------
+
+1       cf:game_      
+2       cf:game_      
+
+--- 2 row(s) selected.
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+ROW_ID
+------
+
+1     
+2     
+
+--- 2 row(s) selected.
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+--- 3 row(s) inserted.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       1                                       
+1       2                                       
+2       3                                       
+2       1                                       
+2       Oklahoma                                
+3       Ohio                                    
+3       1                                       
+3       3                                       
+
+--- 9 row(s) selected.
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       1                                       
+1       2                                       
+2       3                                       
+2       1                                       
+2       Oklahoma                                
+3       Ohio                                    
+3       1                                       
+3       3                                       
+
+--- 9 row(s) selected.
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+--- 1 row(s) deleted.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- revoke role from sql_user1, no longer has privilege
+>>revoke role t146_role1 from sql_user1;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user1;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER1                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>select row_id from t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_HB_MAP_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[4481] The user does not have DELETE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+*** ERROR[15017] Statement S1 was not found.
+
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>--revoke column privs from sql_user2
+>>revoke select (row_id) on hbase."_ROW_".t146t1 from sql_user2 by db__hbaserole;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST146(perform_ops)" -u sql_user2;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER2                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>-- contains most columns
+>>select substring (row_id,1,5) as row_id, 
++>       col_family,
++>       cast(col_name as char(20) character set iso88591) as col_name,
++>       cast(col_value as char(20) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_CELL_".T146T1(columns: COL_FAMILY, COL_NAME).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s1 from
++>select substring (row_id,1,4) as row_id, 
++>       cast (col_value as char (40) character set iso88591) as col_value
++>  from hbase."_CELL_".t146t1 order by 1;
+
+--- SQL command prepared.
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>select row_id from t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_HB_MAP_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- contains columns associated with granted column level privileges
+>>select substring(row_id,1,4) as row_id, 
++>       substring (column_details,3,8) as column_details
++>  from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>insert into hbase."_CELL_".t146t1 values 
++>  ('3', 'cf', 'game_location', -1, 'Ohio'), 
++>  ('3', 'cf', 'visitor_team', -1, '3'),
++>  ('3', 'cf', 'team_number', -1, '1');
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view HBASE."_CELL_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
++>   where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>delete from hbase."_ROW_".t146t1 where row_id = '3';
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[4481] The user does not have DELETE privilege on table or view HBASE."_ROW_".T146T1.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute s1;
+
+ROW_ID  COL_VALUE                               
+------  ----------------------------------------
+
+1       California                              
+1       2                                       
+1       1                                       
+2       1                                       
+2       Oklahoma                                
+2       3                                       
+
+--- 6 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>obey TEST146(test_ddl);
+>>-- ============================================================================
+>>-- verify that DDL operations work correctly
+>>-- ============================================================================
+>>
+>>--  db__root can create hbase tables
+>>obey TEST146(perform_ddl);
+>>-- ============================================================================
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>log LOG146;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+DB__ROOT                                                                                                                         
+
+--- 1 row(s) selected.
+>>
+>>create hbase table t146t2 ( column family 'cf');
+
+--- SQL operation complete.
+>>
+>>create external table t146t2
++>  (row_id varchar(20) not null primary key,
++>   row_value varchar (1000))
++> attribute default column family 'cf'
++> map to hbase table t146t2;
+
+--- SQL operation complete.
+>>
+>>grant select on hbase."_CELL_".t146t2 to sql_user1 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on hbase."_ROW_".t146t2 to sql_user1 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on hbase."_MAP_".t146t2 to sql_user1 by db__hbaserole;
+
+--- SQL operation complete.
+>>get privileges for role db__hbaserole, match '%146%';
+
+Privileges for Role DB__HBASEROLE
+=================================
+
+SIDU-R-    HBASE._CELL_.T146T1
+SIDU-R-    HBASE._CELL_.T146T2
+SIDU-R-    HBASE._ROW_.T146T1
+SIDU-R-    HBASE._ROW_.T146T2
+SIDU-R-    TRAFODION._HB_MAP_.T146T1
+SIDU-R-    TRAFODION._HB_MAP_.T146T2
+
+--- SQL operation complete.
+>>
+>>drop hbase table t146t2;
+
+--- SQL operation complete.
+>>get privileges for role db__hbaserole, match '%146%';
+
+Privileges for Role DB__HBASEROLE
+=================================
+
+SIDU-R-    HBASE._CELL_.T146T1
+SIDU-R-    HBASE._ROW_.T146T1
+SIDU-R-    TRAFODION._HB_MAP_.T146T1
+
+--- SQL operation complete.
+>>
+>>
+>>-- sql_user3 has no privleges, cannot create tables
+>>sh sqlci -i "TEST146(perform_ddl)" -u sql_user3;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>create hbase table t146t2 ( column family 'cf');
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>create external table t146t2
++>  (row_id varchar(20) not null primary key,
++>   row_value varchar (1000))
++> attribute default column family 'cf'
++> map to hbase table t146t2;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+*** ERROR[4260] HBase table(T146T2) to be mapped as an external table does not exist.
+
+--- SQL operation failed with errors.
+>>
+>>grant select on hbase."_CELL_".t146t2 to sql_user1 by db__hbaserole;
+
+*** ERROR[1389] Object T146T2 does not exist in Trafodion.
+
+*** ERROR[4082] Object HBASE."_CELL_".T146T2 does not exist or is inaccessible.
+
+--- SQL operation failed with errors.
+>>grant select on hbase."_ROW_".t146t2 to sql_user1 by db__hbaserole;
+
+*** ERROR[1389] Object T146T2 does not exist in Trafodion.
+
+*** ERROR[4082] Object HBASE."_ROW_".T146T2 does not exist or is inaccessible.
+
+--- SQL operation failed with errors.
+>>grant select on hbase."_MAP_".t146t2 to sql_user1 by db__hbaserole;
+
+*** ERROR[4082] Object TRAFODION."_HB_MAP_".T146T2 does not exist or is inaccessible.
+
+*** ERROR[4082] Object TRAFODION."_HB_MAP_".T146T2 does not exist or is inaccessible.
+
+--- SQL operation failed with errors.
+>>get privileges for role db__hbaserole, match '%146%';
+
+--- SQL operation complete.
+>>
+>>drop hbase table t146t2;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get privileges for role db__hbaserole, match '%146%';
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- sql_user5 that has been granted db__hbaserole can create hbase tables
+>>grant role db__hbaserole to sql_user5;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST146(perform_ddl)" -u sql_user5;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5                                                                                                                        
+
+--- 1 row(s) selected.
+>>
+>>create hbase table t146t2 ( column family 'cf');
+
+--- SQL operation complete.
+>>
+>>create external table t146t2
++>  (row_id varchar(20) not null primary key,
++>   row_value varchar (1000))
++> attribute default column family 'cf'
++> map to hbase table t146t2;
+
+--- SQL operation complete.
+>>
+>>grant select on hbase."_CELL_".t146t2 to sql_user1 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on hbase."_ROW_".t146t2 to sql_user1 by db__hbaserole;
+
+--- SQL operation complete.
+>>grant select on hbase."_MAP_".t146t2 to sql_user1 by db__hbaserole;
+
+--- SQL operation complete.
+>>get privileges for role db__hbaserole, match '%146%';
+
+Privileges for Role DB__HBASEROLE
+=================================
+
+SIDU-R-    HBASE._CELL_.T146T1
+SIDU-R-    HBASE._CELL_.T146T2
+SIDU-R-    HBASE._ROW_.T146T1
+SIDU-R-    HBASE._ROW_.T146T2
+SIDU-R-    TRAFODION._HB_MAP_.T146T1
+SIDU-R-    TRAFODION._HB_MAP_.T146T2
+
+--- SQL operation complete.
+>>
+>>drop hbase table t146t2;
+
+--- SQL operation complete.
+>>get privileges for role db__hbaserole, match '%146%';
+
+Privileges for Role DB__HBASEROLE
+=================================
+
+SIDU-R-    HBASE._CELL_.T146T1
+SIDU-R-    HBASE._ROW_.T146T1
+SIDU-R-    TRAFODION._HB_MAP_.T146T1
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>revoke role db__hbaserole from sql_user5;
+
+--- SQL operation complete.
+>>
+>>-- make sure correct result when repeating tests
+>>create hbase table t146t2 ( column family 'cf');
+
+--- SQL operation complete.
+>>create hbase table t146t2 ( column family 'cf');
+
+*** WARNING[1055] Object T146T2 already exists.
+
+--- SQL operation complete.
+>>
+>>drop hbase table t146t2;
+
+--- SQL operation complete.
+>>drop hbase table t146t2;
+
+*** WARNING[1004] Object T146T2 does not exist or object type is invalid for the current operation.
+
+--- SQL operation complete.
+>>
+>>log;

http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/regress/privs2/FILTER146
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/FILTER146 b/core/sql/regress/privs2/FILTER146
new file mode 100755
index 0000000..9f08ff1
--- /dev/null
+++ b/core/sql/regress/privs2/FILTER146
@@ -0,0 +1,38 @@
+#! /bin/sh
+# @@@ START COPYRIGHT @@@
+#
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+# @@@ END COPYRIGHT @@@
+
+# Like the pattern-masking of QACOMP, here we filter out such stuff as
+# timestamps and generated identifiers.  Called by the runregr script
+# before doing diff.
+
+fil=$1
+if [ "$fil" = "" ]; then
+  echo "Usage: $0 filename"
+  exit 1
+fi
+
+# filters for showstats output
+#Table ID: 56910649950845593
+#2071939932      8           8           8 SYSKEY
+sed " 
+s/\/\* ObjectUID = *[0-9]*/ObjectUID = <UID removed>/g
+" $fil

http://git-wip-us.apache.org/repos/asf/trafodion/blob/b178915d/core/sql/regress/privs2/TEST146
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/TEST146 b/core/sql/regress/privs2/TEST146
new file mode 100755
index 0000000..9f3f6d0
--- /dev/null
+++ b/core/sql/regress/privs2/TEST146
@@ -0,0 +1,248 @@
+-- ============================================================================
+-- TEST146 - tests grant and revoke privileges on native HBase tables
+--
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+--
+-- ============================================================================
+
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+obey TEST146(clean_up);
+log LOG146 clear;
+obey TEST146(create_db);
+obey TEST146(test_grants);
+obey TEST146(test_revokes);
+obey TEST146(test_ddl);
+log;
+
+obey TEST146(clean_up);
+
+exit;
+
+?section clean_up
+-- =================================================================
+-- remove objects and update standard privileges
+-- =================================================================
+
+revoke all on hbase."_CELL_".t146t1 from t146_role1 by db__hbaserole;
+revoke all on hbase."_ROW_".t146t1 from t146_role1 by db__hbaserole;
+revoke all on hbase."_CELL_".t146t1 from sql_user1 by db__hbaserole;
+revoke all on hbase."_ROW_".t146t1 from sql_user1 by db__hbaserole;
+revoke select (row_id, col_value) on hbase."_CELL_".t146t1 from sql_user2 by db__hbaserole;  
+revoke select on hbase."_CELL_".t146t1 from sql_user3 by db__hbaserole;
+revoke select (row_id) on hbase."_ROW_".t146t1 from sql_user3 by db__hbaserole;
+revoke select on hbase."_ROW_".t146t1 from sql_user3 by db__hbaserole;
+revoke select on t146t1 from sql_user3 by db__hbaserole;
+revoke role t146_role1 from sql_user1;
+
+unregister hbase table if exists t146t1;
+drop external table if exists t146t1;
+
+drop hbase table t146t1;
+drop hbase table t146t2;
+
+drop role t146_role1;
+revoke role db__hbaserole from sql_user5;
+
+grant component privilege "SHOW" on sql_operations to "PUBLIC";
+
+?section create_db
+-- =================================================================
+-- create native hbase table and roles
+-- =================================================================
+
+-- create hbase table 
+create hbase table t146t1 ( column family 'cf');
+insert into hbase."_CELL_".t146t1 values ('1', 'cf', 'team_number', -1, '1');
+insert into hbase."_CELL_".t146t1 values ('1', 'cf', 'visitor_team', -1, '2');
+insert into hbase."_CELL_".t146t1 values ('1', 'cf', 'game_location', -1, 'California');
+insert into hbase."_CELL_".t146t1 values ('2', 'cf', 'team_number', -1, '1');
+insert into hbase."_CELL_".t146t1 values ('2', 'cf', 'visitor_team', -1, '3');
+insert into hbase."_CELL_".t146t1 values ('2', 'cf', 'game_location', -1, 'Oklahoma');
+select count(*) from hbase."_CELL_".t146t1;
+select count(*) from hbase."_ROW_".t146t1;
+
+create external table t146t1 
+  (row_id varchar(20) not null primary key, 
+   row_value varchar (1000))
+ attribute default column family 'cf'
+ map to hbase table t146t1;
+
+-- These operations are not allowed on hbase tables
+update hbase."_CELL_".t146t1 set col_value = '4' where row_id = '2' and col_name = 'visitor_team';
+delete from hbase."_CELL_".t146t1 where row_id = '3';
+
+showddl hbase."_CELL_".t146t1;
+showddl hbase."_ROW_".t146t1;
+showddl hbase."_MAP_".t146t1;
+get hbase registered tables in catalog trafodion, match '%T146%';
+
+create role t146_role1;
+grant role t146_role1 to sql_user1;
+grant role db__hbaserole to sql_user5;
+
+revoke component privilege "SHOW" on sql_operations from "PUBLIC";
+get privileges on component sql_operations for "PUBLIC";
+
+?section test_grants
+-- =================================================================
+-- this set of tests run basic grant tests for native Hive tables
+-- =================================================================
+
+-- grant a privilege where user has no grant privilege
+sh sqlci -i "TEST146(perform_grants)" -u sql_user3;
+
+-- grant privileges by the hbase admin user
+sh sqlci -i "TEST146(perform_grants)" -u sql_user5;
+
+-- user1 can select and update table via t146_role1, no select from map table
+sh sqlci -i "TEST146(perform_ops)" -u sql_user1;
+
+-- user2 can select subset of columns
+sh sqlci -i "TEST146(perform_ops)" -u sql_user2;
+
+-- user3 can select all 
+sh sqlci -i "TEST146(perform_ops)" -u sql_user3;
+
+-- user4 has no privileges
+sh sqlci -i "TEST146(perform_ops)" -u sql_user4;
+
+-- user5 has all privileges through role db__hbaserole
+sh sqlci -i "TEST146(perform_ops)" -u sql_user5;
+
+?section test_revokes
+-- ============================================================================
+-- verify that revoking privileges work as expected on native Hive tables
+-- ============================================================================
+
+-- revoke update from t146_role1
+revoke update on hbase."_ROW_".t146t1 from t146_role1 by db__hbaserole;
+sh sqlci -i "TEST146(perform_ops)" -u sql_user1;
+
+-- revoke role from sql_user1, no longer has privilege
+revoke role t146_role1 from sql_user1;
+sh sqlci -i "TEST146(perform_ops)" -u sql_user1;
+
+--revoke column privs from sql_user2
+revoke select (row_id) on hbase."_ROW_".t146t1 from sql_user2 by db__hbaserole;
+sh sqlci -i "TEST146(perform_ops)" -u sql_user2;
+
+?section test_ddl
+-- ============================================================================
+-- verify that DDL operations work correctly
+-- ============================================================================
+
+--  db__root can create hbase tables
+obey TEST146(perform_ddl);
+
+-- sql_user3 has no privleges, cannot create tables
+sh sqlci -i "TEST146(perform_ddl)" -u sql_user3;
+
+-- sql_user5 that has been granted db__hbaserole can create hbase tables
+grant role db__hbaserole to sql_user5;
+sh sqlci -i "TEST146(perform_ddl)" -u sql_user5;
+revoke role db__hbaserole from sql_user5;
+
+-- make sure correct result when repeating tests
+create hbase table t146t2 ( column family 'cf');
+create hbase table t146t2 ( column family 'cf');
+
+drop hbase table t146t2;
+drop hbase table t146t2;
+
+?section perform_ddl
+-- ============================================================================
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+log LOG146;
+values(user);
+
+create hbase table t146t2 ( column family 'cf');
+
+create external table t146t2
+  (row_id varchar(20) not null primary key,
+   row_value varchar (1000))
+ attribute default column family 'cf'
+ map to hbase table t146t2;
+
+grant select on hbase."_CELL_".t146t2 to sql_user1 by db__hbaserole;
+grant select on hbase."_ROW_".t146t2 to sql_user1 by db__hbaserole;
+grant select on hbase."_MAP_".t146t2 to sql_user1 by db__hbaserole;
+get privileges for role db__hbaserole, match '%146%';
+
+drop hbase table t146t2;
+get privileges for role db__hbaserole, match '%146%';
+
+?section perform_grants
+-- ============================================================================
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+log LOG146;
+values(user);
+
+grant all on hbase."_CELL_".t146t1 to t146_role1 by db__hbaserole;
+grant select, update, insert, delete on hbase."_ROW_".t146t1 to t146_role1 by db__hbaserole;
+grant select(row_id, col_value) on hbase."_CELL_".t146t1 to sql_user2 by db__hbaserole;
+grant select on hbase."_CELL_".t146t1 to sql_user3 by db__hbaserole;
+grant select (row_id) on hbase."_ROW_".t146t1 to sql_user2 by db__hbaserole;
+grant select on hbase."_ROW_".t146t1 to sql_user3 by db__hbaserole;
+grant select on t146t1 to sql_user3 by db__hbaserole;
+showddl hbase."_CELL_".t146t1;
+showddl hbase."_ROW_".t146t1;
+showddl hbase."_MAP_".t146t1;
+
+?section perform_ops
+-- ============================================================================
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+log LOG146;
+values (user);
+
+-- contains most columns
+select substring (row_id,1,5) as row_id, 
+       col_family,
+       cast(col_name as char(20) character set iso88591) as col_name,
+       cast(col_value as char(20) character set iso88591) as col_value
+  from hbase."_CELL_".t146t1 order by 1;
+prepare s1 from
+select substring (row_id,1,4) as row_id, 
+       cast (col_value as char (40) character set iso88591) as col_value
+  from hbase."_CELL_".t146t1 order by 1;
+execute s1;
+select row_id from t146t1;
+
+-- contains columns associated with granted column level privileges
+select substring(row_id,1,4) as row_id, 
+       substring (column_details,3,8) as column_details
+  from hbase."_ROW_".t146t1;
+select substring(row_id,1,4) as row_id from hbase."_ROW_".t146t1;
+
+insert into hbase."_CELL_".t146t1 values 
+  ('3', 'cf', 'game_location', -1, 'Ohio'), 
+  ('3', 'cf', 'visitor_team', -1, '3'),
+  ('3', 'cf', 'team_number', -1, '1');
+execute s1;
+
+update hbase."_ROW_".t146t1 set column_details = column_create ('cf:game_time', '2018-04-30')
+   where row_id = '3';
+execute s1;
+
+delete from hbase."_ROW_".t146t1 where row_id = '3';
+execute s1;
+


Mime
View raw message