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.
Attempting to Import Hive Authorization though SQLAuthorization is in disabled mode
Example 1: Importing Hive Role permissions
> 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.
Example 2: Importing user permissions (GRANTs)
> 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 |
+---------+------------+-----------+-----------+------------+--------------+------------+