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_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.

By default, the capability to create StorageHandler-based tables is turned off. Required Ranger features changes might not be available in your environment.

  • Check that you have required Ranger features as described in the next topic.
  • Enable authorization of StorageHandler based tables as described in the subsequent topic.
  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. In Edit Policy, select storage-type, type hbase, and then select hbase to set the storage type to hbase.
  3. In Storage URL, type the URI format for the table.
    For example, type 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.