Importing Hive Ranger policies to Db2 Big SQL authorizations on HDP
You can use the HCAT_SYNC_OBJECTS stored procedure with the IMPORT HIVE RANGER AUTHORIZATIONS option to import any existing permissions that are set for Hive through the Ranger service to Db2 Big SQL. GRANT statements are issued to the users or groups in Db2 Big SQL based on the permissions that were set by Ranger for the corresponding table and views in Hive.
Syntax
IMPORT HIVE RANGER AUTHORIZATIONS ADMINPROPERTIES=protocol://hostname:port:username:password
Note: This option must be part of the call to the HCAT_SYNC_OBJECTS stored procedure.
The ADMINPROPERTIES parameters are given to connect to the Ranger portal:
- Protocol
- The protocol used to connect to the Ranger portal. For example: http or https.
- Hostname
- The IP address of the Ranger portal. For example: http://9.234.9.10.
- Port
- The port number on which the Ranger web portal can be requested by the Ranger REST API. For example: 6080.
- Username
- The username for the Ranger web portal. For example: admin.
- Password
- The password for the Ranger web portal. For example: admin.
When this option is specified, privileges like SELECT, INDEX and ALTER are directly mapped with the corresponding Db2® Big SQL permissions. Other privileges are mapped as shown in the following table.
| Privileges | Table privileges mapped with Db2 Big SQL | View privileges mapped with Db2 Big SQL |
|---|---|---|
| Select | Select | Select |
| Update | Insert, Update, Delete | Insert, Update, Delete |
| Alter | Alter | NA |
| Index | Index | NA |
| Create | Mapped inside bigsql-conf.xml under property hive.ranger.create.mapping | Mapped inside bigsql-conf.xml under property hive.ranger.create.mapping |
| Drop | Mapped inside bigsql-conf.xml under property hive.ranger.drop.mapping | Mapped inside bigsql-conf.xml under property hive.ranger.drop.mapping |
| Lock | Mapped inside bigsql-conf.xml under property hive.ranger.lock.mapping | Mapped inside bigsql-conf.xml under property hive.ranger.lock.mapping |
| All | Mapped inside bigsql-conf.xml under property hive.ranger.all.mapping That is, Insert, Delete, Update, Select, Alter, Index | Mapped inside bigsql-conf.xml under property hive.ranger.all.mapping That is, Insert, Delete, Update, Select, Index |
| Admin | Mapped inside bigsql-conf.xml under property hive.ranger.admin.mapping | Mapped inside bigsql-conf.xml under property hive.ranger.admin.mapping |
| All + Admin (Delegate Admin) | Mapped to CONTROL privilege in Db2 Big SQL | Mapped to CONTROL privilege in Db2 Big SQL |
Note:
- The following mapping properties need to be added to
bigsql-conf.xml:
<property><name>hive.ranger.admin.mapping</name><value>CONTROL</value></property> <property><name>hive.ranger.create.mapping</name><value>CONTROL</value></property> <property><name>hive.ranger.lock.mapping</name><value /></property> <property><name>hive.ranger.drop.mapping</name><value /></property> - After making any changes to bigsql-default.xml or bigsql-conf.xml the Db2 Big SQL service needs to be restarted. If no mapping is provided inside the configuration files for relevant Db2 Big SQL privileges, then those privileges will not be imported into Db2 Big SQL.
- If SSL is enabled for Ranger, then the ADMINPROPERTIES Protocol parameter must be configured accordingly. For example, https:\\91.23.223.34:6182.
- Only Ranger policies that apply to all columns in a schema or table (that is, with wildcard authorization *) will be imported into Db2 Big SQL. Privileges for columns specified explicitly with column names inside a Ranger policy will not be imported.
- A Ranger policy that is marked as disabled will not be imported into Db2 Big SQL.
- The IMPORT HIVE RANGER AUTHORIZATIONS option imports authorizations set for Hive by Ranger, as opposed to syncing them. So, if any authorizations are revoked in Ranger after this operation, the changed permissions will not automatically propagate to Db2 Big SQL.
- SSL enabled Ranger is supported for the IMPORT HIVE RANGER AUTHORIZATIONS option.
- IMPORT HIVE RANGER AUTHORIZATIONS will not import authorizations for objects that are defined in one or more deny policies. This is due to the inability to represent deny condition via SQL GRANTS/REVOKE. The recommendation is to define policies using the Db2 Big SQL Ranger plugin for the corresponding objects.
- If you have configured Ranger Admin into SSL, make sure to import the Ranger Admin certificate
in the truststore used by HCAT_SYNC_OBJECTS. For example, if you are using self-signed certificates
following the topic Configuring the Db2 Big SQL Ranger plugin for SSL using self-signed certificates, run the following
command:
keytool -import -file ranger-admin-trust.cer -alias rangeradmintrust -keystore /home/bigsql/sqllib/java/jdk64/jre/lib/security/cacerts -storepass changeit - If you want to import Hive Ranger authorizations without updating any existing objects, use the option exists-action=SKIP.
Example
Suppose that you have a Hive Ranger policy which grants SELECT and ALTER permissions to user newuser on table hivebig.items.
Execute HCAT_SYNC_OBJECTS stored procedure with IMPORT HIVE RANGER
AUTHORIZATIONS ADMINPROPERTIES=protocol://hostname:port:username:password the option to
import Hive Ranger policies to Big SQL
authorizations:
iveofour-1.fyre.ibm.com][bigsql] 1> CALL SYSHADOOP.HCAT_SYNC_OBJECTS('.*', '.*', 'a', 'SKIP', 'CONTINUE',
'IMPORT HIVE RANGER AUTHORIZATIONS ADMINPROPERTIES=https://fiveofour-1.fyre.ibm.com:6182:admin:admin');[fiveofour-1.fyre.ibm.com][bigsql] 1> SELECT * FROM SYSIBMADM.PRIVILEGES WHERE AUTHID = 'NEWUSER';
+---------+------------+-----------+-----------+------------+--------------+------------+
| AUTHID | AUTHIDTYPE | PRIVILEGE | GRANTABLE | OBJECTNAME | OBJECTSCHEMA | OBJECTTYPE |
+---------+------------+-----------+-----------+------------+--------------+------------+
| NEWUSER | U | ALTER | N | ITEMS | HIVEBIG | TABLE |
| NEWUSER | U | SELECT | N | ITEMS | HIVEBIG | TABLE |
+---------+------------+-----------+-----------+------------+--------------+------------+
2 rows in results(first row: 0.461s; total: 0.464s)