본문으로 건너뛰기
최종 수정: 에

REVOKE FROM

Description​

The REVOKE command has the following functions:

  1. Revoke the specified permission of a user or a role.
  2. Revoke the specified role previously granted to a user.

Related Commands

Syntax​

Revoke the specified permission of a user or a role

REVOKE <privilege_list> 
ON { <priv_level> 
   | RESOURCE <resource_name> 
   | WORKLOAD GROUP <workload_group_name> 
   | COMPUTE GROUP <compute_group_name> 
   | STORAGE VAULT <storage_vault_name> 
   } 
FROM { <user_identity> | ROLE <role_name> }

Revoke the specified role previously granted to a user

REVOKE <role_list> FROM <user_identity>

Required Parameters​

1. <privilege_list>

A comma-separated list of privileges to be revoked. Supported privileges include:

  • NODE_PRIV: Cluster node operation permissions
  • ADMIN_PRIV: Administrator privileges
  • GRANT_PRIV: Authorization privileges
  • SELECT_PRIV: Query privileges
  • LOAD_PRIV: Data import privileges
  • ALTER_PRIV: Modify privileges
  • CREATE_PRIV: Create privileges
  • DROP_PRIV: Delete privileges
  • USAGE_PRIV: Usage privileges
  • SHOW_VIEW_PRIV: View definition privileges

2. <priv_level>

Specifies the scope of the privileges. Supported formats include:

  • ..*: All catalogs, databases, and tables
  • catalog_name..: Specifies all databases and tables in the specified catalog
  • catalog_name.db.*: Specifies all tables in the specified database
  • catalog_name.db.tbl: Specifies a specific table in the specified database

3. <resource_name>

Specifies the resource name. Supports % (matches any string) and _ (matches any single character) wildcard characters.

4. <workload_group_name>

Specifies the workload group name. Supports % (matches any string) and _ (matches any single character) wildcard characters.

5. <compute_group_name>

Specifies the compute group name. Supports % (matches any string) and _ (matches any single character) wildcard characters.

6. <storage_vault_name>

Specifies the storage vault name. Supports % (matches any string) and _ (matches any single character) wildcard characters.

7. <user_identity>

Specifies the user identity. The user must be a user created with the CREATE USER statement. The host part of the user identity can be a domain name, and the permission revocation time may have a delay of 1 minute for domain names.

8. <role_name>

Specifies the role name. The role must exist.

9. <role_list>

A comma-separated list of roles to be revoked. All specified roles must exist.

Since 4.1.5, a privilege object name has at most three components: db, db.table, or catalog.db.table, including the corresponding wildcard forms. More than three components are rejected, rather than silently ignoring extra prefixes.

Access Control Requirements​

Users executing this SQL command must have at least the following privileges:

PrivilegeObjectNotes
GRANT_PRIVUser or RoleOnly users or roles with the GRANT_PRIV privilege can perform the REVOKE operation.

Since 4.1.5, a non-ADMIN user granting or revoking column privileges needs both the existing GRANT privilege and the corresponding table privilege or the corresponding privilege on every listed column. GRANT alone does not allow users to grant or revoke column privileges they do not hold.

Examples​

Setup — create the users / roles / database / workload group that the examples below revoke from, and the prerequisite GRANTs that make each REVOKE meaningful (you can't revoke a privilege that was never granted):

CREATE DATABASE db1;
CREATE USER jack@'192.%' IDENTIFIED BY 'jack_pwd';
CREATE USER jack@'%' IDENTIFIED BY 'jack_pwd';
CREATE ROLE role1;
CREATE ROLE role2;
CREATE ROLE my_role;
CREATE WORKLOAD GROUP g1 PROPERTIES("max_cpu_percent"="10");

GRANT SELECT_PRIV ON db1.* TO 'jack'@'192.%';
GRANT USAGE_PRIV ON RESOURCE 'spark_resource' TO 'jack'@'192.%';
GRANT 'role1','role2' TO 'jack'@'192.%';
GRANT USAGE_PRIV ON WORKLOAD GROUP 'g1' TO 'jack'@'%';
GRANT USAGE_PRIV ON WORKLOAD GROUP '%' TO 'jack'@'%';
GRANT USAGE_PRIV ON COMPUTE GROUP 'group1' TO 'jack'@'%';
GRANT USAGE_PRIV ON COMPUTE GROUP 'group1' TO ROLE 'my_role';
GRANT USAGE_PRIV ON STORAGE VAULT 'vault1' TO 'jack'@'%';
GRANT USAGE_PRIV ON STORAGE VAULT 'vault1' TO ROLE 'my_role';
GRANT USAGE_PRIV ON STORAGE VAULT '%' TO 'jack'@'%';

  • Revoke the SELECT privilege on a specific database from a user:

    REVOKE SELECT_PRIV ON db1.* FROM 'jack'@'192.%';

  • Revoke the usage privilege on a specific resource from a user:

    REVOKE USAGE_PRIV ON RESOURCE 'spark_resource' FROM 'jack'@'192.%';

  • Revoke roles from a user:

    REVOKE 'role1','role2' FROM 'jack'@'192.%';

  • Revoke the usage privilege on a specific workload group from a user:

    REVOKE USAGE_PRIV ON WORKLOAD GROUP 'g1' FROM 'jack'@'%';

  • Revoke the usage privilege on all workload groups from a user:

    REVOKE USAGE_PRIV ON WORKLOAD GROUP '%' FROM 'jack'@'%';

  • Revoke the usage privilege on all compute groups from a user:

    REVOKE USAGE_PRIV ON COMPUTE GROUP 'group1' FROM 'jack'@'%';

  • Revoke the usage privilege on a compute group from a role:

    REVOKE USAGE_PRIV ON COMPUTE GROUP 'group1' FROM ROLE 'my_role';

  • Revoke the usage privilege on all storage vaults from a user:

    REVOKE USAGE_PRIV ON STORAGE VAULT 'vault1' FROM 'jack'@'%';

  • Revoke the usage privilege on a storage vault from a role:

    REVOKE USAGE_PRIV ON STORAGE VAULT 'vault1' FROM ROLE 'my_role';

  • Revoke the usage privilege on all storage vaults from a role:

    REVOKE USAGE_PRIV ON STORAGE VAULT '%' FROM 'jack'@'%';
Report issue