Hive SQL Syntax for Use with Sentry

Sentry permissions can be configured through GRANT and REVOKE statements issued either interactively or programmatically through the HiveServer2 SQL command line interface, Beeline (documentation available here). The syntax described below is very similar to the GRANT and REVOKE commands that are 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 .. WITH GRANT OPTION to that role: 
CREATE ROLE <admin role>;
GRANT ALL ON SERVER <server1> TO ROLE <admin role> WITH GRANT OPTION;
GRANT ROLE <admin role> TO GROUP <hive>;

Sentry supports column-level authorization with the SELECT privilege. Information about column-level authorization is in the Column-Level Authorization section of this page.

See the sections below for details about the supported statements and privileges:

ALTER DATABASE Statement

Use the ALTER TABLE statement to set or transfer ownership of an HMS database in Sentry. Object ownership must be enabled in Sentry to assign ownership to an object. For information on how to enable object ownership and the privileges an object owner has on the object, see Object Ownership.

You can grant the OWNER privilege on a database to a role or a user with the following commands, respectively:

ALTER DATABASE <database name> SET OWNER ROLE <role name>
ALTER DATABASE <database name> SET OWNER USER <user name>

ALTER TABLE Statement

Use the ALTER TABLE statement to set or transfer ownership of an HMS table in Sentry. Object ownership must be enabled in Sentry to assign ownership to an object. For information on how to enable object ownership and the privileges an object owner has on the object, see Object Ownership.

You can grant the OWNER privilege on a table to a role or a user with the following commands, respectively:

ALTER TABLE <table name> SET OWNER ROLE <role name>
ALTER TABLE <table name> SET OWNER USER <user name>

In Hive, the ALTER TABLE statement also sets the owner of a view. Use the following commands to grant the OWNER privilege on a view:

ALTER TABLE <view name> SET OWNER ROLE <role name>
ALTER TABLE <view name> SET OWNER USER <user name>

ALTER VIEW Statement

In Impala, use the ALTER VIEW statement to transfer ownership of a view in Sentry. Object ownership must be enabled in Sentry to assign ownership to an object. For information on how to enable object ownership and the privileges an object owner has on the object, see Object Ownership.

You can grant the OWNER privilege on a table to a role or a user with the following commands, respectively:

ALTER VIEW <view name> SET OWNER ROLE <role name>
ALTER VIEW <view name> SET OWNER USER <user name>

In Hive, use the ALTER TABLE statement to transfer ownership of a view.

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 or drop roles. By default, the hive, impala and hue users have admin privileges in Sentry.

CREATE ROLE <role name>;

