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 |
+---------+------------+-----------+-----------+------------+--------------+------------+