GRANT statement
The GRANT
statement grants a privilege on a
specified object to a user or to a group.
Statement
Syntax:
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.
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. The following notations are used:
- ANY denotes the
SELECT
,INSERT
,CREATE
, orREFRESH
privilege. - ALL privilege denotes the
SELECT
,INSERT
,CREATE
, andREFRESH
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
andREVOKE
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
andREVOKE
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.