Skip to main content
Version: 3.2

REVOKE

Description

Revokes specific privileges or roles from a user or a role. For the privileges supported by StarRocks, see Privileges supported by StarRocks.

tip
  • Common users can only revoke their privileges that have the WITH GRANT OPTION keyword from other users and roles. For information about WITH GRANT OPTION, see GRANT.
  • Only users with the user_admin role has the privilege to revoke privileges from other users.

Syntax

Revoke privileges

The privileges that can be revoked are object-specific. The following part describes syntax based on objects.

System

REVOKE
{ CREATE RESOURCE GROUP | CREATE RESOURCE | CREATE EXTERNAL CATALOG | REPOSITORY | BLACKLIST | FILE | OPERATE }
ON SYSTEM
FROM { ROLE | USER} {<role_name>|<user_identity>}

Resource group

REVOKE
{ ALTER | DROP | ALL [PRIVILEGES] }
ON { RESOURCE GROUP <resourcegroup_name> [, <resourcegroup_name>,...]ALL RESOURCE GROUPS}
FROM { ROLE | USER} {<role_name>|<user_identity>}

Resource

REVOKE
{ USAGE | ALTER | DROP | ALL [PRIVILEGES] }
ON { RESOURCE <resource_name> [, <resource_name>,...]ALL RESOURCES}
FROM { ROLE | USER} {<role_name>|<user_identity>}

User

REVOKE IMPERSONATE ON USER <user_identity> FROM USER <user_identity>;

Global UDF

REVOKE
{ USAGE | DROP | ALL [PRIVILEGES]}
ON { GLOBAL FUNCTION <function_name> [, <function_name>,...]
| ALL GLOBAL FUNCTIONS }
FROM { ROLE | USER} {<role_name>|<user_identity>}

Internal catalog

REVOKE 
{ USAGE | CREATE DATABASE | ALL [PRIVILEGES]}
ON CATALOG default_catalog
FROM { ROLE | USER} {<role_name>|<user_identity>}

External catalog

REVOKE  
{ USAGE | DROP | ALL [PRIVILEGES] }
ON { CATALOG <catalog_name> [, <catalog_name>,...] | ALL CATALOGS}
FROM { ROLE | USER} {<role_name>|<user_identity>}

Database

REVOKE 
{ ALTER | DROP | CREATE TABLE | CREATE VIEW | CREATE FUNCTION | CREATE MATERIALIZED VIEW | ALL [PRIVILEGES] }
ON {{ DATABASE <database_name> [, <database_name>,...]} | ALL DATABASES }
FROM { ROLE | USER} {<role_name>|<user_identity>}
  • You must first run SET CATALOG before you run this command.

Table

REVOKE  
{ ALTER | DROP | SELECT | INSERT | EXPORT | UPDATE | DELETE | ALL [PRIVILEGES]}
ON { TABLE <table_name> [, < table_name >,...]
| ALL TABLES} IN
{ { DATABASE <database_name> [, <database_name>,...]} | ALL DATABASES }
FROM { ROLE | USER} {<role_name>|<user_identity>}
  • You must first run SET CATALOG before you run this command.

  • You can also use db.tbl to represent a table.

    REVOKE <priv> ON TABLE db.tbl FROM {ROLE <role_name> | USER <user_identity>}

View

REVOKE  
{ ALTER | DROP | SELECT | ALL [PRIVILEGES]}
ON { VIEW <view_name> [, < view_name >,...]
ALL VIEWS} IN
{ { DATABASE <database_name> [, <database_name>,...]} | ALL DATABASES }
FROM { ROLE | USER} {<role_name>|<user_identity>}
  • You must first run SET CATALOG before you run this command.

  • You can also use db.view to represent a view.

    REVOKE <priv> ON VIEW db.view FROM {ROLE <role_name> | USER <user_identity>}

Materialized view

REVOKE
{ SELECT | ALTER | REFRESH | DROP | ALL [PRIVILEGES]}
ON { MATERIALIZED VIEW <mv_name> [, < mv_name >,...]
ALL MATERIALIZED VIEWS} IN
{ { DATABASE <database_name> [, <database_name>,...] } | ALL [DATABASES] }
FROM { ROLE | USER} {<role_name>|<user_identity>}
  • You must first run SET CATALOG before you run this command.

  • You can also use db.mv to represent an mv.

    REVOKE <priv> ON MATERIALIZED VIEW db.mv FROM {ROLE <role_name> | USER <user_identity>}

Function

REVOKE
{ USAGE | DROP | ALL [PRIVILEGES]}
ON { FUNCTION <function_name> [, < function_name >,...]
ALL FUNCTIONS} IN
{ { DATABASE <database_name> [, <database_name>,...] } | ALL DATABASES }
FROM { ROLE | USER} {<role_name>|<user_identity>}
  • You must first run SET CATALOG before you run this command.

  • You can also use db.function to represent a function.

    REVOKE <priv> ON FUNCTION db.function FROM {ROLE <role_name> | USER <user_identity>}

Storage volume

REVOKE
CREATE STORAGE VOLUME
ON SYSTEM
FROM { ROLE | USER} {<role_name>|<user_identity>}

REVOKE
{ USAGE | ALTER | DROP | ALL [PRIVILEGES] }
ON { STORAGE VOLUME < name > [, < name >,...]ALL STORAGE VOLUME}
FROM { ROLE | USER} {<role_name>|<user_identity>}

Revoke roles

REVOKE <role_name> [,<role_name>, ...] FROM ROLE <role_name>
REVOKE <role_name> [,<role_name>, ...] FROM USER <user_identity>

Parameters

ParameterDescription
role_nameThe role name.
user_identityThe user identity, for example, 'jack'@'192.%'.
resourcegroup_nameThe resource group name
resource_nameThe resource name.
function_nameThe function name.
catalog_nameThe name of the external catalog.
database_nameThe database name.
table_nameThe table name.
view_nameThe view name.
mv_nameThe name of the materialized view.

Examples

Revoke privileges

Revoke the SELECT privilege on table sr_member from user jack:

REVOKE SELECT ON TABLE sr_member FROM USER 'jack'@'192.%'

Revoke the USAGE privilege on resource spark_resource from role test_role:

REVOKE USAGE ON RESOURCE 'spark_resource' FROM ROLE 'test_role';

Revoke roles

Revoke the role example_role from user jack:

REVOKE example_role FROM 'jack'@'%';

Revoke the role example_role from role test_role:

REVOKE example_role FROM ROLE 'test_role';

References

GRANT