About table privileges|V4.0.0|OceanBase Database| docs|Distributed Database

This topic describes how to grant and revoke table privileges.

Grant privileges

Overview

System administrators can use this statement to grant object privileges, system privileges, and roles to users.

Note

To grant object privileges, you must be the owner of the object, or have the GRANT OPTION privilege and the privileges to be granted. For example, if user1 wants to grant the SELECT privilege on the tbl1 table to user2, user1 must have the GRANT OPTION privilege and the SELECT privilege on tbl1.

To grant system privileges or roles to a user, you must have the GRANT OPTION privilege and the privileges or roles to be granted.

After a user is granted a role, the privileges of the role takes effect only after the user reconnects to OceanBase Database.

The privileges directly granted to a user take effect immediately.

Syntax

/*Grant object privileges*/
GRANT obj_with_col_priv_list
    ON obj_clause TO grant_list [WITH GRANT OPTION];

obj_with_col_priv_list:
    obj_with_col_priv
    | obj_with_col_priv_list, obj_with_col_priv

obj_with_col_priv:
    obj_privilege [column_list]

obj_privilege:
    ALTER
    | DELETE
    | INDEX
    | INSERT
    | SELECT
    | UPDATE
    | REFERENCES
    | EXECUTE

obj_clause:
    relation_name
    | relation_name '.' relation_name
    | DIRECTORY relation_name

grantee_list:
    grantee_user_list[,grantee_role_list]

grantee_user_list:
    user_name[,user_name...]

grantee_role_list:
    role_name [, role_name ...]

/*Grant system privileges*/
GRANT {system_privilege_list | ALL PRIVILEGES}
    TO grantee_list [IDENTIFIED BY password];

system_privilege_list:
    system_privilege [, system_privilege ...]

grantee_user_list:
    user_name[,user_name...]

grantee_role_list:
    role_name [, role_name ...]

system_privilege:
    CREATE SESSION
    | EXEMPT REDACTION POLICY
    | SYSDBA
    | SYSOPER
    | SYSBACKUP
    | CREATE TABLE
    | CREATE ANY TABLE
    | ALTER ANY TABLE
    | BACKUP ANY TABLE
    | DROP ANY TABLE
    | LOCK ANY TABLE
    | COMMENT ANY TABLE
    | SELECT ANY TABLE
    | INSERT ANY TABLE
    | UPDATE ANY TABLE
    | DELETE ANY TABLE
    | FLASHBACK ANY TABLE
    | CREATE ROLE
    | DROP ANY ROLE
    | GRANT ANY ROLE
    | ALTER ANY ROLE
    | AUDIT ANY
    | GRANT ANY PRIVILEGE
    | GRANT ANY OBJECT PRIVILEGE
    | CREATE ANY INDEX
    | ALTER ANY INDEX
    | DROP ANY INDEX
    | CREATE ANY VIEW
    | DROP ANY VIEW
    | CREATE VIEW
    | SELECT ANY DICTIONARY
    | CREATE PROCEDURE
    | CREATE ANY PROCEDURE
    | ALTER ANY PROCEDURE
    | DROP ANY PROCEDURE
    | EXECUTE ANY PROCEDURE
    | CREATE SYNONYM
    | CREATE ANY SYNONYM
    | DROP ANY SYNONYM
    | CREATE PUBLIC SYNONYM
    | DROP PUBLIC SYNONYM
    | CREATE SEQUENCE
    | CREATE ANY SEQUENCE
    | ALTER ANY SEQUENCE
    | DROP ANY SEQUENCE
    | SELECT ANY SEQUENCE
    | CREATE TRIGGER
    | CREATE ANY TRIGGER
    | ALTER ANY TRIGGER
    | DROP ANY TRIGGER
    | CREATE PROFILE
    | ALTER PROFILE
    | DROP PROFILE
    | CREATE USER
    | ALTER USER
    | DROP USER
    | CREATE TYPE
    | CREATE ANY TYPE
    | ALTER ANY TYPE
    | DROP ANY TYPE
    | EXECUTE ANY TYPE
    | UNDER ANY TYPE
    | PURGE DBA_RECYCLEBIN
    | CREATE ANY OUTLINE
    | ALTER ANY OUTLINE
    | DROP ANY OUTLINE
    | SYSKM
    | CREATE TABLESPACE
    | ALTER TABLESPACE
    | DROP TABLESPACE
    | SHOW PROCESS
    | ALTER SYSTEM
    | CREATE DATABASE LINK
    | CREATE PUBLIC DATABASE LINK
    | DROP DATABASE LINK
    | ALTER SESSION
    | ALTER DATABASE

