GRANT

Description

Grants specific privileges to a user or a role.

Grants roles to users or other roles.

Syntax

Grant privileges to roles or users

System

GRANT
    { CREATE EXTERNAL CATALOG | REPOSITORY } 
    ON SYSTEM
    TO { ROLE | USER } {<role_name>|<username>} [ WITH GRANT OPTION ]

Global UDF

GRANT
    { USAGE | DROP | ALL [PRIVILEGES]} 
    ON { GLOBAL FUNCTION <function_name>(input_data_type) [, < function_name >(input_data_type),...]    
       | ALL GLOBAL FUNCTIONS }
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]

Example: GRANT USAGE ON GLOBAL FUNCTION a(string) to USER kevin;

Internal catalog

GRANT
    { USAGE | CREATE DATABASE | ALL [PRIVILEGES]} 
    ON CATALOG default_catalog
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]

External catalog

GRANT
   { USAGE | DROP | ALL [PRIVILEGES] } 
   ON { CATALOG <catalog_name> [, <catalog_name>,...] | ALL CATALOGS}
   TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]

Database

GRANT
    { ALTER | DROP | CREATE TABLE | CREATE VIEW | CREATE FUNCTION | CREATE MATERIALIZED VIEW | ALL [PRIVILEGES] } 
    ON { DATABASE <database_name> [, <database_name>,...] | ALL DATABASES }
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]
  
-- You must first run SET CATALOG before you run this command.

Table

GRANT
    { ALTER | DROP | SELECT | INSERT | EXPORT | UPDATE | DELETE | ALL [PRIVILEGES]} 
    ON { TABLE <table_name> [, < table_name >,...]
       | ALL TABLES} IN 
           { DATABASE <database_name> | ALL DATABASES }
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]

-- You must first run SET CATALOG before you run this command. 
-- You can also use db.tbl to represent a table.
GRANT <priv> ON TABLE db.tbl TO {ROLE <role_name> | USER <username>}

-- You can grant the SELECT privilege on all tables in Internal and External Catalogs to read data from these tables. For tables in Hive and Iceberg Catalogs，you can grant the INSERT privilege to write data into such tables.

View

GRANT  
    { ALTER | DROP | SELECT | ALL [PRIVILEGES]} 
    ON { VIEW <view_name> [, < view_name >,...]
       ｜ ALL VIEWS} IN 
           {  DATABASE <database_name> | ALL DATABASES }
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]
    
-- You must first run SET CATALOG before you run this command. 
-- You can also use db.view to represent a view.
GRANT <priv> ON VIEW db.view TO {ROLE <role_name> | USER <username>}

-- For tables in an External Catalog, you can only grant the SELECT privilege on Hive table views.

Materialized view

GRANT
    { SELECT | ALTER | REFRESH | DROP | ALL [PRIVILEGES]} 
    ON { MATERIALIZED VIEW <mv_name> [, < mv_name >,...]
       ｜ ALL MATERIALIZED VIEWS} IN 
           { DATABASE <database_name> | ALL DATABASES }
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]

-- You must first run SET CATALOG before you run this command. 
-- You can also use db.mv to represent an mv.
GRANT <priv> ON MATERIALIZED VIEW db.mv TO {ROLE <role_name> | USER <username>}

Function

GRANT
    { USAGE | DROP | ALL [PRIVILEGES]} 
    ON { FUNCTION <function_name>(input_data_type) [, < function_name >(input_data_type),...]
       ｜ ALL FUNCTIONS} IN 
           {  DATABASE <database_name>  | ALL DATABASES }
    TO { ROLE | USER} {<role_name>|<username>} [ WITH GRANT OPTION ]

-- You must first run SET CATALOG before you run this command. 
-- You can also use db.function to represent a function.
GRANT <priv> ON FUNCTION db.function TO {ROLE <role_name> | USER <username>}

User

GRANT IMPERSONATE
ON USER <username>
TO USER <username> [ WITH GRANT OPTION ]

Grant roles to roles or users

GRANT <role_name> [,<role_name>, ...] TO ROLE <role_name>
GRANT <role_name> [,<role_name>, ...] TO USER <username>

Examples

Grant privileges

Example 1: Grant the privilege to read data from all tables in all databases to user jack.

GRANT SELECT ON *.* TO USER 'jack'@'%';

Example 2: Grant the privilege to load data into all tables of database db1 to role analyst.

GRANT INSERT ON db1.* TO ROLE 'analyst';

Example 3: Grant the privileges to read, update, and load data into table tbl1 of database db1 to user jack.

GRANT SELECT,ALTER,INSERT ON db1.tbl1 TO USER 'jack'@'192.8.%';

Example 4: Grant the privilege to read data from table tbl1 to user jack and allow user jack to grant this privilege to other users or roles (by specifying WITH GRANT OPTION).

GRANT SELECT ON TABLE tbl1 TO USER jack@'172.10.1.10' WITH GRANT OPTION;

Example 5: Grant the privilege to create materialized views in database db1 to user jack.

GRANT CREATE MATERIALIZED VIEW ON DATABASE db1 TO USER 'jack'@'%';

Example 6: Allow user jack to perform operations as user rose.

GRANT IMPERSONATE ON USER 'rose'@'%' TO USER 'jack'@'%';

Grant roles

Example 1: Grant the system pre-defined roles db_admin and user_admin to user jack so that the user has privileges to operate all the database objects and manage all users, roles, and privileges in the cluster:

GRANT db_admin, user_admin TO USER jack;

Example 2: Create a role analyst, grant the privileges to read all tables in all databases to the role, and grant this role to user jack.

CREATE ROLE `analyst`;
GRANT SELECT ON *.* TO ROLE `analyst`;
GRANT `analyst` TO USER jack;