GRANT Statement (Impala 2.0 or higher only)

The GRANT statement grants a privilege on a specified object to a role or grants a role to a group.

Syntax:

  1. GRANT ROLE role_name TO GROUP group_name
  2. GRANT privilege ON object_type object_name
  3. TO [ROLE] roleName
  4. [WITH GRANT OPTION]
  5. privilege ::= ALL | ALTER | CREATE | DROP | INSERT | REFRESH | SELECT | SELECT(column_name)
  6. object_type ::= TABLE | DATABASE | SERVER | URI

Typically, the object name is an identifier. For URIs, it is a string literal.

Required privileges:

Only administrative users (initially, a predefined set of users specified in the Sentry service configuration file) can use this statement.

Only Sentry administrative users can grant roles to a group.

The WITH GRANT OPTION clause allows members of the specified role to issue GRANT and REVOKE statements for those same privileges 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. This means a user could revoke the privileges of the user that provided them the GRANT OPTION.

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

The ability to grant or revoke SELECT privilege on specific columns is available in Impala 2.3 and higher. See the documentation for Apache Sentry for details.

Usage notes:

You can only grant the ALL privilege to the URI object. Finer-grained privileges mentioned below on a URI are not supported.

Starting in Impala 3.0, finer grained privileges are enforced as below.

PrivilegeScopeSQL Allowed to Execute
REFRESHSERVERINVALIDATE METADATA on all tables in all databases

REFRESH on all tables and functions in all databases

REFRESHDATABASEINVALIDATE METADATA on all tables in the named database

REFRESH on all tables and functions in the named database

REFRESHTABLEINVALIDATE METADATA on the named table

REFRESH on the named table

CREATESERVERCREATE DATABASE on all databases

CREATE TABLE on all tables

CREATEDATABASECREATE TABLE on all tables in the named database
DROPSERVERDROP DATBASE on all databases

DROP TABLE on all tables

DROPDATABASEDROP DATABASE on the named database

DROP TABLE on all tables in the named database

DROPTABLEDROP TABLE on the named table
ALTERSERVERALTER TABLE on all tables
ALTERDATABASEALTER TABLE on the tables in the named database
ALTERTABLEALTER TABLE on the named table

Note:

  • ALTER TABLE RENAME requires the ALTER privilege at the TABLE level and the CREATE privilege at the DATABASE level.
  • CREATE TABLE AS SELECT requires the CREATE privilege on the database that should contain the new table and the SELECT privilege on the tables referenced in the query portion of the statement.
  • COMPUTE STATS requires the ALTER and SELECT privileges on the target table.

Compatibility:

  • The Impala GRANT and REVOKE statements are available in Impala 2.0 and later.
  • In Impala 1.4 and later, Impala can make use of any roles and privileges specified by the GRANT and REVOKE statements in Hive, when your system is configured to use the Sentry service instead of the file-based policy mechanism.
  • The Impala GRANT and REVOKE statements for privileges do not require the ROLE keyword to be repeated before each role name, unlike the equivalent Hive statements.
  • Currently, each Impala GRANT or REVOKE statement can only grant or revoke a single privilege to or from a single role.

Cancellation: Cannot be cancelled.

HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.

Kudu considerations:

Access to Kudu tables must be granted to and revoked from roles with the following considerations:

  • Only users with the ALL privilege on SERVER can create external Kudu tables.
  • The ALL privileges on SERVER is required to specify the kudu.master_addresses property in the CREATE TABLE statements for managed tables as well as external tables.
  • Access to Kudu tables is enforced at the table level and at the column level.
  • The SELECT- and INSERT-specific permissions are supported.
  • The DELETE, UPDATE, and UPSERT operations require the ALL privilege.

Because non-SQL APIs can access Kudu data without going through Sentry authorization, currently the Sentry support is considered preliminary and subject to change.

Related information:

Enabling Sentry Authorization for Impala, REVOKE Statement (Impala 2.0 or higher only), CREATE ROLE Statement (Impala 2.0 or higher only), DROP ROLE Statement (Impala 2.0 or higher only), SHOW Statement

Parent topic: Impala SQL Statements