/*Grant roles.*/
GRANT role_list TO grantee_list [IDENTIFIED BY password][WITH ADMIN OPTION];

role_list:
    role_name [, role_name ...]

grantee_list:
    grantee_user_list[,grantee_role_list]

grantee_user_list:
    user_name[,user_name...]

grantee_role_list:
    role_name [, role_name ...]

Parameters

Parameter	Description
obj_with_col_priv	The type of the privilege to be granted. For information about the specific privilege types and their description, see the following table. To grant multiple privileges to a user, separate the types with commas (,).
system_privilege	Specifies the type of the system privilege to be granted. To grant multiple privileges to a user, separate the types with commas (,).
obj_clause	The object involved in object privileges.
WITH GRANT OPTION	Specifies whether to enable privilege delegation. When privilege delegation is enabled, grant revocation extends to dependent users.
WITH ADMIN OPTION	Specifies whether to enable admin privilege delegation. When admin privilege delegation is enabled, grant revocation does not extend to dependent users.
grantee_list	The list of objects to which privileges or roles are to be granted. Such objects can be roles or users.
user_name	The username.
role_name	The role name.

The following table lists the types of privileges that can be granted.

Privilege type	Description
ALL PRIVILEGES	All privileges except `GRANT OPTION`.
ALTER	The `ALTER TABLE` privilege.
CREATE	The `CREATE TABLE` privilege.
DELETE	The `DELETE` privilege.
DROP	The `DROP` privilege.
GRANT OPTION	The `GRANT OPTION` privilege.
INSERT	The `INSERT` privilege.
UPDATE	The `UPDATE` privilege.
SELECT	The `SELECT` privilege.
INDEX	The `CREATE INDEX` and `DROP INDEX` privileges.
REFERENCES	The privilege to create constraints on a table.
EXECUTE	The privilege to execute the precompiler program.
FLASHBACK	The `FLASHBACK` privilege.
READ	The `READ` privilege.
WRITE	The `WRITE` privilege.
CREATE SESSION	The privilege to connect to a database.
EXEMPT REDACTION POLICY	The privilege to bypass any existing redaction policies and to view data.
SYSDBA	The `SYSDBA` privilege.
SYSOPER	The `SYSOPER` privilege.
SYSBACKUP	The `SYSBACKUP` privilege.
CREATE TABLE	The privilege to create a table in the schema of a specified user.
CREATE ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
ALTER ANY TABLE	The privilege to modify a table in the schema of any user except the SYS user.
BACKUP ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
DROP ANY TABLE	The privilege to drop a table from the schema of any user except the SYS user.
LOCK ANY TABLE	The privilege to lock a table in the schema of any user except the SYS user.
COMMENT ANY TABLE	The privilege to comment a table in the schema of any user except the SYS user.
SELECT ANY TABLE	The privilege to view a table in the schema of any user except the SYS user.
INSERT ANY TABLE	The privilege to insert rows into a table in the schema of any user except the SYS user.
UPDATE ANY TABLE	The privilege to update rows of a table in the schema of any user except the SYS user.
DELETE ANY TABLE	The privilege to delete a table from the schema of any user except the SYS user.
FLASHBACK ANY TABLE	The privilege to flash back a table in the schema of any user except the SYS user.
CREATE ROLE	The privilege to create a role.
DROP ANY ROLE	The privilege to drop any role.
GRANT ANY ROLE	The privilege to grant any role.
ALTER ANY ROLE	The privilege to modify any role.
AUDIT ANY	The privilege to audit objects in the schema of any user except the SYS user.
GRANT ANY PRIVILEGE	The privilege to grant any system privilege.
GRANT ANY OBJECT PRIVILEGE	The privilege to grant any object privilege.
CREATE ANY INDEX	The privilege to create an index in the schema of any user except the SYS user.
ALTER ANY INDEX	The privilege to modify an index in the schema of any user except the SYS user.
DROP ANY INDEX	The privilege to drop an index from the schema of any user except the SYS user.
CREATE ANY VIEW	The privilege to create a view in the schema of any user except the SYS user.
DROP ANY VIEW	The privilege to drop an index from the schema of any user except the SYS user.
CREATE VIEW	The privilege to create a view in the schema of a specified user.
SELECT ANY DICTIONARY	The privilege to query a dictionary in the schema of a specified user.
CREATE PROCEDURE	The privilege to create a procedure in the schema of a specified user.
CREATE ANY PROCEDURE	The privilege to create a procedure in the schema of any user except the SYS user.
ALTER ANY PROCEDURE	The privilege to modify a procedure in the schema of any user except the SYS user.
DROP ANY PROCEDURE	The privilege to drop a procedure from the schema of any user except the SYS user.
EXECUTE ANY PROCEDURE	The privilege to execute a procedure in the schema of any user except the SYS user.
CREATE SYNONYM	The privilege to create a synonym in the schema of a specified user.
CREATE ANY SYNONYM	The privilege to create a synonym in the schema of any user except the SYS user.
DROP ANY SYNONYM	The privilege to drop a synonym from the schema of any user except the SYS user.
CREATE PUBLIC SYNONYM	The privilege to create a public synonym.
DROP PUBLIC SYNONYM	The privilege to drop a public synonym.
CREATE SEQUENCE	The privilege to create a sequence in the schema of a specified user.
CREATE ANY SEQUENCE	The privilege to create a sequence in the schema of any user except the SYS user.
ALTER ANY SEQUENCE	The privilege to modify a sequence in the schema of any user except the SYS user.
DROP ANY SEQUENCE	The privilege to drop a sequence from the schema of any user except the SYS user.
SELECT ANY SEQUENCE	The privilege to query a sequence in the schema of any user except the SYS user.
CREATE TRIGGER	The privilege to create a trigger in the schema of a specified user.
CREATE ANY TRIGGER	The privilege to create a trigger in the schema of any user except the SYS user.
ALTER ANY TRIGGER	The privilege to modify a trigger in the schema of any user except the SYS user.
DROP ANY TRIGGER	The privilege to drop a trigger in the schema of any user except the SYS user.
CREATE PROFILE	The privilege to create a profile.
ALTER PROFILE	The privilege to modify a profile.
DROP PROFILE	The privilege to drop a profile.
CREATE USER	The privilege to create a user.
ALTER USER	The privilege to modify a user.
DROP USER	The privilege to drop a user.
CREATE TYPE	The privilege to create a type in the schema of a specified user.
CREATE ANY TYPE	The privilege to create a type in the schema of any user except the SYS user.
ALTER ANY TYPE	The privilege to modify a type in the schema of any user except the SYS user.
DROP ANY TYPE	The privilege to drop a type from the schema of any user except the SYS user.
EXECUTE ANY TYPE	The privilege to execute a type in the schema of any user except the SYS user.
UNDER ANY TYPE	The privilege to create subtypes under a type in the schema of any user except the SYS user.
PURGE DBA_RECYCLEBIN	The privilege to purge all objects from the recycle bin.
CREATE ANY OUTLINE	The privilege to create an outline in the schema of any user except the SYS user.
ALTER ANY OUTLINE	The privilege to modify an outline in the schema of any user except the SYS user.
DROP ANY OUTLINE	The privilege to drop an outline from the schema of any user except the SYS user.
SYSKM	The `SYSKM` privilege.
CREATE TABLESPACE	The privilege to create a tablespace.
ALTER TABLESPACE	The privilege to modify a tablespace.
DROP TABLESPACE	The privilege to drop a tablespace.
ALTER SYSTEM	The `ALTER SYSTEM` privilege.
CREATE DATABASE LINK	The privilege to create a database link in the schema of a specified user.
DROP DATABASE LINK	The privilege to drop a database link from the schema of a specified user.
ALTER SESSION	The privilege to modify a session.
ALTER DATABASE	The privilege to modify a database.

