Importing HDFS authorization to Db2 Big SQL authorization

You can use the HCAT_SYNC_OBJECTS stored procedure with the IMPORT HDFS AUTHORIZATIONS option to import HDFS authorizations to Big SQL authorizations.

The HDFS authorizations on the tables are imported automatically. GRANT statements are issued to the same owner, group, and other roles based on the read/write permissions in the HDFS directory for the specified tables.

This option is only applicable for table objects. If you also specify the exists -action=SKIP option, then tables that exist are not created again, but the HDFS authorizations are imported. If you also specify the exists-action=REPLACE option, then the tables are replaced and HDFS authorizations are imported.

For example, assume that permissions on the file location are:
-rwxr-xr-- hdfs biadmin
The following GRANT statements are automatically issued when you specify the IMPORT HDFS AUTHORIZATIONS option:
GRANT SELECT ON <schema.table> TO user hdfs;
GRANT UPDATE ON <schema.table> TO user hdfs;
GRANT DELETE ON <schema.table> TO user hdfs;
GRANT INSERT ON <schema.table> TO user hdfs;
GRANT SELECT ON <schema.table> TO group biadmin;
GRANT SELECT ON <schema.table> TO public;

Example

Suppose that you have a table file stored in HDFS, owned by a user named newuser, and having the permissions drwxrwxrwx:
[root@fiveofour-1 ~]# hadoop fs -ls /apps/hive/warehouse/hivebig.db
Found 1 items
drwxrwxrwx   - newuser hadoop          0 2018-08-10 09:56 /apps/hive/warehouse/hivebig.db/items
Execute HCAT_SYNC_OBJECTS[link] stored procedure with IMPORT HDFS AUTHORIZATIONS option to import HDFS authorizations to Db2 Big SQL authorizations:
[fiveofour-1.fyre.ibm.com][bigsql] 1> CALL SYSHADOOP.HCAT_SYNC_OBJECTS('.*', '.*', 'a', 'SKIP', 'CONTINUE', 'IMPORT HDFS AUTHORIZATIONS');
+-----------+---------+-----------+------+--------+---------+
| OBJSCHEMA | OBJNAME | OBJATTRIB | TYPE | STATUS | DETAILS |
+-----------+---------+-----------+------+--------+---------+
| HIVEBIG   | ITEMS   | [NULL]    | T    | SKIP   | Exists  |
+-----------+---------+-----------+------+--------+---------+
You can now confirm that newuser has SELECT, INSERT, UPDATE and DELETE privileges in Db2 Big SQL:
[fiveofour-1.fyre.ibm.com][bigsql] 1> SELECT * FROM SYSIBMADM.PRIVILEGES WHERE AUTHID = 'NEWUSER';
+---------+------------+-----------+-----------+------------+--------------+------------+
| AUTHID  | AUTHIDTYPE | PRIVILEGE | GRANTABLE | OBJECTNAME | OBJECTSCHEMA | OBJECTTYPE |
+---------+------------+-----------+-----------+------------+--------------+------------+
| NEWUSER | U          | UPDATE    | N         | ITEMS      | HIVEBIG      | TABLE      |
| NEWUSER | U          | SELECT    | N         | ITEMS      | HIVEBIG      | TABLE      |
| NEWUSER | U          | INSERT    | N         | ITEMS      | HIVEBIG      | TABLE      |
| NEWUSER | U          | DELETE    | N         | ITEMS      | HIVEBIG      | TABLE      |
+---------+------------+-----------+-----------+------------+--------------+------------+
You can also see that same is true for the public and the group:
[fiveofour-1.fyre.ibm.com][bigsql] 1> SELECT * FROM SYSIBMADM.PRIVILEGES WHERE (AUTHID = 'NEWUSER' OR AUTHID = 'HADOOP') AND OBJECTNAME = 'ITEMS';
+---------+------------+-----------+-----------+------------+--------------+------------+
| AUTHID  | AUTHIDTYPE | PRIVILEGE | GRANTABLE | OBJECTNAME | OBJECTSCHEMA | OBJECTTYPE |
+---------+------------+-----------+-----------+------------+--------------+------------+
| NEWUSER | U          | UPDATE    | N         | ITEMS      | HIVEBIG      | TABLE      |
| HADOOP  | G          | SELECT    | N         | ITEMS      | HIVEBIG      | TABLE      |
| HADOOP  | G          | INSERT    | N         | ITEMS      | HIVEBIG      | TABLE      |
| HADOOP  | G          | DELETE    | N         | ITEMS      | HIVEBIG      | TABLE      |
| HADOOP  | G          | UPDATE    | N         | ITEMS      | HIVEBIG      | TABLE      |
| NEWUSER | U          | INSERT    | N         | ITEMS      | HIVEBIG      | TABLE      |
| NEWUSER | U          | DELETE    | N         | ITEMS      | HIVEBIG      | TABLE      |
| NEWUSER | U          | SELECT    | N         | ITEMS      | HIVEBIG      | TABLE      |
+---------+------------+-----------+-----------+------------+--------------+------------+