Hive SQL Syntax for Use with Sentry

Permissions stored in the Sentry service are configured through Grant and Revoke statements issued either interactively or programmatically through the HiveServer2 SQL command line interface, Beeline. The syntax described below is very similar to the GRANT/REVOKE commands available in well-established relational database systems.

In HUE, the Sentry Admin that creates roles and grants privileges must belong to a group that has ALL privileges on the server. For example, you can create a role for the group that contains the hive or impala user, and grant ALL ON SERVER to that role: 
CREATE ROLE <admin role>;
GRANT ALL ON SERVER <server1> TO ROLE <admin_role>;
GRANT ROLE <admin role> TO GROUP <hive>;

Continue reading:

CREATE ROLE Statement

The CREATE ROLE statement creates a role to which privileges can be granted. Privileges can be granted to roles, which can then be assigned to users. A user that has been assigned a role will only be able to exercise the privileges of that role.

Only users that have administrative privileges can create/drop roles. By default, the hive, impala and hue users have admin privileges in Sentry.

CREATE ROLE [role_name];

DROP ROLE Statement

The DROP ROLE statement can be used to remove a role from the database. Once dropped, the role will be revoked for all users to whom it was previously assigned. Queries that are already executing will not be affected. However, since Hive checks user privileges before executing each query, active user sessions in which the role has already been enabled will be affected. 
DROP ROLE [role_name];

GRANT ROLE Statement

The GRANT ROLE statement can be used to grant roles to groups. Only Sentry admin users can grant roles to a group. 
GRANT ROLE role_name [, role_name]    
    TO GROUP <groupName> [,GROUP <groupName>]

REVOKE ROLE Statement

The REVOKE ROLE statement can be used to revoke roles from groups. Only Sentry admin users can revoke the role from a group. 
REVOKE ROLE role_name [, role_name]    
    FROM GROUP <groupName> [,GROUP <groupName>]

GRANT <PRIVILEGE> Statement

In order to grant privileges on an object to a role, the user must be a Sentry admin user. 
GRANT    
    <PRIVILEGE> [, <PRIVILEGE> ]    
    ON <OBJECT> <object_name>    
    TO ROLE <roleName> [,ROLE <roleName>]

REVOKE <PRIVILEGE> Statement

Since only authorized admin users can create roles, consequently only Sentry admin users can revoke privileges from a group. 
REVOKE    
    <PRIVILEGE> [, <PRIVILEGE> ]    
    ON <OBJECT> <object_name>    
    FROM ROLE <roleName> [,ROLE <roleName>]

GRANT <PRIVILEGE> ... WITH GRANT OPTION

With CDH 5.2, you can delegate granting and revoking privileges to other roles. For example, a role that is granted a privilege WITH GRANT OPTION can GRANT/REVOKE the same privilege to/from other roles. Hence, if a role has the ALL privilege on a database and the WITH GRANT OPTION set, users granted that role can execute GRANT/REVOKE statements only for that database or child tables of the database. 
GRANT
    <PRIVILEGE> 
    ON <OBJECT> <object_name>
    TO ROLE <roleName> 
    WITH GRANT OPTION
Only a role with GRANT option on a specific privilege or its parent privilege can revoke that privilege from other roles. Once the following statement is executed, all privileges with and without grant option are revoked. 
REVOKE
    <PRIVILEGE>
    ON <OBJECT> <object_name>
    FROM ROLE <roleName>
Hive does not currently support revoking only the WITH GRANT OPTION from a privilege previously granted to a role. To remove the WITH GRANT OPTION, revoke the privilege and grant it again without the WITH GRANT OPTION flag.

SET ROLE Statement

The SET ROLE statement can be used to specify a role to be enabled for the current session. A user can only enable a role that has been granted to them. Any roles not listed and not already enabled are disabled for the current session. If no roles are enabled, the user will have the privileges granted by any of the roles that (s)he belongs to.
To enable a specific role: 
SET ROLE <roleName>;
To enable all roles: 
SET ROLE ALL;
No roles enabled: 
SET ROLE NONE;

SHOW Statement

To list all the roles in the system (only for sentry admin users): 
SHOW ROLES;
To list all the roles in effect for the current user session: 
SHOW CURRENT ROLES;
To list all the roles assigned to the given <groupName> (only allowed for Sentry admin users and others users that are part of the group specified by <groupName>): 
SHOW ROLE GRANT GROUP <groupName>;

The SHOW statement can also be used to list the privileges that have been granted to a role or all the grants given to a role for a particular object.

To list all the grants for the given <roleName> (only allowed for Sentry admin users and other users that have been granted the role specified by <roleName>): 
SHOW GRANT ROLE <roleName>;
To list all the grants for a role on the given <objectName> (only allowed for Sentry admin users and other users that have been granted the role specified by <roleName>): 
SHOW GRANT ROLE <roleName> on OBJECT <objectName>;

Example: Using Grant/Revoke Statements to Match an Existing Policy File

Here is a sample policy file: 
[groups] 
# Assigns each Hadoop group to its set of roles  
manager = analyst_role, junior_analyst_role 
analyst = analyst_role 
jranalyst = junior_analyst_role 
customers_admin = customers_admin_role 
admin = admin_role  

[roles] # The uris below define a define a landing skid which 
# the user can use to import or export data from the system. 
# Since the server runs as the user "hive" files in that directory 
# must either have the group hive and read/write set or 
# be world read/write. 
analyst_role = server=server1->db=analyst1, \     
    server=server1->db=jranalyst1->table=*->action=select         
    server=server1->uri=hdfs://ha-nn-uri/landing/analyst1 
junior_analyst_role = server=server1->db=jranalyst1, \     
    server=server1->uri=hdfs://ha-nn-uri/landing/jranalyst1  

# Implies everything on server1. 
admin_role = server=server1

The following sections show how you can use the new GRANT statements to assign privileges to roles (and assign roles to groups) to match the sample policy file above.

Grant privileges to analyst_role: 
CREATE ROLE analyst_role;
GRANT ALL ON DATABASE analyst1 TO ROLE analyst_role;
GRANT SELECT ON DATABASE jranalyst1 TO ROLE analyst_role;
GRANT ALL ON URI 'hdfs://ha-nn-uri/landing/analyst1' \
TO ROLE analyst_role;
Grant privileges to junior_analyst_role: 
CREATE ROLE junior_analyst_role;
GRANT ALL ON DATABASE jranalyst1 TO ROLE junior_analyst_role;
GRANT ALL ON URI 'hdfs://ha-nn-uri/landing/jranalyst1' \
TO ROLE junior_analyst_role;
Grant privileges to admin_role: 
CREATE ROLE admin_role
GRANT ALL ON SERVER server TO ROLE admin_role;
Grant roles to groups: 
GRANT ROLE admin_role TO GROUP admin;
GRANT ROLE analyst_role TO GROUP analyst;
GRANT ROLE jranalyst_role TO GROUP jranalyst;