Examples

Grant all privileges to a user.

Grant all privileges to the ny user.
```
obclient>GRANT ALL PRIVILEGES TO ny;
Query OK, 0 rows affected
```

View the privileges of the ny user.

obclient>SELECT * FROM USER_SYS_PRIVS;
+----------+-----------------------------+--------------+
| USERNAME | PRIVILEGE                   | ADMIN_OPTION |
+----------+-----------------------------+--------------+
| SYS      | CREATE SESSION              | YES          |
| SYS      | CREATE TABLE                | YES          |
| SYS      | CREATE ANY TABLE            | YES          |
| SYS      | ALTER ANY TABLE             | YES          |
| SYS      | BACKUP ANY TABLE            | YES          |
| SYS      | DROP ANY TABLE              | YES          |
| SYS      | LOCK ANY TABLE              | YES          |
| SYS      | COMMENT ANY TABLE           | YES          |
| SYS      | SELECT ANY TABLE            | YES          |
| SYS      | INSERT ANY TABLE            | YES          |
| SYS      | UPDATE ANY TABLE            | YES          |
| SYS      | DELETE ANY TABLE            | YES          |
| SYS      | FLASHBACK ANY TABLE         | YES          |
| SYS      | CREATE ROLE                 | YES          |
| SYS      | DROP ANY ROLE               | YES          |
| SYS      | GRANT ANY ROLE              | YES          |
| SYS      | ALTER ANY ROLE              | YES          |
| SYS      | AUDIT ANY                   | YES          |
| SYS      | GRANT ANY PRIVILEGE         | YES          |
| SYS      | GRANT ANY OBJECT PRIVILEGE  | YES          |
| SYS      | CREATE ANY INDEX            | YES          |
| SYS      | ALTER ANY INDEX             | YES          |
| SYS      | DROP ANY INDEX              | YES          |
| SYS      | CREATE ANY VIEW             | YES          |
| SYS      | DROP ANY VIEW               | YES          |
| SYS      | CREATE VIEW                 | YES          |
| SYS      | SELECT ANY DICTIONARY       | YES          |
| SYS      | CREATE PROCEDURE            | YES          |
| SYS      | CREATE ANY PROCEDURE        | YES          |
| SYS      | ALTER ANY PROCEDURE         | YES          |
| SYS      | DROP ANY PROCEDURE          | YES          |
| SYS      | EXECUTE ANY PROCEDURE       | YES          |
| SYS      | CREATE SYNONYM              | YES          |
| SYS      | CREATE ANY SYNONYM          | YES          |
| SYS      | DROP ANY SYNONYM            | YES          |
| SYS      | CREATE PUBLIC SYNONYM       | YES          |
| SYS      | DROP PUBLIC SYNONYM         | YES          |
| SYS      | CREATE SEQUENCE             | YES          |
| SYS      | CREATE ANY SEQUENCE         | YES          |
| SYS      | ALTER ANY SEQUENCE          | YES          |
| SYS      | DROP ANY SEQUENCE           | YES          |
| SYS      | SELECT ANY SEQUENCE         | YES          |
| SYS      | CREATE TRIGGER              | YES          |
| SYS      | CREATE ANY TRIGGER          | YES          |
| SYS      | ALTER ANY TRIGGER           | YES          |
| SYS      | DROP ANY TRIGGER            | YES          |
| SYS      | CREATE PROFILE              | YES          |
| SYS      | ALTER PROFILE               | YES          |
| SYS      | DROP PROFILE                | YES          |
| SYS      | CREATE USER                 | YES          |
| SYS      | BECOME USER                 | YES          |
| SYS      | ALTER USER                  | YES          |
| SYS      | DROP USER                   | YES          |
| SYS      | CREATE TYPE                 | YES          |
| SYS      | CREATE ANY TYPE             | YES          |
| SYS      | ALTER ANY TYPE              | YES          |
| SYS      | DROP ANY TYPE               | YES          |
| SYS      | EXECUTE ANY TYPE            | YES          |
| SYS      | UNDER ANY TYPE              | YES          |
| SYS      | PURGE DBA_RECYCLEBIN        | YES          |
| SYS      | CREATE ANY OUTLINE          | YES          |
| SYS      | ALTER ANY OUTLINE           | YES          |
| SYS      | DROP ANY OUTLINE            | YES          |
| SYS      | SYSKM                       | YES          |
| SYS      | CREATE TABLESPACE           | YES          |
| SYS      | ALTER TABLESPACE            | YES          |
| SYS      | DROP TABLESPACE             | YES          |
| SYS      | SHOW PROCESS                | YES          |
| SYS      | ALTER SYSTEM                | YES          |
| SYS      | CREATE DATABASE LINK        | YES          |
| SYS      | CREATE PUBLIC DATABASE LINK | YES          |
| SYS      | DROP DATABASE LINK          | YES          |
| SYS      | ALTER SESSION               | YES          |
| SYS      | ALTER DATABASE              | YES          |
| SYS      | CREATE ANY DIRECTORY        | YES          |
| SYS      | DROP ANY DIRECTORY          | YES          |
+----------+-----------------------------+--------------+
76 rows in set

