REVOKE Statement

Revoke privileges from users or roles.

Syntax:

Roles:
   REVOKE role FROM {user, | role, |PUBLIC}

System Privs:
   REVOKE system_priv(s) FROM {user, | role, |PUBLIC}

   REVOKE ALL FROM {user, | role, |PUBLIC}

Object Privs:
   REVOKE object_priv [(column1, column2..)] ON [schema.]object 
         FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE] 

   REVOKE object_priv [(column1, column2..)] ON [schema.]object
         FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

   REVOKE object_priv [(column1, column2..)] ON DIRECTORY directory_name
         FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

   REVOKE object_priv [(column1, column2..)] ON JAVA [RE]SOURCE [schema.]object
         FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

key:
   object_privs
   ALTER, DELETE, EXECUTE, INDEX, INSERT, 
   REFERENCES, SELECT, UPDATE, ALL PRIVILEGES

   system_privs
   ALTER ANY INDEX, BECOME USER, CREATE TABLE, DROP ANY VIEW
   RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE
   plus too many others to list here

   roles
   Standard Oracle roles -  
   SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE
   plus any user defined roles you have available

FORCE, will revoke all privileges from a user-defined-type and mark it's dependent objects INVALID.

The roles CONNECT, RESOURCE and DBA are now deprecated (supported only for backwards compatibility) unless you are still running Oracle 6.0

Error ORA-01927 "cannot REVOKE privileges you did not grant" - This usually means you tried revoking permission from the table owner, e.g.
Oracle will not allow REVOKE select on USER1.Table1 from USER1 Owners of objects ALWAYS have full permissions on those objects. This is one reason it makes sense to place tables in one schema and the packaged prodecures used to access those tables in a separate schema.

"The most radical revolutionary will become a conservative the day after the revolution" ~ Hannah Arendt

Related Commands:

AUDIT
ALTER SESSION SET CURRENT_SCHEMA = schema
CREATE ROLE
GRANT

Related Views:

 DBA_COL_PRIVS        ALL_COL_PRIVS        USER_COL_PRIVS       COLUMN_PRIVILEGES
 ALL_COL_PRIVS_MADE   USER_COL_PRIVS_MADE
 ALL_COL_PRIVS_RECD   USER_COL_PRIVS_RECD
 DBA_ROLE_PRIVS                            USER_ROLE_PRIVS      ROLE_ROLE_PRIVS
 DBA_SYS_PRIVS                             USER_SYS_PRIVS       ROLE_SYS_PRIVS
 SESSION_PRIVS
 DBA_TAB_PRIVS        ALL_TAB_PRIVS        USER_TAB_PRIVS       TABLE_PRIVILEGES
 ROLE_TAB_PRIVS
 ALL_TAB_PRIVS_MADE   USER_TAB_PRIVS_MADE
 ALL_TAB_PRIVS_RECD   USER_TAB_PRIVS_RECD


© Copyright SS64.com 1999-2013
Some rights reserved