Importing Hive authorization to Db2 Big SQL authorization

You can use the HCAT_SYNC_OBJECTS stored procedure with the IMPORT HDFS AUTHORIZATIONS option to import any existing permissions that are set in Hive to Db2 Big SQL. GRANT statements are issued to the same owner, group, and other roles in Db2 Big SQL based on the permissions that were set for the corresponding table and views in Hive.

On CDH, importing Hive authorizations is not supported.

For any permissions that are assigned to roles in Hive, an error will occur if that same role does not already exists in Big SQL.

The IMPORT HIVE AUTHORIZATIONS option imports authorizations in Hive as opposed to syncing them. So, if any authorizations are revoked in Hive after this operation, the changed permissions will not automatically propagate to Db2 Big SQL.

Note: To use the IMPORT HIVE AUTHORIZATIONS option, SQL Standard Based Authorization must be set as the authorization method in Hive. For details, see SQL Standard Based Hive Authorization. If SQL Standard Based Authorization is not in place, Hive authorizations will still be imported to Db2 Big SQL, but a warning message will be displayed after the HCAT_SYNC_OBJECTS procedure is called:
Attempting to Import Hive Authorization though SQLAuthorization is in disabled mode

Example 1: Importing Hive Role permissions

Suppose that you have a readonly role in Hive with SELECT privilege on table hivebig.items. In Hive:
> show grant role readonly;
+-----------+--------+------------+------------+
| database  | table  | ... | privilege  | ...  |
+-----------+--------+------------+------------+
| hivebig   | items  | ... | SELECT     | ...  |
+-----------+--------+------------+------------+
In order to import the role in Db2 Big SQL, first make sure the “readonly” role exists in Db2 Big SQL:
[bigsql] 1> create role READONLY;
Then run the import:
[bigsql] 1> CALL SYSHADOOP.HCAT_SYNC_OBJECTS('.*', '.*', 'a', 'SKIP', 'CONTINUE', 'IMPORT HIVE AUTHORIZATIONS');
+-----------+---------+------------------+------+--------+--------------------------------------------------------------------------------------+
| OBJSCHEMA | OBJNAME | OBJATTRIB        | TYPE | STATUS | DETAILS                                                                              |
+-----------+---------+------------------+------+--------+--------------------------------------------------------------------------------------+
| HIVEBIG   | ITEMS   | [NULL]           | T    | SKIP   | Exists                                                                      |
+-----------+---------+------------------+------+--------+--------------------------------------------------------------------------------------+
You can confirm that the role permissions (SELECT on HIVEBIG.ITEMS) were imported by running:
db2 => select * from SYSIBMADM.ROLE_TAB_PRIVS where ROLE = 'READONLY'

ROLE                  OWNER        TABLE_SCHEMA          TABLE_NAME        COLUMN_NAME     PRIVILEGE GRANTABLE
-----------------------------------------------------------------------------------------------------------------
READONLY         BIGSQL        HIVEBIG                       ITEMS                    BIGSQL                   SELECT    NO       

  1 record(s) selected.
Note: User role assignments are not imported. Thus, if you had the readonly user assigned to user newuser in Hive, that user role assignment will not be imported to Db2 Big SQL.

Example 2: Importing user permissions (GRANTs)

In Hive:
> GRANT DELETE ON TABLE hivebig.items TO USER newuser;

> SHOW GRANT USER newuser ON ALL;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| database  | table  | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| hivebig   | items  |            |         | newuser         | USER            | DELETE     | false         | 1535385292000  | hive     |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
Then run the import in DB2 Big SQL:
> CALL SYSHADOOP.HCAT_SYNC_OBJECTS('.*', '.*', 'a', 'SKIP', 'CONTINUE', 'IMPORT HIVE AUTHORIZATIONS');
+-----------+---------+------------------+------+--------+---------------------------------------------------------------------------------+
| OBJSCHEMA | OBJNAME | OBJATTRIB        | TYPE | STATUS | DETAILS                                                                              |
+-----------+---------+------------------+------+--------+---------------------------------------------------------------------------------+
| HIVEBIG   | ITEMS   | [NULL]           | T    | SKIP   | Exists                                                                               |
+-----------+---------+------------------+------+--------+---------------------------------------------------------------------------------+
You can confirm that the user permissions were imported by running:
> SELECT * FROM SYSIBMADM.PRIVILEGES WHERE AUTHID = 'NEWUSER';
+---------+------------+-----------+-----------+------------+--------------+------------+
| AUTHID  | AUTHIDTYPE | PRIVILEGE | GRANTABLE | OBJECTNAME | OBJECTSCHEMA | OBJECTTYPE |
+---------+------------+-----------+-----------+------------+--------------+------------+
| NEWUSER | U          | DELETE    | N         | ITEMS      | HIVEBIG      | TABLE      |
+---------+------------+-----------+-----------+------------+--------------+------------+