Grant a user all the privileges on a table.

Grant the ny user all privileges on the dws_ny table.

obclient> GRANT ALL PRIVILEGES ON dws_ny to ny with GRANT OPTION;
Query OK, 0 rows affected

View the privileges on the dws_ny table that are granted to the ny user.

obclient> SELECT * FROM ALL_TAB_PRIVS;
+---------+---------+--------------+------------+------------+-----------+-----------+
| GRANTOR | GRANTEE | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE  | GRANTABLE | HIERARCHY |
+---------+---------+--------------+------------+------------+-----------+-----------+
| SYS     | NY      | SYS          | DWS_NY     | ALTER      | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | DELETE     | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | INDEX      | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | INSERT     | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | SELECT     | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | REFERENCES | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | FLASHBACK  | YES       | NO        |
+---------+---------+--------------+------------+------------+-----------+-----------+
7 rows in set

Note

Both the SYS user and the ALL_TAB_PRIVS user can query the privileges on the dws_ny table in the ALL_TAB_PRIVS table.

Revoke privileges

Prerequisites

When you revoke an object privilege, you must have the privilege to be revoked. For example, for user1 to revoke the SELECT privilege on table t1 from user2, user1 must have the SELECT privilege on table t1. In addition, you must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege.
When you revoke a system privilege or a role, you must have the privilege or role to be revoked and have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege.
To revoke the ALL PRIVILEGES and GRANT OPTION privileges, you must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the table.

