GRANT Statement (Impala 2.0 or higher only)

GRANT Statement (Impala 2.0 or higher only)

The GRANT statement grants a privilege on a specified object to a role, to a user, or to a group. The statement is also used to grant a role to a group.

Syntax:

The following syntax are supported in Impala through Ranger to manage authorization.

GRANT ROLE TO GROUP

For GRANT ROLE TO GROUP, only Ranger administrative users can grant roles to a group.

GRANT ROLE role_name TO GROUP group_name

GRANT privilege ON object TO PRINCIPAL

For GRANT privilege ON object TO principal (a user, a group, or a role), Ranger administrative users can use this statement. If the privilege is granted with the clause of WITH GRANT OPTION, the grantee (the user, the users belonging to the group, or the users belonging to the group granted the role) is able to grant the privilege to other principals.

GRANT privilege ON object_type object_name
   TO USER user_name
GRANT privilege ON object_type object_name
   TO GROUP group_name
  GRANT privilege ON object_type object_name
  TO ROLE role_name
privilege ::= ALL | ALTER | CREATE | DROP | INSERT | REFRESH | SELECT | SELECT(column_name)
object_type ::= SERVER | URI | DATABASE | TABLE

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

Required privileges:

Only administrative users for Ranger can use this statement.

Only Ranger 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.

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.

The table below lists the minimum level of privileges and the scope required to execute SQL statements in Impala 3.0 and higher. The following notations are used:

The SERVER resource type in Ranger implies all databases, all tables, all columns, all UDFs, and all URIs.
ANY denotes the SELECT, INSERT, CREATE, ALTER, DROP, or REFRESH privilege.
ALL privilege denotes the SELECT, INSERT, CREATE, ALTER, DROP, and REFRESH privileges.
The owner of an object effectively has the ALL privilege on the object.
The parent levels of the specified scope are implicitly supported where a scope refers to the specific level in the object hierarchy that the privilege is granted. For example, if a privilege is listed with the TABLE scope, the same privilege granted on DATABASE and SERVER will allow the user to execute the specified SQL statement.

SQL Statement	Privileges	Object Type / Resource Type
SELECT	SELECT	TABLE
WITH SELECT	SELECT	TABLE
EXPLAIN SELECT	SELECT	TABLE
INSERT	INSERT	TABLE
EXPLAIN INSERT	INSERT	TABLE
TRUNCATE	INSERT	TABLE
LOAD	INSERT	TABLE
	ALL	URI
CREATE DATABASE	CREATE	SERVER
CREATE DATABASE LOCATION	CREATE	SERVER
	ALL	URI
CREATE TABLE	CREATE	DATABASE
CREATE TABLE LIKE	CREATE	DATABASE
	SELECT, INSERT, or REFRESH	TABLE
CREATE TABLE AS SELECT	CREATE	DATABASE
	INSERT	DATABASE
	SELECT	TABLE
EXPLAIN CREATE TABLE AS SELECT	CREATE	DATABASE
	INSERT	DATABASE
	SELECT	TABLE
CREATE TABLE LOCATION	CREATE	TABLE
	ALL	URI
CREATE VIEW	CREATE	DATABASE
	SELECT	TABLE
ALTER DATABASE SET OWNER	ALL WITH GRANT	DATABASE
ALTER TABLE	ALTER	TABLE
ALTER TABLE SET LOCATION	ALTER	TABLE
	ALL	URI
ALTER TABLE RENAME	CREATE	DATABASE
	ALL	TABLE
ALTER TABLE SET OWNER	ALL WITH GRANT	TABLE
ALTER VIEW	ALTER	TABLE
	SELECT	TABLE
ALTER VIEW RENAME	CREATE	DATABASE
	ALL	TABLE
ALTER VIEW SET OWNER	ALL WITH GRANT	VIEW
DROP DATABASE	DROP	DATABASE
DROP TABLE	DROP	TABLE
DROP VIEW	DROP	TABLE
CREATE FUNCTION	CREATE	DATABASE
	ALL	URI
DROP FUNCTION	DROP	DATABASE
COMPUTE STATS	ALTER and SELECT	TABLE
DROP STATS	ALTER	TABLE
INVALIDATE METADATA	REFRESH	SERVER
INVALIDATE METADATA <table>	REFRESH	TABLE
REFRESH <table>	REFRESH	TABLE
REFRESH AUTHORIZATION	REFRESH	SERVER
REFRESH FUNCTIONS	REFRESH	DATABASE
COMMENT ON DATABASE	ALTER	DATABASE
COMMENT ON TABLE	ALTER	TABLE
COMMENT ON VIEW	ALTER	TABLE
COMMENT ON COLUMN	ALTER	TABLE
DESCRIBE DATABASE	SELECT, INSERT, or REFRESH	DATABASE
DESCRIBE <table/view>	SELECT, INSERT, or REFRESH	TABLE
If the user has the SELECT privilege at the COLUMN level, only the columns the user has access will show.	SELECT	COLUMN
USE	ANY	TABLE
SHOW DATABASES	ANY	TABLE
SHOW TABLES	ANY	TABLE
SHOW FUNCTIONS	SELECT, INSERT, or REFRESH	DATABASE
SHOW PARTITIONS	SELECT, INSERT, or REFRESH	TABLE
SHOW TABLE STATS	SELECT, INSERT, or REFRESH	TABLE
SHOW COLUMN STATS	SELECT, INSERT, or REFRESH	TABLE
SHOW FILES	SELECT, INSERT, or REFRESH	TABLE
SHOW CREATE TABLE	SELECT, INSERT, or REFRESH	TABLE
SHOW CREATE VIEW	SELECT, INSERT, or REFRESH	TABLE
SHOW CREATE FUNCTION	SELECT, INSERT, or REFRESH	DATABASE
SHOW RANGE PARTITIONS (Kudu only)	SELECT, INSERT, or REFRESH	TABLE
UPDATE (Kudu only)	ALL	TABLE
EXPLAIN UPDATE (Kudu only)	ALL	TABLE
UPSERT (Kudu only)	ALL	TABLE
WITH UPSERT (Kudu only)	ALL	TABLE
EXPLAIN UPSERT (Kudu only)	ALL	TABLE
DELETE (Kudu only)	ALL	TABLE
EXPLAIN DELETE (Kudu only)	ALL	TABLE

Compatibility:

The Impala GRANT and REVOKE statements are available in Impala 2.0 and later.
In general, 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 Ranger service instead of the file-based policy mechanism.
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 principal 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.

Related information:

Impala Authorization, 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