SQL Standards-Based Hive Authorization

Using MEP 6.0.0 and later, you can configure SQL standards-based authorization to enable fine grained access control with SQL commands.

The SQL standards-based authorization mode can be used in conjunction with storage-based authorization on the Metastore server. Similar to the current default authorization in Hive, SQL standards-based authorization is also enforced at query compilation time. To provide security through this option, the client will have to be secured. You can do this by allowing users access only through HiveServer2, and by restricting the user code and non-SQL commands that can be run. The checks will happen against the user who submits the request, but the query will run as the Hive server user. The directories and files for input data would have read access for this Hive server user. For users who do not have the need to protect against malicious users, this could potentially be supported through the Hive command line as well.

  1. Add the following properties to hive-site.xml:
    <!-- SQL standard based authorization -->
  2. Create a hiveserver2-site.xml configuration file:
    touch /opt/mapr/hive/hive-<version>/conf/hiveserver2-site.xml
    Add the following properties to the hiveserver2-site.xml file:
  3. Change owner of the hiveserver2-site.xml file to mapr, and restart Hive services:
    chown mapr:mapr /opt/mapr/hive/hive-<version>/conf/hiveserver2-site.xml 
    maprcli node services -name hs2 -action restart -nodes `hostname -f`
    maprcli node services -name hivemeta -action restart -nodes `hostname -f`

If you are a database administrator and want to run commands such as create role and drop role or to access objects without being given explicit access, you must run the set role command.

  1. Create a test role:
    hive> set role admin;
    Time taken: 0.02 seconds
    hive> create role example_role;
    Time taken: 0.099 seconds
    hive> show roles;
    Time taken: 0.02 seconds, Fetched: 3 row(s)
  2. Grant access:
    hive> GRANT example_role to USER testuser;
    Time taken: 0.058 seconds
    hive> GRANT SELECT  on table eg_test to role example_role;
    Time taken: 0.146 seconds 
  3. Using the test role, check access:
    sudo -u mapruser1 hive
    If there is an access violation, correct it. The following is an example of an access violation error:
    hive> insert into table eg_test values (4), (5), (6);
    FAILED: RuntimeException Cannot create staging directory 
    User mapruser1(user id 5001) has been denied access to create .hive-staging_hive_2018-06-08_10-24-11_566_5325052587659005252-1

    You can apply access restrictions to all actions except for READ access.

    The following are examples of permitted access operations:

    • Select:
      hive> select count (*) from eg_test;
      Time taken: 2.491 seconds, Fetched: 1 row(s)   
    • Describe:
      hive> describe extended eg_test;
      id                      int
      Detailed Table Information      
      Table(tableName:eg_test, dbName:default, owner:mapr, createTime:1528453013, lastAccessTime:0, retention:0, 
      sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:null)], 
      location:maprfs:/user/hive/warehouse/eg_test, inputFormat:org.apache.hadoop.mapred.TextInputFormat,
      compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, 
      parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, 
      skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), 
      storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=6, numRows=3, rawDataSize=3, 
      COLUMN_STATS_ACCURATE={"BASIC_STATS":"true"}, numFiles=1, transient_lastDdlTime=1528453046}, viewOriginalText:null, 
      viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false)
      Time taken: 0.12 seconds, Fetched: 3 row(s)        
    • Show columns:
      hive> SHOW COLUMNS from eg_test;
      Time taken: 0.049 seconds, Fetched: 1 row(s)       
Note: For more information about privileges required for Hive operations, see the open source documentation.