Cloudera Enterprise 6.0.x | Other versions

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/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;

Important:

When Sentry is enabled, you must use Beeline to execute Hive queries. Hive CLI is not supported with Sentry and must be disabled. See Disabling Hive CLI.
There are some differences in syntax between Hive and the corresponding Impala SQL statements. For the Impala syntax, see SQL Statements.

Continue reading:

Column-level Authorization
CREATE ROLE Statement
DROP ROLE Statement
GRANT ROLE Statement
REVOKE ROLE Statement
GRANT <PRIVILEGE> Statement
GRANT <PRIVILEGE> ON URIs (HDFS and S3A)
REVOKE <PRIVILEGE> Statement
GRANT <PRIVILEGE> ... WITH GRANT OPTION
SET ROLE Statement
SHOW Statement

Column-level Authorization

In CDH 5.5 and later, Sentry allows you to assign the SELECT privilege on a subset of columns in a table.

The following command grants a role the SELECT privilege on a column:

GRANT SELECT(column_name) ON TABLE table_name TO ROLE role_name;

The following command can be used to revoke the SELECT privilege on a column:

REVOKE SELECT(column_name) ON TABLE table_name FROM ROLE role_name;

Any new columns added to a table will be inaccessible by default, until explicitly granted access.

Actions allowed for users with SELECT privilege on a column:

Users whose roles have been granted the SELECT privilege on columns only, can perform operations which explicitly refer to those columns. Some examples are:

```
SELECT column_name FROM TABLE table_name;
```
In this case, Sentry will first check to see if the user has the required privileges to access the table. It will then further check to see whether the user has the SELECT privilege to access the column(s).
```
SELECT COUNT(column_name) FROM TABLE table_name;
```
Users are also allowed to use the COUNT function to return the number of values in the column.

SELECT column_name FROM TABLE table_name WHERE column_name operator GROUP BY column_name;

The above command will work as long as you refer only to columns to which you already have access.

To list the column(s) to which the current user has SELECT access:
```
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) database_name];
```

Exceptions:

If a user has SELECT access to all columns in a table, the following command will work. Note that this is an exception, not the norm. In all other cases, SELECT on all columns does not allow you to perform table-level operations.
```
SELECT * FROM TABLE table_name;
```
The DESCRIBE table command differs from the others, in that it does not filter out columns for which the user does not have SELECT access.
```
DESCRIBE table_name;
```

Limitations:

Column-level privileges can only be applied to tables, not partitions or views.
HDFS-Sentry Sync: With HDFS-Sentry sync enabled, even if a user has been granted access to all columns of a table, they will not have access to the corresponding HDFS data files. This is because Sentry does not consider SELECT on all columns equivalent to explicitly being granted SELECT on the table.
Column-level access control for access from Spark SQL is not supported by the HDFS-Sentry plug-in.

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;

Operating system group names must be in lowercase letters. Although group names are case-insensitive to Sentry, Sentry modifies capital letters within group names to be lowercase. For example, Sentry will change TestGroup to testgroup. It is not possible to disable this normalization. Therefore, group information within the environment must be in lowercase letters.

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

To grant privileges on an object to a role, the user must be a Sentry admin user.

GRANT    
    privilege [, privilege ]
    ON object_type object_name
    TO ROLE role_name [,ROLE role_name]

In CDH 5.5 and later, you can grant the SELECT privilege on specific columns of a table. For example:

GRANT SELECT(column_name) ON TABLE table_name TO ROLE role_name;

GRANT <PRIVILEGE> ON URIs (HDFS and S3A)

In CDH 5.8 and later, 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'
CREATE EXTERNAL TABLE foo LOCATION 'namenode:XXX/path/to/table'

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'
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

In CDH 5.2 and later, 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_type object_name
    TO ROLE role_name
    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_type object_name
    FROM ROLE role_name

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

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

To list the database(s) for which the current user has database, table, or column-level access:
```
SHOW DATABASES;
```
To list the table(s) for which the current user has table or column-level access:
```
SHOW TABLES;
```
To list the column(s) to which the current user has SELECT access:
```
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) database_name];
```
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 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.
To list 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;
```
To list all the grants for a role 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;
```

Page generated July 25, 2018.