Note that role names are case-insensitive.

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 <group name> [,GROUP <group name>]
Sentry only allows you to grant roles to groups that have alphanumeric characters and underscores (_) in the group name. If the group name contains a non-alphanumeric character that is not an underscore, you can put the group name in backticks (`) to execute the command. For example, Sentry will return an error for the following command: 
GRANT ROLE test TO GROUP test-group;
To grant a role to this group, put the group name in backticks: 
GRANT ROLE test TO GROUP `test-group`;
The following command, which contains an underscore, is also acceptable: 
GRANT ROLE test TO GROUP test_group;

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 <group name> [,GROUP <group name>]

GRANT <Privilege> Statement

Use the GRANT <Privilege> statement to grant privileges on an object to a role. The statement uses the following syntax:

GRANT
    <privilege> [, <privilege> ]
    ON <object type> <object name>
    TO ROLE <role name> [,ROLE <role name>]

For example, you might enter the following statement:

GRANT SELECT ON TABLE feathered_dinosaurs TO ROLE archaeopteryx

The following table describes the privileges you can grant and the objects that they apply to:

Privilege Types
Privilege Object
ALL Server, database, table, URI
CREATE Server, database
INSERT Server, database, table
REFRESH (Impala only) Server, database, table
SELECT Server, database, table, view, column
You can also grant the SELECT privilege on a specific column of a table with the following statement: 
GRANT SELECT <column name> ON TABLE <table name> TO ROLE <role name>;

GRANT <Privilege> ON URIs (HDFS and S3A)

You can only grant the ALL privilege on a URI. See Granting Privileges on URIs for more information about using URIs with Sentry.

If the GRANT for Sentry URI does not specify the complete scheme, or the URI mentioned in Hive DDL statements does not have a scheme, Sentry automatically completes the URI by applying the default scheme based on the HDFS configuration provided in the fs.defaultFS property. Using the same HDFS configuration, Sentry can also auto-complete URIs in case the URI is missing a scheme and an authority component.

When a user attempts to access a URI, Sentry will check to see if the user has the required privileges. During the authorization check, if the URI is incomplete, Sentry will complete the URI using the default HDFS scheme. Note that Sentry does not check URI schemes for completion when they are being used to grant privileges. This is because users can GRANT privileges on URIs that do not have a complete scheme or do not already exist on the filesystem.

For example, in CDH 5.8 and later, the following CREATE EXTERNAL TABLE statement works even though the statement does not include the URI scheme.

GRANT ALL ON URI 'hdfs://namenode:XXX/path/to/table' TO ROLE <role name>;
CREATE EXTERNAL TABLE foo LOCATION 'namenode:XXX/path/to/table' TO ROLE <role name>;

Similarly, the following CREATE EXTERNAL TABLE statement works even though it is missing scheme and authority components.

GRANT ALL ON URI 'hdfs://namenode:XXX/path/to/table' TO ROLE <role name>;
CREATE EXTERNAL TABLE foo LOCATION '/path/to/table'
Since Sentry supports both HDFS and Amazon S3, in CDH 5.8 and later, Cloudera recommends that you specify the fully qualified URI in GRANT statements to avoid confusion. If the underlying storage is a mix of S3 and HDFS, the risk of granting the wrong privileges increases. The following are examples of fully qualified URIs:
  • HDFS: hdfs://host:port/path/to/hdfs/table
  • S3: s3a://host:port/path/to/s3/table

REVOKE <Privilege> Statement

You can use the REVOKE <Privilege> statement to revoke previously-granted privileges that a role has on an object.

REVOKE
    <privilege> [, <privilege> ]
    ON <object type> <object name>
    FROM ROLE <role name> [,ROLE <role name>]
For example, you can revoke previously-granted SELECT privileges on specific columns of a table with the following statement: 
REVOKE SELECT <column name> ON TABLE <table name> FROM ROLE <role name>;

GRANT <Privilege> ... WITH GRANT OPTION

You can add the WITH GRANT OPTION clause to a GRANT <PRIVILEGE> statement to allow the role to grant and revoke the privilege to and from other roles.

The WITH GRANT OPTION clause uses the following syntax:

GRANT
    <privilege>
    ON <object type> <object name>
    TO ROLE <role name>
    WITH GRANT OPTION

When you use the WITH GRANT OPTION clause, the ability to grant and revoke privileges applies to the object container and all its children. For example, if you give GRANT privileges to a role at the database level, that role can grant and revoke privileges to and from the database and all the tables in the database.

Only a role with the GRANT option on a privilege can revoke that privilege from other roles. And you cannot revoke the GRANT privilege from a role without also revoking the privilege. To revoke the GRANT privilege, revoke the privilege that it applies to and then grant that privilege again without the WITH GRANT OPTION clause.

You can use the WITH GRANT OPTION clause with the following privileges:

  • ALL
  • CREATE
  • INSERT
  • REFRESH (Impala only)
  • SELECT

WITH GRANT OPTION Example

For example, if you grant a role the SELECT privilege with the following statement:

GRANT SELECT ON DATABASE coffee_database TO ROLE coffee_bean WITH GRANT OPTION

The coffee_bean role can grant SELECT privileges to other roles on the coffee_database and all the tables within that database.

When you revoke a privilege from a role, the GRANT privilege is also revoked from that role. For example, if you revoke SELECT privileges from the coffee_bean role with this command:

REVOKE SELECT ON DATABASE coffee_database FROM ROLE coffee_bean

The coffee_bean role can no longer grant SELECT privileges on the coffee_database or its tables.

To remove the WITH GRANT OPTION privilege from the coffee_bean role and still allow the role to have SELECT privileges on the coffee_database, you must run these two commands:

REVOKE SELECT ON coffee_database FROM ROLE coffee_bean;

GRANT SELECT ON coffee_database TO ROLE coffee_bean;

SET ROLE Statement

Sentry enforces restrictions on queries based on the roles and privileges that the user has. A user can have multiple roles and a role can have multiple privileges.

The SET ROLE command enforces restrictions at the role level, not at the user level. When you use the SET ROLE command to make a role active, the role becomes current for the session. If a role is not current for the session, it is inactive and the user does not have the privileges assigned to that role. A user can only use the SET ROLE command for roles that have been granted to the user.

To list the roles that are current for the user, use the SHOW CURRENT ROLES command. By default, all roles that are assigned to the user are current.

You can use the following SET ROLE commands:

SET ROLE NONE
Makes all roles for the user inactive. When no role is current, the user does not have any privileges and cannot execute a query.
SET ROLE ALL
Makes all roles that have been granted to the user active. All privileges assigned to those roles are applied. When the user executes a query, the query is filtered based on those privileges.
SET ROLE role name
Makes a single role active. The privileges assigned to that role are applied. When the user executes a query, the query is filtered based on the privileges assigned to that role.

SHOW Statement

  • Lists the database(s) for which the current user has database, table, or column-level access: 
    SHOW DATABASES;
  • Lists the table(s) for which the current user has table or column-level access: 
    SHOW TABLES;
  • Lists the column(s) to which the current user has SELECT access: 
    SHOW COLUMNS (FROM|IN) <table name> [(FROM|IN) <database name>];
  • Lists all the roles in the system (only for sentry admin users): 
    SHOW ROLES;
  • Lists all the roles in effect for the current user session: 
    SHOW CURRENT ROLES;
  • Lists all the roles assigned to the given group name (only allowed for Sentry admin users and others users that are part of the group specified by group name): 
    SHOW ROLE GRANT GROUP group name;

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

    It lists all the grants for the given <role name> (only allowed for Sentry admin users and other users that have been granted the role specified by <role name>). The following command will also list any column-level privileges: 
    SHOW GRANT ROLE <role name>;
  • Lists all the grants for a role or user on the given <object name> (only allowed for Sentry admin users and other users that have been granted the role specified by <role name>). The following command will also list any column-level privileges: 
    SHOW GRANT ROLE <role name> on <object type> <object name>;
SHOW GRANT USER <user name> on <object type> <object name>;
  • Lists the roles and users that have grants on the Hive object. It does not show inherited grants from a parent object. It only shows grants that are applied directly to the object. This command is only available for Hive. 
    SHOW GRANT ON <hive object>;
  • In Hive, this statement lists all the privileges the user has on objects. In Impala, this statement shows the privileges the user has and the privileges the user's roles have on objects. 
    SHOW GRANT USER <user name>;

Privileges

Sentry supports the following privilege types:

CREATE

The CREATE privilege allows a user to create databases, tables, and functions. Note that to create a function, the user also must have ALL permissions on the JAR where the function is located, i.e. GRANT ALL ON URI is required.

You can grant the CREATE privilege on a server or database with the following commands, respectively:

GRANT CREATE ON SERVER <server name> TO ROLE <role name>
GRANT CREATE ON DATABASE <database name> TO ROLE <role name>

For example, you might enter the following command:

GRANT CREATE ON SERVER super_cool_server TO ROLE my_favorite_role

You can use the GRANT CREATE statement with the WITH GRANT OPTION clause. The WITH GRANT OPTION clause allows the granted role to grant the privilege to other roles on the system. See GRANT <Privilege> ... WITH GRANT OPTION for more information about how to use the clause.

The following table shows the CREATE privilege scope:

Scope Available Operations
Server Create databases, tables, views, and functions
Database Create tables and views in the database
Table Not allowed

OWNER

The OWNER privilege gives a user or role special privileges on a database, table, or view in HMS. An object can only have one owner at a time. For more information about the OWNER privilege, see Object Ownership.

The owner of an object can execute any action on the object, similar to the ALL privilege. However, the object owner cannot transfer object ownership unless the ALL privileges with GRANT option is selected. You can specify the privileges that an object owner has on the object with the OWNER Privileges for Sentry Policy Database Objects setting in Cloudera Manager.

The following table shows the OWNER privilege scope:

Scope Available Operations
Server Not available.
Database

Any action allowed by the ALL privilege on the database and tables within the database except transferring ownership of the database or tables.

WITH GRANT enabled: Allows the user or role to grant and revoke privileges to other roles on the database, tables, and views. The user can also transfer ownership of the database and tables within the database. If ownership is transferred at the database level, ownership of the tables is not transferred; the original owner continues to have the OWNER privilege on the tables.
Table / View

Any action allowed by the ALL privilege on the table except transferring ownership of the table or view.

WITH GRANT enabled: Allows the user or role to transfer ownership of the table or view as well as grant and revoke privileges to other roles on the table or view.

For more information about the OWNER privilege, see Object Ownership.

REFRESH (Impala Only)

The REFRESH privilege allows a user to execute commands that update metadata information on Impala databases and tables, such as the REFRESH and INVALIDATE METADATA commands. Keep in mind that metadata invalidation or refresh in Impala is an expensive procedure that can cause performance issues if it is overused.

You can grant the REFRESH privilege on a server, table, or database with the following commands, respectively:

GRANT REFRESH ON SERVER <server name> TO ROLE <role name>
GRANT REFRESH ON DATABASE <database name> TO ROLE <role name>
GRANT REFRESH ON TABLE <table name> TO ROLE <role name>

You can use the GRANT REFRESH statement with the WITH GRANT OPTION clause. The WITH GRANT OPTION clause allows the granted role to grant the privilege to other roles on the system. See GRANT <Privilege> ... WITH GRANT OPTION for more information about how to use the clause.

The following table shows the REFRESH privilege scope:

Scope Available Operations
Server Invalidate the metadata of all tables on the server
Database Invalidate the metadata of all tables in the database
Table Invalidate and refresh the table metadata

SELECT

The SELECT privilege allows a user to view table data and metadata. In additon, you can use the SELECT privilige to provide column-level authorization. See Column-Level Authorization below for details.

You can grant the SELECT privilege on a server, table, or database with the following commands, respectively:

GRANT SELECT ON SERVER <server name> TO ROLE <role name>
GRANT SELECT ON DATABASE <database name> TO ROLE <role name>
GRANT SELECT ON TABLE <table name> TO ROLE <role name>
Scope Available Operations
Server View table data and metadata of all tables in all the databases on the server
Database View table data and metadata of all tables in the database
Table View table data and metadata
Column View table data and metadata for the granted column
View
  • In CDH 5.x, column-level permissions with the SELECT privilege are not available for views.
  • In CDH 6.x, column-level permissions with the SELECT privilege are avaialbe for views in Hive, but not in Impala.

Column-Level Authorization

Sentry provides column-level authorization with the SELECT privilege. You can grant the SELECT privilege to a role for a subset of columns in a table. If a new column is added to the table, the role will not have the SELECT privilege on that column until it is explicitly granted.

You can grant and revoke the SELECT privilege on a set of columns with the following commands, respectively:

GRANT SELECT (<column name>) ON TABLE <table name> TO ROLE <role name>;
REVOKE SELECT (<column name>) ON TABLE <table name> FROM ROLE <role name>;

Users with column-level authorization can execute the following commands on the columns that they have access to. Note that the commands will only return data and metadata for the columns that the user's role has been granted access to.

  • SELECT <column name> FROM TABLE <table name>;
  • SELECT COUNT <column name> FROM TABLE <table name>;
  • SELECT <column name> FROM TABLE <table name> WHERE <column name> <operator> GROUP BY <column name>;
  • SHOW COLUMNS (FROM|IN) <table name> [(FROM|IN) <database name>];
As a rule, a user with select access to columns in a table cannot perform table-level operations, however, if a user has SELECT access to all the columns in a table, that user can also execute the following command: 
SELECT * FROM TABLE <table name>;
Considerations for Column-Level Authorization

When you implement column-level authorization, consider the following:

  • When a user has column-level permissions, it may be confusing that they cannot execute a select * from <tablename> statement even though they have select privileges on some of the columns in the table. Instead, the user must explicitely define the columns that they want to query.
  • Using views instead of column-level authorization requires additional administration, such as creating the view and administering the Sentry grants. In addition, a new view may be needed for a new role, and third-party applications must use a different view based on the role of the user.
  • With HDFS sync enabled, even if a user has been granted access to all columns of a table, the user will not have access ot the corresponding HDFS data files. This is because Sentry does not consider SELECT on all columns equivalent to explicitely being granted SELECT on the table.
  • Column-level access control for access from Spark SQL is not supported by the HDFS-Sentry plug-in.