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 in Cloudera Runtime. 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.

By default, the capability to create StorageHandler-based tables is turned off. Required Ranger features changes might not be available in your environment. For example, if in your Cloudera Data Warehouse environment, you upgraded a Hive Virtual Warehouse but did not upgrade your Data Lake, Ranger might not include the required features).

Prerequisites

  • You must check that you have required Ranger features.
  • Enable authorization of StorageHandler-based tables.

Check for required Ranger features in Data Hub

  1. In Data Hub, click the Ranger Admin web UI link, enter your user name and password, then click Sign In.
  2. Click Ranger > Hadoop SQL and in Hadoop SQL Policies, look for a StorageHandler policy.

Check for Ranger features in Cloudera Data Warehouse

  1. In your Database Catalog, click Open Ranger.
  2. Click Ranger > Hadoop SQL and in Hadoop SQL Policies, look for a StorageHandler policy.

Enable authorization of StorageHandler-based tables in CDW

In CDW, you configure hive.security.authorization.tables.on.storagehandlers = true to enable authorization of StorageHandler-based tables.
  1. Click CDW Overview→Database Catalogs, and select a Database Catalog
  2. Click Configurations→Metastore.
  3. From the dropdown, select hive-site.
  4. Click +.
  5. In Add Custom Configurations, add the property and value hive.security.authorization.tables.on.storagehandlers=true (no single or double quotation marks).
  6. Click ADD.
  7. Click Apply
    The Virtual Warehouse automatically updates and restarts.

Enable authorization of StorageHandler-based tables in Data Hub

In Data Hub, you configure hive.security.authorization.tables.on.storagehandlers = true to enable authorization of StorageHandler-based tables.
  1. In Cloudera Manager, click Clusters→Hive →Configurations, and search for hive.security.authorization.tables.on.storagehandlers.
  2. Set the value to true.
  3. Save changes.

Creating a policy for StorageHandler-based tables

  1. Go to Ranger > Service Manager > Hadoop SQL Policies, and enter a policy name.
  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.