Syntax

Syntax for revoking object privileges:

REVOKE obj_privileges
  ON obj_clause FROM user_list;

user_list:
  user [, user ...]

obj_privileges:
  obj_privilege [, obj_privilege ...]

obj_privilege:
    ALTER
  | DELETE
  | INDEX
  | INSERT
  | SELECT
  | UPDATE
  | REFERENCES
  | EXECUTE

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

relation_name:
  STR_VALUE

Syntax for revoking system privileges:

REVOKE {system_privilege_list | ALL PRIVILEGES}
  FROM user_list;
REVOKE ALL [PRIVILEGES], GRANT_OPTION FROM user_list;

system_privilege_list:
  system_privilege [, system_privilege ...]

system_privilege:
  CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

Parameters

Parameter	Description
obj_privileges	Specifies the type of the object privilege to be revoked. For information about the specific privilege types and their description, see the following table. To revoke multiple types of privileges, separate them with commas (,).
system_privilege	Specifies the type of the system privilege to be revoked. To revoke multiple types of privileges, separate them with commas (,).
obj_clause	The level of the privilege to be revoked. `relation_name` specifies the name of the object. Privileges can be divided into the following levels: Global: Privileges at this level apply to all databases. Database level: Privileges at this level apply to all objects in a specified database. Table level: Privileges at this level apply to all columns in a specified table.
role_list	The role list.

Privileges that can be revoked

