GRANT Statement (CDH 5.2 or higher only)
The GRANT statement grants a privilege on a specified object to a role or grants a role to a group.
Syntax:
GRANT ROLE role_name TO GROUP group_name GRANT privilege ON object_type object_name TO [ROLE] roleName [WITH GRANT OPTION] privilege ::= ALL | CREATE | 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 (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.
The ability to grant or revoke SELECT privilege on specific columns is available in CDH 5.5 / Impala 2.3 and higher. See Hive SQL Syntax for Use with 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.
- 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 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 CDH 5.2 / Impala 2.0 and later.
- In CDH 5.1 / 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:
- 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:
Enabling Sentry Authorization for Impala, REVOKE Statement (CDH 5.2 or higher only), CREATE ROLE Statement (CDH 5.2 or higher only), DROP ROLE Statement (CDH 5.2 or higher only), SHOW Statement