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
user1wants to grant theSELECTprivilege on thetbl1table touser2,user1must have theGRANT OPTIONprivilege and theSELECTprivilege on tbl1.- To grant system privileges or roles to a user, you must have the
GRANT OPTIONprivilege 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
nyuser.obclient>GRANT ALL PRIVILEGES TO ny; Query OK, 0 rows affectedView the privileges of the
nyuser.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 setGrant a user all the privileges on a table.
Grant the
nyuser all privileges on thedws_nytable.obclient> GRANT ALL PRIVILEGES ON dws_ny to ny with GRANT OPTION; Query OK, 0 rows affectedView the privileges on the
dws_nytable that are granted to thenyuser.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 setNote
Both the
SYSuser and theALL_TAB_PRIVSuser can query the privileges on thedws_nytable in theALL_TAB_PRIVStable.
Revoke privileges
Prerequisites
When you revoke an object privilege, you must have the privilege to be revoked. For example, for
user1to revoke theSELECTprivilege on tablet1fromuser2,user1must have theSELECTprivilege on tablet1. In addition, you must have theGRANT OPTIONorGRANT ANY OBJECT PRIVILEGEprivilege.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, orGRANT ANY ROLEprivilege.To revoke the
ALL PRIVILEGESandGRANT OPTIONprivileges, you must have the globalGRANT OPTIONprivilege or theUPDATEandDELETEprivileges 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:
|
| 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
UPDATEprivilege from a user.Revoke the
UPDATEprivilege on thedws_nytable in thedata_nydatabase from thenyuser.obclient> revoke update on dws_ny from ny; Query OK, 0 rows affectedView the privileges of the
nyuser on thedws_nytable.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 setNote
Both the
rootuser andnyuser can query the privileges on thedws_nytable in theTABLE_PRIVILEGEStable.
Revoke the
UPDATEprivilege from a user.Revoke the
UPDATEprivilege from thenyuser.obclient> revoke UPDATE on *.* from ny; Query OK, 0 rows affectedView the current privileges of the
nyuser.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