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 .

  1. Select the HadoopSQLservice on the Service Manager page.
    The List of Policies HadoopSQL page appears.
  2. 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.
  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 follows:
    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 the Delegate Admin check box. 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");
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'"
	);
Kafka StorageHandler policy:
Storage Type: kafka
Storage URL: bootstrap-server:port/kafka-topic
JDBC StorageHandler policy:
Storage Type: jdbc:mysql
Storage URL: mysql-host:port/DBname/Table , or
Storage URL: mysql-host:port/DBname/*
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"
);