Creating secure external tables

A step-by-step procedure shows you how to create a secure external table using SERDEPROPERTIES or TBLPROPERTIES and Ranger policies.

You provide SERDEPROPERTIES or TBLPROPERTIES when you create the external table. Hive uses this information to authorize access to the table based on Ranger policies you set up. Continuing with the HBaseStorageHandler example from the previous topic, this procedure shows how to set up Ranger policies for the following table:
Create table foo_ext(i int) stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
with serdeproperties ("hbase.columns.mapping"="cf:string", "hbase.table.name"="hbase_table_0”);
In this case, the URI sent for ranger Authentication is hbase://hostclustername:2181/hbase_table_0/cf. In this procedure, you create a Hadoop SQL policy in Ranger that provides the following authorizations:
  • Authorizes the end user against the location of the external table.

    For example, in Ranger create an HDFS policy to give read, write, and execute permissions to hdfs://user/warehouse/tablespace/external/ hive/foo.

  • Authorizes the end user against the URI used in the table creation statement.

    For example, in Ranger set a SQL policy to give create/alter/drop privileges on hbase://hostname:portnumber/hbase_table_0.

  • Authorizes the user hive to access the HBase table.

    For example, in Ranger set an HBase policy to have full access to hbase://hostname/hbase_table_0.

  1. Go to Ranger Service Manager > Hadoop SQL Policies, enter a policy name, and click the numerical link for all - storage-type, storage-url.
    For example, click 11 in the screenshot below.
  2. Select storage-type, and select hbase.
  3. In Storage URL, select the URI format for the table.
    For example, select hbase-cluster:port/hbase-table.
  4. Add permissions for users or groups to create a table on the table storage location.
    For example, select Create Table on Storage.