Privilege type	Description
ALL PRIVILEGES	All privileges except `GRANT OPTION`.
ALTER	The `ALTER TABLE` privilege.
CREATE	The `CREATE TABLE` privilege.
DELETE	The `DELETE` privilege.
DROP	The `DROP` privilege.
GRANT OPTION	The `GRANT OPTION` privilege.
INSERT	The `INSERT` privilege.
UPDATE	The `UPDATE` privilege.
SELECT	The `SELECT` privilege.
INDEX	The `CREATE INDEX` and `DROP INDEX` privileges.
SHOW VIEW	The `SHOW CREATE VIEW` privilege.
SHOW DATABASES	The global `SHOW DATABASES` privilege.
SUPER	The `SET GLOBAL` privilege for modifying global system parameters.
REFERENCES	The privilege to create constraints on a table.
EXECUTE	The privilege to execute the precompiler program.
FLASHBACK	The `FLASHBACK` privilege.
READ	The `READ` privilege.
WRITE	The `WRITE` privilege.
CREATE SESSION	The privilege to connect to a database.
EXEMPT REDACTION POLICY	The privilege to bypass any existing redaction policies and to view data.
SYSDBA	The `SYSDBA` privilege.
SYSOPER	The `SYSOPER` privilege.
SYSBACKUP	The `SYSBACKUP` privilege.
CREATE TABLE	The privilege to create a table in the schema of a specified user.
CREATE ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
ALTER ANY TABLE	The privilege to modify a table in the schema of any user except the SYS user.
BACKUP ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
DROP ANY TABLE	The privilege to drop a table from the schema of any user except the SYS user.
LOCK ANY TABLE	The privilege to lock a table in the schema of any user except the SYS user.
COMMENT ANY TABLE	The privilege to comment a table in the schema of any user except the SYS user.
SELECT ANY TABLE	The privilege to view a table in the schema of any user except the SYS user.
INSERT ANY TABLE	The privilege to insert rows into a table in the schema of any user except the SYS user.
UPDATE ANY TABLE	The privilege to update rows of a table in the schema of any user except the SYS user.
DELETE ANY TABLE	The privilege to delete a table from the schema of any user except the SYS user.
FLASHBACK ANY TABLE	The privilege to flash back a table in the schema of any user except the SYS user.
CREATE ROLE	The privilege to create a role.
DROP ANY ROLE	The privilege to drop any role.
GRANT ANY ROLE	The privilege to grant any role.
ALTER ANY ROLE	The privilege to modify any role.
AUDIT ANY	The privilege to audit objects in the schema of any user except the SYS user.
GRANT ANY PRIVILEGE	The privilege to grant any system privilege.
GRANT ANY OBJECT PRIVILEGE	The privilege to grant any object privilege.
CREATE ANY INDEX	The privilege to create an index in the schema of any user except the SYS user.
ALTER ANY INDEX	The privilege to modify an index in the schema of any user except the SYS user.
DROP ANY INDEX	The privilege to drop an index from the schema of any user except the SYS user.
CREATE ANY VIEW	The privilege to create a view in the schema of any user except the SYS user.
DROP ANY VIEW	The privilege to drop an index from the schema of any user except the SYS user.
CREATE VIEW	The privilege to create a view in the schema of a specified user.
SELECT ANY DICTIONARY	The privilege to query a dictionary in the schema of a specified user.
CREATE PROCEDURE	The privilege to create a procedure in the schema of a specified user.
CREATE ANY PROCEDURE	The privilege to create a procedure in the schema of any user except the SYS user.
ALTER ANY PROCEDURE	The privilege to modify a procedure in the schema of any user except the SYS user.
DROP ANY PROCEDURE	The privilege to drop a procedure from the schema of any user except the SYS user.
EXECUTE ANY PROCEDURE	The privilege to execute a procedure in the schema of any user except the SYS user.
CREATE SYNONYM	The privilege to create a synonym in the schema of a specified user.
CREATE ANY SYNONYM	The privilege to create a synonym in the schema of any user except the SYS user.
DROP ANY SYNONYM	The privilege to drop a synonym from the schema of any user except the SYS user.
CREATE PUBLIC SYNONYM	The privilege to create a public synonym.
DROP PUBLIC SYNONYM	The privilege to drop a public synonym.
CREATE SEQUENCE	The privilege to create a sequence in the schema of a specified user.
CREATE ANY SEQUENCE	The privilege to create a sequence in the schema of any user except the SYS user.
ALTER ANY SEQUENCE	The privilege to modify a sequence in the schema of any user except the SYS user.
DROP ANY SEQUENCE	The privilege to drop a sequence from the schema of any user except the SYS user.
SELECT ANY SEQUENCE	The privilege to query a sequence in the schema of any user except the SYS user.
CREATE TRIGGER	The privilege to create a trigger in the schema of a specified user.
CREATE ANY TRIGGER	The privilege to create a trigger in the schema of any user except the SYS user.
ALTER ANY TRIGGER	The privilege to modify a trigger in the schema of any user except the SYS user.
DROP ANY TRIGGER	The privilege to drop a trigger in the schema of any user except the SYS user.
CREATE PROFILE	The privilege to create a profile.
ALTER PROFILE	The privilege to modify a profile.
DROP PROFILE	The privilege to drop a profile.
CREATE USER	The privilege to create a user.
ALTER USER	The privilege to modify a user.
DROP USER	The privilege to drop a user.
CREATE TYPE	The privilege to create a type in the schema of a specified user.
CREATE ANY TYPE	The privilege to create a type in the schema of any user except the SYS user.
ALTER ANY TYPE	The privilege to modify a type in the schema of any user except the SYS user.
DROP ANY TYPE	The privilege to drop a type from the schema of any user except the SYS user.
EXECUTE ANY TYPE	The privilege to execute a type in the schema of any user except the SYS user.
UNDER ANY TYPE	The privilege to create subtypes under a type in the schema of any user except the SYS user.
PURGE DBA_RECYCLEBIN	The privilege to purge all objects from the recycle bin.
CREATE ANY OUTLINE	The privilege to create an outline in the schema of any user except the SYS user.
ALTER ANY OUTLINE	The privilege to modify an outline in the schema of any user except the SYS user.
DROP ANY OUTLINE	The privilege to drop an outline from the schema of any user except the SYS user.
SYSKM	The `SYSKM` privilege.
CREATE TABLESPACE	The privilege to create a tablespace.
ALTER TABLESPACE	The privilege to modify a tablespace.
DROP TABLESPACE	The privilege to drop a tablespace.
ALTER SYSTEM	The `ALTER SYSTEM` privilege.
CREATE DATABASE LINK	The privilege to create a database link in the schema of a specified user.
CREATE PUBLIC DATABASE LINK	The privilege to create a public database link.
DROP DATABASE LINK	The privilege to drop a database link from the schema of a specified user.
ALTER SESSION	The privilege to modify a session.
ALTER DATABASE	The privilege to modify a database.

