REVOKE

Purpose

This statement is used by a system administrator to revoke some privileges from a user.

Limitations and considerations

• The user must have the revoked privilege (for example, the SELECT privilege on table tbl1) and the GRANT OPTION privilege.

Privileges required

• When a privilege does not have the GRANT OPTION, the revocation operation does not cascade. For example, if user1 grants a privilege to user2, revoking the privilege from user1 does not automatically revoke the corresponding privilege from user2. However, if a privilege has the GRANT OPTION, the revocation operation cascades.

• To revoke the ALL PRIVILEGES or GRANT OPTION privilege, the current user must have the global GRANT OPTION privilege or the UPDATE or DELETE privilege on the privilege table.

For more information about privileges in OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

/*Revoke object privileges*/

REVOKE {obj_privilege [, obj_privilege...]} ON obj_clause FROM {user_name [, user_name ...]}

/*Revoke system privileges*/
REVOKE {system_privilege_list | ALL PRIVILEGES} FROM user_name [, user_name ...];

REVOKE ALL [PRIVILEGES], GRANT_OPTION FROM user_name [, user_name ...];

/*Revoke roles*/
REVOKE role_name [, role_name ...] FROM user_name;

obj_clause:  
      * [. *]
    | relation_name . {*  | relation_name}
    | [DIRECTORY] relation_name
    | CATALOG external_catalog_name

Parameters

obj_clause

• * [. *]: Refers to all tables in all databases.

  The example is as follows:

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

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

• relation_name . {* | relation_name}: Refers to all tables in the specified database. db_name represents the name of the specified database.

  The example is as follows:

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

  REVOKE INSERT ON 'test'.* FROM 'user003';
  

• [DIRECTORY] relation_name: represents a specific table in a specific database.

  The example is as follows:

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

  REVOKE SELECT ON 'test'.'tbl2' FROM 'user003';
  

• CATALOG external_catalog_name: Represents the catalog in the Catalog.

  The example is as follows:

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

  REVOKE USE CATALOG ON CATALOG test_odps_catalog FROM user004;
  

The following table describes the privilege types that can be revoked.

Privilege types

Examples

1. Create a user and a role.

   CREATE USER user1 IDENTIFIED BY password;
   CREATE ROLE role1;
   CREATE TABLE user1.tbl1 (id INT);
   

2. Grant privileges to the role.

   GRANT SELECT ON user1.tbl1 TO role1;
   

3. Grant the role to the user.

   GRANT role1 TO user1;
   

4. Revoke the privileges granted to the role on the table.

   REVOKE SELECT ON user1.tbl1 FROM role1;
   

The preceding steps enable you to grant and revoke privileges.

The following examples show SQL statements for revoking all privileges, object privileges, and system privileges.

• Revoke all privileges from user user1.

obclient> REVOKE ALL PRIVILEGES FROM user1;
Query OK, 0 rows affected

• Revoke the SELECT privilege on the user1.tbl1 table from role1.

obclient> REVOKE SELECT ON user1.tbl1 FROM role1;
Query OK, 0 rows affected

• Revoke the DROP ANY TABLE privilege from users user1 and user2.

obclient> REVOKE DROP ANY TABLE FROM user1,user2;
Query OK, 0 rows affected

References

• For more information about how to query user privileges, see Query user privileges.

• For more information about how to query roles and privileges in a role, see Query roles.

• You can query the information about the created users in the dba_users table. For more information about the dba_users table, see DBA_USERS.