GRANT statement

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

Syntax:

GRANT privilege ON object_type object_name
   TO USER user_name
GRANT privilege ON object_type object_name
   TO GROUP group_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.

The ability to grant or revoke SELECT privilege on specific columns is available in Impala 2.3 and higher.

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 CDH 6.1 / CDH 5.16 and higher. The following notations are used:
  • ANY denotes the SELECT, INSERT, CREATE, or REFRESH privilege.
  • ALL privilege denotes the SELECT, INSERT, CREATE, 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. 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 that specific SQL statement on TABLE.
SQL Statement Privileges Scope
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 ALL TABLE
ALTER TABLE SET LOCATION ALL TABLE
ALL URI
ALTER TABLE RENAME CREATE DATABASE
ALL TABLE
ALTER TABLE SET OWNER ALL WITH GRANT TABLE
ALTER VIEW ALL TABLE
SELECT TABLE
ALTER VIEW RENAME CREATE DATABASE
ALL TABLE
ALTER VIEW SET OWNER ALL WITH GRANT VIEW
DROP DATABASE ALL DATABASE
DROP TABLE ALL TABLE
DROP VIEW ALL TABLE
CREATE FUNCTION CREATE DATABASE
ALL URI
DROP FUNCTION ALL DATABASE
COMPUTE STATS ALL TABLE
DROP STATS ALL 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 ALL DATABASE
COMMENT ON TABLE ALL TABLE
COMMENT ON VIEW ALL TABLE
COMMENT ON COLUMN ALL 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 Impala 1.4 and later, Impala can make use of any privileges specified by the GRANT and REVOKE statements in Hive.

Cancellation: Cannot be cancelled.

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