SQL-Standard Based Authorization

One of the available Authorization methods for HIVE

Use SQL-Standard Based Authorization to enable fine grained access control. It is based on the SQL standard for authorization, and uses the familiar GRANT/REVOKE statements to control access. You enable it through HiveServer2 configuration. For the Hive command line (CLI), SQL Standard Based Authorization is disabled. This is because secure access control is not possible for the Hive command line using an access control policy in Hive, because users have direct access to HDFS and so they can easily bypass the SQL standards based authorization checks or even disable it altogether. You an use SQL Standard Based Authorization along with storage based authorization on the metastore server. For more details, see https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization.

Restrictions on Hive Commands and Statements

When this authorization method is enabled, commands such as dfs, add, delete, compile, and reset do not function.

The commands available to users to modify Hive configurations are restricted to a smaller set, and this set is controlled by the hive.security.authorization.sqlstd.confwhitelist parameter. If this set needs to be altered, the HiveServer2 administrator can set values for this parameter in the hive-site.xml.

Admins are the only users who have the privilege to add or drop functions and macros. This means that Admins can create permanent functions that all users can use.

Users and Roles

Privileges are granted to users and/or roles, where any user can be assigned to one or more roles. The public and admin roles have special meaning:

The public role includes all users. This role can be used in a GRANT statement to assign a privilege to every user.
The admin role is required to run certain commands, such as the ADD JAR command, and it also allows access to certain objects even where access may not have been explicitly granted to the user.

Before a Hive query or command is executed, the privileges assigned to the current user and that user's current roles are checked. Use the SHOW CURRENT ROLES command to view the roles to which a given user belongs. By default, all of a user's roles are in the current role except for the admin role. A user belonging to the admin role needs to run the command SET ROLE ADMIN in order to have the privileges associated with that role.

Procedure for configuring SQL-Standard Based Authorization

Use these steps to configure this authorization method for HIVE

Enable SQL-Standard Based Authorization:
1. Click Hive > Configs.
2. In the Security section of the Configs page, select SQLStdAuth in the Choose Authorization drop down menu.
3. Review the changes by clicking the Show Details link.
4. Find and modify the following property in the Hive Configs tab by typing the property in the Filter:
```
hive.users.in.admin.role = [The value is a list of users that are assigned to the Admin role (list divided by commas).]
```
5. If you work with Big SQL, from the Hive > Configs > Advanced tab, find the hive.security.metastore.authorization.manager property. Remove the org.apache.hadoop.hive.ql.security.authorization.MetaStoreAuthzAPIAuthorizerEmbedOnly value.
As the Hive administrator, run the following command to give yourself Admin status:
```
GRANT admin TO USER hiveadmin;
```
Click Save. In the Save Configuration confirmation window, click Save. Then click OK when the system returns the information window that changes were saved.
The Ambari dashboard indicates that a restart is required. Click Restart > Restart All Affected . Then click Confirm Restart All.

Permissions Required for Hive Operations

Action	Permission required
Create Table	Ownership of database; URI privilege: RWX permission + ownership (for create external table - the location). Ownership of the table directory is also required when creating a managed table with location property. Similar to external tables, the table directory needs to exist before creating the table.
Drop Table	Ownership
Describe Table	Select
Show Partitions	Select
Alter Table Location	Ownership; URI privilege: RWX permission + ownership (for new location)
Alter Partition Location	Ownership; URI privilege: RWX permission + ownership (for new partition location)
Alter Table Add Partition	Insert; URI privilege: RWX permission + ownership (for partition location)
Alter Table Drop Partition	Delete
Alter Table (all of them except the ones listed above)	Ownership
Truncate Table	Ownership
Create View	Select "with grant option"
Alter View Properties	Ownership
Alter View Rename	Ownership
Drop View Properties	Ownership
Drop View	Ownership
Analyze Table	Select and insert
Show Columns	Select
Show Table Status	Select
Show Table Properties	Select
Create Table as Select	Select (of input) and ownership (of database)
Create Index	Ownership (of table)
Drop Index	Ownership
Alter Index Rebuild	Ownership
Alter Index Properties	Ownership
Select	Select
Insert	Insert and delete (for overwrite)
Update	Update
Delete	Delete
Load	Insert (output); delete (output); URI privilege: RWX permission + ownership (input location)
Show Create Table	Select "with grant option"
Create Function	Admin
Drop Function	Admin
Create Macro	Admin
Drop Macro	Admin
MSCK (metastore check)	Admin
Alter Database	Admin
Create Database	URI privilege: RWX permission + ownership (if custom location specified)
Explain	Select
Drop Database	Ownership

Privileges

Select - gives READ access to an object
Insert - gives access to ADD data to an object (table)
Update - gives access to run UPDATE queries on an object (table)
Delete - gives access to DELETE data in an object (table)
All Privileges - gives access to all privileges (includes all above privileges)

Note: These privileges apply to tables and views, but do not apply to databases.

Managing Privileges

Grant

GRANT    
          priv_type [, priv_type ] ...    
          ON table_or_view_name    
          TO principal_specification [, principal_specification] ...    
          [WITH GRANT OPTION];

Revoke

REVOKE [GRANT OPTION FOR]    
          priv_type [, priv_type ] ...    
          ON table_or_view_name    
          FROM principal_specification [, principal_specification] ... ; 

          principal_specification  
          : USER user  
          | ROLE role 

          priv_type  
          : INSERT | SELECT | UPDATE | DELETE | ALL

If users are given privilege "WITH GRANT OPTION" on tables and views, they can grant/revoke the privileges of other users on those objects. The "WITH GRANT OPTION" can be removed without revoking a user's privilege by using the the REVOKE GRANT OPTION FOR <privilege> (this option is available as of HIVE 0.14).

With the REVOKE statement (outlined above), the DROP-BEHAVIOR option of CASCADE is not currently supported (although it is still part of SQL Standard). This means that the REVOKE statement will not remove dependent privileges from users.