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 permission “RW Storage ” permission. Ranger authorizes a user that creates or alters a table against this resource policy. A user having the required “RW Storage” permission on the resource representing the storage-type and storage-url, is allowed to create/alter the table in the respective storage.

  1. On the Service Manager page, select HadoopSQL service.
    The List of Policies HadoopSQL page appears.
  2. To create a new policy, click Add New Policy.
    1. On Create Policy click storage-type as shown in the following example:
    2. Complete the required* fields.
    3. In Allow Conditions, select users, then add the RW Storage permission, as shown in the preceeding example.
    4. Scroll to the bottom of Create Policy, then click Add.
  3. To configure an existing policy named all - storage-type, storage-url, click Edit.

    The Edit Policy page appears.

    Ranger > Edit HadoopSQL Policy page.
  4. 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.

    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.


    (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



    Select User

    Specify the users to which this policy applies.

    To designate a user as an Administrator, select the Delegate Admin check box. Administrators can edit or delete the policy, and can also create child policies based on the original policy.


    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:
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 (‘’ = ‘bar’);
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_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
Sorage URL: mysql-host:port/DBname/*
Storage create table command:
  name string,
  age int,
  gpa double
    "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",
                            "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'"