REVOKE

Description

This statement allows a system administrator to revoke some privileges from a user.

Limitations and considerations

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

Privilege requirements

• When a user revokes a privilege that they do not have the GRANT OPTION for, the revocation does not cascade. For example, if user1 grants a privilege to user2, and then user1 revokes the privilege, user2 keeps the privilege. If a user has the GRANT OPTION, the revocation cascades.

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

For more information about privileges in OceanBase Database, see Privilege types in Oracle 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

Parameter explanation

obj_clause

• * [. *]: represents all tables in all databases.

  Here is an example:

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

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

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

  Here is an example:

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

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

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

  Here is an example:

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

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

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

The following table describes the privilege types.

Examples

1. Create users and roles.

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

2. Grant privileges to roles.

   GRANT SELECT ON user1.tbl1 TO role1;
   

3. Grant roles to users.

   GRANT role1 TO user1;
   

4. Revoke privileges from a table for a role.

   REVOKE SELECT ON user1.tbl1 FROM role1;
   

You can perform both privilege granting and revocation operations by following the preceding steps.

The following examples show the SQL statements for revocation operations: revocation of all privileges, object privileges, and system privileges.

• Revoke all privileges from the user user1.

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

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

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

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

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

References

• For more information about how to view user privileges, see View user privileges.

• For more information about how to view roles and the privileges in the roles, see View roles.

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