Examples

Revoke the UPDATE privilege from a user.

Revoke the UPDATE privilege on the dws_ny table in the data_ny database from the ny user.
```
obclient> revoke update on dws_ny from ny;
Query OK, 0 rows affected
```

View the privileges of the ny user on the dws_ny table.

obclient> SELECT * FROM ALL_TAB_PRIVS;
+---------+---------+--------------+------------+------------+-----------+-----------+
| GRANTOR | GRANTEE | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE  | GRANTABLE | HIERARCHY |
+---------+---------+--------------+------------+------------+-----------+-----------+
| SYS     | NY      | SYS          | DWS_NY     | ALTER      | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | DELETE     | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | INDEX      | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | INSERT     | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | SELECT     | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | REFERENCES | YES       | NO        |
| SYS     | NY      | SYS          | DWS_NY     | FLASHBACK  | YES       | NO        |
+---------+---------+--------------+------------+------------+-----------+-----------+
7 rows in set

Note

Both the root user and ny user can query the privileges on the dws_ny table in the TABLE_PRIVILEGES table.

Revoke the UPDATE privilege from a user.

Revoke the UPDATE privilege from the ny user.

obclient> revoke UPDATE on *.* from ny;
Query OK, 0 rows affected

View the current privileges of the ny user.

obclient> SELECT * FROM USER_SYS_PRIVS;
+----------+-----------------------------+--------------+
| USERNAME | PRIVILEGE                   | ADMIN_OPTION |
+----------+-----------------------------+--------------+
| SYS      | CREATE SESSION              | YES          |
| SYS      | CREATE TABLE                | YES          |
| SYS      | CREATE ANY TABLE            | YES          |
| SYS      | ALTER ANY TABLE             | YES          |
| SYS      | BACKUP ANY TABLE            | YES          |
| SYS      | DROP ANY TABLE              | YES          |
| SYS      | LOCK ANY TABLE              | YES          |
| SYS      | COMMENT ANY TABLE           | YES          |
| SYS      | SELECT ANY TABLE            | YES          |
| SYS      | INSERT ANY TABLE            | YES          |
| SYS      | UPDATE ANY TABLE            | YES          |
| SYS      | DELETE ANY TABLE            | YES          |
| SYS      | FLASHBACK ANY TABLE         | YES          |
| SYS      | CREATE ROLE                 | YES          |
| SYS      | DROP ANY ROLE               | YES          |
| SYS      | GRANT ANY ROLE              | YES          |
| SYS      | ALTER ANY ROLE              | YES          |
| SYS      | AUDIT ANY                   | YES          |
| SYS      | GRANT ANY PRIVILEGE         | YES          |
| SYS      | GRANT ANY OBJECT PRIVILEGE  | YES          |
| SYS      | CREATE ANY INDEX            | YES          |
| SYS      | ALTER ANY INDEX             | YES          |
| SYS      | DROP ANY INDEX              | YES          |
| SYS      | CREATE ANY VIEW             | YES          |
| SYS      | DROP ANY VIEW               | YES          |
| SYS      | CREATE VIEW                 | YES          |
| SYS      | SELECT ANY DICTIONARY       | YES          |
| SYS      | CREATE PROCEDURE            | YES          |
| SYS      | CREATE ANY PROCEDURE        | YES          |
| SYS      | ALTER ANY PROCEDURE         | YES          |
| SYS      | DROP ANY PROCEDURE          | YES          |
| SYS      | EXECUTE ANY PROCEDURE       | YES          |
| SYS      | CREATE SYNONYM              | YES          |
| SYS      | CREATE ANY SYNONYM          | YES          |
| SYS      | DROP ANY SYNONYM            | YES          |
| SYS      | CREATE PUBLIC SYNONYM       | YES          |
| SYS      | DROP PUBLIC SYNONYM         | YES          |
| SYS      | CREATE SEQUENCE             | YES          |
| SYS      | CREATE ANY SEQUENCE         | YES          |
| SYS      | ALTER ANY SEQUENCE          | YES          |
| SYS      | DROP ANY SEQUENCE           | YES          |
| SYS      | SELECT ANY SEQUENCE         | YES          |
| SYS      | CREATE TRIGGER              | YES          |
| SYS      | CREATE ANY TRIGGER          | YES          |
| SYS      | ALTER ANY TRIGGER           | YES          |
| SYS      | DROP ANY TRIGGER            | YES          |
| SYS      | CREATE PROFILE              | YES          |
| SYS      | ALTER PROFILE               | YES          |
| SYS      | DROP PROFILE                | YES          |
| SYS      | CREATE USER                 | YES          |
| SYS      | BECOME USER                 | YES          |
| SYS      | ALTER USER                  | YES          |
| SYS      | DROP USER                   | YES          |
| SYS      | CREATE TYPE                 | YES          |
| SYS      | CREATE ANY TYPE             | YES          |
| SYS      | ALTER ANY TYPE              | YES          |
| SYS      | DROP ANY TYPE               | YES          |
| SYS      | EXECUTE ANY TYPE            | YES          |
| SYS      | UNDER ANY TYPE              | YES          |
| SYS      | PURGE DBA_RECYCLEBIN        | YES          |
| SYS      | CREATE ANY OUTLINE          | YES          |
| SYS      | ALTER ANY OUTLINE           | YES          |
| SYS      | DROP ANY OUTLINE            | YES          |
| SYS      | SYSKM                       | YES          |
| SYS      | CREATE TABLESPACE           | YES          |
| SYS      | ALTER TABLESPACE            | YES          |
| SYS      | DROP TABLESPACE             | YES          |
| SYS      | SHOW PROCESS                | YES          |
| SYS      | ALTER SYSTEM                | YES          |
| SYS      | CREATE DATABASE LINK        | YES          |
| SYS      | CREATE PUBLIC DATABASE LINK | YES          |
| SYS      | DROP DATABASE LINK          | YES          |
| SYS      | ALTER SESSION               | YES          |
| SYS      | ALTER DATABASE              | YES          |
| SYS      | CREATE ANY DIRECTORY        | YES          |
| SYS      | DROP ANY DIRECTORY          | YES          |
+----------+-----------------------------+--------------+
76 rows in set