REVOKE

Description

This statement is used to revoke permissions previously granted to a user or role, or to revoke a role from a user or role.

Limitations and Considerations

• Revoking object privileges does not cascade. For example, if user user1 grants certain privileges to user2, revoking user1's privileges will not also revoke user2's corresponding privileges.
• The user or role whose privileges are to be revoked must exist; otherwise, an error will be returned.
• Roles specified in the mandatory_roles system variable cannot be revoked.
• The REVOKE statement takes effect immediately for the currently logged-in user.

Privilege Requirements

• Revoke specific privilege level privileges: The user must have the privilege to be revoked (e.g., user1 must have the SELECT privilege on table t1 to revoke user2's SELECT privilege on t1), and also have the GRANT OPTION privilege.

• Revoke all privileges and grant options: When revoking ALL PRIVILEGES and GRANT OPTION, the current user must have the global GRANT OPTION privilege, or the UPDATE and DELETE privileges on the privilege tables.

• Revoke role privileges from a user or role: When revoking a role, the current user must have the role to be revoked and the ADMIN OPTION privilege.

For more information about seekdb privileges, see seekdb privilege types.

Syntax

Revoke object privileges from a user or role

revoke_stmt1:
    REVOKE grant_privileges ON priv_level FROM user_or_role_list;

revoke_stmt2:
    REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user_or_role_list;

revoke_stmt3:
    REVOKE [IF EXISTS] role_name_list FROM user_or_role_list [IGNORE UNKNOWN USER];

grant_privileges:
    priv_type_list
    | ALL [PRIVILEGES]

priv_type_list:
    priv_type[(column_name_list)] [, priv_type[(column_name_list)] ...]

column_name_list:
    column_name [, column_name ...]

priv_level:
    *.*
    | db_name.*
    | db_name.table_name
    | CATALOG external_catalog_name

user_or_role_list:
      user_or_role [, user_or_role ...]

user_or_role:
    user_name | role_name

role_name_list:
    role_name [, role_name ...]

Parameter Description

Parameter	Description
revoke_stmt1	The SQL statement to revoke specific privilege level privileges from a user or role.
revoke_stmt2	The SQL statement to revoke all privileges and grant options from a user or role.
revoke_stmt3	The SQL statement to revoke a role from a user or role.
grant_privileges	Specifies the type of privilege to revoke. It can be a list of specific privilege types or all privileges except GRANT OPTION.
priv_type_list	Specifies a list of specific privilege types. When revoking multiple privileges, separate them with commas (,).
priv_type	Specifies a specific privilege type. For more information about specific privilege types, see seekdb privilege types. Note: The PROXY parameter is only recorded for syntax in the current version and does not take effect.
column_name_list	Optional. Specifies a list of column-level privileges to revoke from a table. If not specified, the privileges for the entire table or object are revoked.
ALL [PRIVILEGES]	Specifies all privileges except GRANT OPTION.
priv_level	Specifies the privilege level. For more information about the specific values of the privilege level, see priv_level below.
user_or_role_list	Specifies a list of users or roles whose privileges are to be revoked. When revoking privileges for multiple users or roles, separate the user and role names with commas (,).
user_name	Specifies the user name.
role_name	Specifies the role name.
IF EXISTS	Optional. If the role to be revoked does not exist, a warning is issued instead of an error message when this option is used.
role_name_list	Specifies a list of roles to revoke. When revoking multiple roles, separate the role names with commas (,).
IGNORE UNKNOWN USER	Optional. If the grantee (user or role) does not exist, a warning is issued instead of an error message when this option is used.
proxy	The syntax of this keyword is recorded in the current version, but the feature does not take effect.

priv_level

• *.*: All tables in all databases.

  Example:

  Revoke the global SELECT and INSERT privileges from users user001 and user002.

  REVOKE SELECT, INSERT ON *.* FROM user001, user002;

• db_name.*: All tables in the specified database. db_name is the name of the specified database.

  Example:

  Revoke the insert privilege on all tables in the test database from user user003.

  REVOKE INSERT ON test.* FROM user003;

• db_name.table_name: A specific table in a specific database.

  Example:

  Revoke the SELECT privilege on the tbl2 table in the test database from user user003.

  REVOKE SELECT ON test.tbl2 FROM user003;

• CATALOG external_catalog_name: A directory in a Catalog.

  Example:

  Revoke the USE CATALOG privilege on the test_odps_catalog object in the Catalog from user user004.

  REVOKE USE CATALOG ON CATALOG test_odps_catalog FROM user004;

Examples

Example 1: Revoke object privileges

Revoke the SELECT privilege from the test_user001 user and view the privileges granted by ALL PRIVILEGES.

1. Grant the ALL PRIVILEGES privilege to the test_user001 user.

   GRANT ALL PRIVILEGES ON *.* TO test_user001;

2. View the privileges granted to the test_user001 user.

   SHOW GRANTS FOR test_user001;

   The return result is as follows:

   +-----------------------------------------------+
   | Grants for test_user001@%                     |
   +-----------------------------------------------+
   | GRANT ALL PRIVILEGES ON *.* TO 'test_user001' |
   +-----------------------------------------------+
   1 row in set (0.001 sec)

3. Revoke the SELECT privilege from the test_user001 user.

   REVOKE SELECT ON *.* FROM test_user001;

4. View the privileges granted to the test_user001 user again.

   SHOW GRANTS FOR test_user001;

   The return result is as follows:

   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Grants for test_user001@%                                                                                                                                                                                                                                                                                                                                                                                                                                       |
   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | GRANT ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, INDEX, CREATE VIEW, SHOW VIEW, SHOW DATABASES, SUPER, PROCESS, CREATE_SYNONYM, REFERENCES, FILE, ALTER TENANT, ALTER SYSTEM, CREATE RESOURCE POOL, CREATE RESOURCE UNIT, REPLICATION SLAVE, REPLICATION CLIENT, DROP DATABASE LINK, CREATE DATABASE LINK, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TABLESPACE, SHUTDOWN, RELOAD, CREATE ROLE, DROP ROLE, TRIGGER ON *.* TO 'test_user001' |
   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.001 sec)

Example 2: Revoke all privileges from a user

Revoke all privileges from the test_user002 user.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM test_user002;

Example 3: Revoke a role from a user or role

• Revoke the role001 role from the role002 role.

  REVOKE role001 FROM role002;

• Revoke the role001 role from the user001 user.

  REVOKE role001 FROM user001;

References

• Revoke privileges from a role
• Revoke a role from a user or role
• Modify user privileges
• GRANT