Configure a resource-based storage handler policy: HadoopSQL

How to configure a policy that allows authorized users to create data tables using storage-handlers.

Ranger includes “storage-type” and “storage-url” resources in HadoopSQL Service that support only the “RW Storage ” permission. Ranger authorizes users to create or alter tables against this resource policy. Users are allowed to create/alter the table in the respective storage if they have the required “RW Storage” permission on the resource representing the storage-type and storage-url .

On Service Manager > Resource Policies, select an existing HadoopSQL service.

List of Policies displays a list of the policies defined for HadoopSQL service.
note
Service_name remains cm_hive. Display name is HadoopSQL.
Select Add New Policy to create a new policy.
1. Within Create Policy, select storage-type as shown in the following example:
2. Complete the required* fields.
3. Under Allow Conditions, select users and add the RW Storage permission, as shown in the preceeding example.
4. Scroll to the bottom of Create Policy, then click Add.
To configure an existing policy named all - storage-type, storage-url, click Edit.

The Edit Policy page appears.

Complete the Edit Policy page as shown in the preceding example using the follow policy detail descriptions:

Table 1. Policy Details
Field	Description
Policy Name	Enter an appropriate policy name. This name cannot be duplicated across the system. This field is mandatory. The policy is enabled by default.
normal/override	Enables you to specify an override policy. When override is selected, the access permissions in the policy override the access permissions in existing policies. This feature can be used with Add Validity Period to create temporary access policies that override existing policies.
Policy Label	Specify a label for this policy. You can search reports and filter policies based on these labels.
storage-type	Type in the applicable storage type. * allows athorizes users to create any table in the spcified storage type..
storage url	Type in the applicable storage URL. * allows athorizes users to create any table in the spcified storage URL. Select Exclude to deny access.
Description	(Optional) Describe the purpose of the policy.
Audit Logging	Specify whether this policy is audited. (De-select to disable auditing).
Add Validity Period	Specify a start and end time for the policy.

Table 2. Allow Conditions
Label	Description
Select User	Specify the users to which this policy applies. To designate a user as an Administrator, select Delegate Admin. Administrators can edit or delete the policy, and can also create child policies based on the original policy.
Permissions	Add or edit permissions: RW Storage, You can assign read and select permissions to rangerlookup user.
Delegate Admin	You can use Delegate Admin to assign administrator privileges to the roles, groups, or users specified in the policy. Administrators can edit or delete the policy, and can also create child policies based on the original policy.

Example StorageHandler Policy Definitions and Use Cases:

HBase StorageHandler policy:

Storage Type: hbase

Storage URL: hbase-cluster:port/hbase-table

Storage create table command:

CREATE [EXTERNAL] table foo(…)
STORED BY‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
TBLPROPERTIES (‘hbase.table.name’ = ‘bar’);

e.g:
CREATE TABLE hive_hbase_test_1(key int, value string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf:string", "hbase.table.name" = "hbase_test_1");

Iceberg StorageHandler policy:: Storage type: iceberg; Storage URL: DBname/Table* , or; Storage URL: DBname/*

JDBC StorageHandler policy:

Storage Type: jdbc:mysql

Storage URL: mysql-host:port/DBname/Table , or

Storage URL: mysql-host:port/DBname/*

note

Policy and table definitions must be in sync regarding the port definition, even for default port numbers. For example, if port number 3306 is defined in the policy for mysql and this port number is left out from the URL as default value for the JDBC Driver, you must use the same reference as defined in the policy when creating the external table.

Using an explicit table name allows only to reference that specific table with hive.sql.table while using * allows not only to reference any tables from the database but also allows you to write a custom query against this database, for example using hive.sql.query.

Storage create table command:

CREATE [EXTERNAL] TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    "hive.sql.database.type" = "MYSQL",
    "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
    "hive.sql.jdbc.url" = "jdbc:mysql://localhost/sample",
    "hive.sql.dbcp.username" = "hive",
    "hive.sql.dbcp.password" = "hive",
    "hive.sql.table" = "STUDENT",
    "hive.sql.dbcp.maxActive" = "1"
);

Kafka StorageHandler policy:: Storage Type: kafka; Storage URL: bootstrap-server:port/kafka-topic

Phoenix StorageHandler policy:

Storage Type: phoenix

Storage URL: phoenix-cluster:port/table-name

Storage create table command:

CREATE [EXTERNAL] TABLE phoenix_table (
                            s1 string,
                            i1 int,
                            f1 float,
                            d1 double
                            )
                            STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
                            TBLPROPERTIES (
                            "phoenix.table.name" = "phoenix_table",
                            "phoenix.zookeeper.quorum" = "localhost",
                            "phoenix.zookeeper.znode.parent" = "/hbase",
                            "phoenix.zookeeper.client.port" = "2181",
                            "phoenix.rowkeys" = "s1, i1",
                            "phoenix.column.mapping" = "s1:s1, i1:i1, f1:f1, d1:d1",
                            "phoenix.table.options" = "SALT_BUCKETS=10, DATA_BLOCK_ENCODING='DIFF'"
                            );