1 REVOKE_ANSI Removes privileges from the Oracle Rdb access control list granted by a specific user for a database object. Each entry in an ANSI/ISO-style access privilege set consists of an identifier and a list of privileges assigned to the identifier. o Each identifier specifies a user or the PUBLIC keyword. o The set of privileges specifies what operations that user or user group can perform on the database, table, column, module, procedure, function or sequence. For ANSI/ISO-style databases, the access privilege set is not order-dependent. The user matches the entry in the access privilege set, receives whatever privileges have been granted on the database object and receives the privileges defined for PUBLIC. A user without an entry in the access privilege set receives only the privileges defined for PUBLIC. The PUBLIC identifier always has an entry in the access control list, even if PUBLIC has no access to the database object. To create an entry or add privileges to an entry in the Oracle Rdb access control list for a a database object, see the GRANT_ ANSI statement. 2 Environment You can use the REVOKE statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of a nonstored procedure in a nonstored SQL module o In dynamic SQL as a statement to be dynamically executed 2 Format REVOKE -----------------------+ +---------------<-------------+ ++-> db-privs-ansi -------> ON DATABASE ALIAS -++-> -++-----------++ | |+--- , <-----+| || | +---> * -------+ || +-> table-privs-ansi ----> ON +----->----+--++-+-> +++------+| | +-> TABLE -+ || +-> -+|| || | |+--------- , <------+| || | +---------> * --------+ || +-> column-privs-ansi ---> ON COLUMN ---+-> +-------------+| | +-------- , <------+ || +-> module-privs-ansi ---> ON MODULE --++-> ++------------+| | |+-------- , <------+| || | +--------> * --------+ || +-> ext-routine-privs-ansi -+> ON FUNCTION --+++-> -+++| | +> ON PROCEDURE -+|+---------- , <---------+||| | +----------> * -----------+|| +-> sequence-privs-ansi -> ON SEQUENCE --++- > -++-------+| |+-------- , <---------+| | +--------> * -----------+ | +----------------------------<--------------------------------------------+ +-> revoke-ansi-from ------------------------------------------------------> db-privs-ansi = ----+---+-+-> SELECT -----+-+----+-> | | +-> INSERT -----+ | | | | +-> OPERATOR ---+ | | | | +-> DELETE -----+ | | | | +-> CREATE -----+ | | | | +-> ALTER ------+ | | | | +-> DROP -------+ | | | | +-> DBCTRL -----+ | | | | +-> DBADM ------+ | | | | +-> SHOW -------+ | | | | +-> REFERENCES -+ | | | | +-> UPDATE -----+ | | | | +-> SECURITY ---+ | | | | +-> DISTRIBTRAN + | | | +------- , <--------+ | +-------> ALL PRIVILEGES ----+ table-privs-ansi = -+--+-+-> SELECT ---------------------------------+-+-+--> | | +-> INSERT ---------------------------------+ | | | | +-> DELETE ---------------------------------+ | | | | +-> CREATE ---------------------------------+ | | | | +-> ALTER ----------------------------------+ | | | | +-> DROP -----------------------------------+ | | | | +-> DBCTRL ---------------------------------+ | | | | +-> SHOW -----------------------------------+ | | | | +-> REFERENCES +---------------------------+ | | | | | +> ( +> +> ) -+ | | | | | +----- , <-------+ | | | | | +-> UPDATE -+---------------------- -------+ | | | | +> ( +> --+--> ) -+ | | | | +----- , <---------+ | | | +------------------- , <------------------------+ | +------> ALL PRIVILEGES -----------------------------+ column-privs-ansi = ---+-+-+-> UPDATE -----+-+--+--> | | +-> REFERENCES -+ | | | +------- , <--------+ | +-----> ALL PRIVILEGES --+ module-privs-ansi = -+--+-+-> ALTER ------------------------------+-+--+--> | | +-> DBCTRL -----------------------------+ | | | | +-> DROP -------------------------------+ | | | | +-> EXECUTE ----------------------------+ | | | | +-> REFERENCES -------------------------+ | | | | +-> SHOW -------------------------------+ | | | +------------------- , <--------------------+ | +------> ALL PRIVILEGES --------------------------+ ext-routine-privs-ansi = -+--+-+-> ALTER ------------------------------+-+--+--> | | +-> DBCTRL -----------------------------+ | | | | +-> DROP -------------------------------+ | | | | +-> EXECUTE ----------------------------+ | | | | +-> REFERENCES -------------------------+ | | | | +-> SHOW -------------------------------+ | | | +------------------- , <--------------------+ | +------> ALL PRIVILEGES --------------------------+ sequence-privs-ansi = -+-+-+-> ALTER -------------------------------+-+--+---> | | +-> DBCTRL ------------------------------+ | | | | +-> DROP --------------------------------+ | | | | +-> REFERENCES --------------------------+ | | | | +-> SELECT ------------------------------+ | | | | +-> SHOW --------------------------------+ | | | +------- , <---------------------------------+ | +--> ALL PRIVILEGES ------------------------------+ revoke-ansi-from = --> FROM -+-+-> identifier-ansi-style ---++--> | +-> PUBLIC ------------------+| +------------- , <--------------+ identifier-ansi-style = ------> user-identifier --------> 2 Arguments 3 ALL_PRIVILEGES Specifies that SQL should revoke all privileges in the access privilege set entry. 3 FROM Syntax options: FROM identifier-ansi-style FROM PUBLIC Specifies the identifiers for the access privilege set entry to be modified or deleted. Specifying PUBLIC is equivalent to a wildcard specification of all user identifiers. The only identifiers are ones that translate to an OpenVMS user identification code (UIC). For more information about user identifiers, see the operating system documentation. Syntax options: ON DATABASE alias ON TABLE table-name ON COLUMN column-name ON MODULE module-name ON FUNCTION ext-routine-name ON PROCEDURE ext-routine-name ON SEQUENCE sequence-name Specifies whether the REVOKE statement applies to ACLs for database objects. You can specify a list of names for any form of the ON clause. You must qualify a column name with at least the associated table name. 3 ON Syntax options: ON DATABASE * ON MODULE * ON FUNCTION * ON PROCEDURE * ON SEQUENCE * Specifies whether the REVOKE statement applies to ACLs for all objects of the specified types. If privileges are denied for the operation on some objects, then the REVOKE is aborted. 3 privileges Types of privileges: db-privs-ansi table-privs-ansi column-privs-ansi module-privs-ansi ext-routine-privs-ansi sequence-privs-ansi Specifies the list of privileges you want to remove from an existing access privilege set entry. The operations permitted by a given privilege keyword differ, depending on whether it was granted for a database, table, column, module, routine, or sequence. The Privileges Table in the GRANT lists the privilege keywords and their meanings for databases, tables, modules, external routines and sequences. 3 user-identifier Uniquely identifies each user on the system. The user identifier consists of the standard OpenVMS user identification code (UIC), a group name, and a member name (user name). The group name is optional. The user identifier can be in either numeric or alphanumeric format. The following are all valid user identifiers that could identify the same user: K_JONES [SYSTEM3, K_JONES] [341,311] When Oracle Rdb creates a database, it automatically creates an access privilege set entry with the PUBLIC identifier, which grants all privileges except DBCTRL to any user. In access privilege set databases, the only wildcard allowed is the PUBLIC identifier. You cannot use more than one user identifier in a multiple identifier. 2 Examples Example 1: Managing User Access with the REVOKE statement SQL> attach 'filename DB$:ANSI_PERSONNEL'; SQL> SQL> -- examine current privileges SQL> show protection on database RDB$DBHANDLE; Protection on Alias RDB$DBHANDLE [DOC,DOC_WRITER]: With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP, DBCTRL,OPERATOR,DBADM,SECURITY,DISTRIBTRAN Without Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP, DBCTRL,OPERATOR,DBADM,SECURITY,DISTRIBTRAN [DOC,DOC_READER]: With Grant Option: NONE Without Grant Option: SELECT,CREATE [*,*]: With Grant Option: NONE Without Grant Option: NONE SQL> SQL> -- revoke selected privileges SQL> revoke CREATE on database alias RDB$DBHANDLE from DOC_READER; SQL> revoke DISTRIBTRAN on database alias RDB$DBHANDLE from DOC_WRITER; SQL> show protection on database RDB$DBHANDLE; Protection on Alias RDB$DBHANDLE [DOC,DOC_WRITER]: With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP, DBCTRL,OPERATOR,DBADM,SECURITY Without Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP, DBCTRL,OPERATOR,DBADM,SECURITY [DOC,DOC_READER]: With Grant Option: NONE Without Grant Option: SELECT [*,*]: With Grant Option: NONE Without Grant Option: NONE SQL> SQL> -- prevent drop by revoking the privilege SQL> revoke DROP on table * from DOC_READER; SQL> commit; Example 2: Revoking a privilege granted with the WITH GRANT OPTION clause When the privilege is revoked from the grantee, rdb_doc, who received the privilege with the WITH GRANT OPTION clause, the privilege is also revoked from all users who received the privilege from that grantee. SQL> SHOW PROTECTION ON TABLE EMPLOYEES; [*,*]: With Grant Option: NONE Without Grant Option: SELECT [SQL,WARRING]: With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER, DROP,DBCTRL,OPERATOR,DBADM,REFERENCES Without Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER, DROP,DBCTRL,DBADM,REFERENCES [RDB,RDB_DOC]: With Grant Option: SHOW Without Grant Option: NONE SQL> SQL> REVOKE SHOW ON EMPLOYEES FROM [rdb,rdb_doc]; SQL> SHOW PROTECTION ON EMPLOYEES; Protection on Table EMPLOYEES [*,*]: With Grant Option: NONE Without Grant Option: SELECT [RDB,RDB_DOC]: With Grant Option: NONE Without Grant Option: NONE Example 3: Revoking column privileges This example shows how to restrict privileges on a specific column by revoking the UPDATE privilege that has been granted for that column. SQL> SHOW PROTECTION ON COLUMN EMPLOYEES.EMPLOYEE_ID; [RDB,RDB_DOC]: With Grant Option: NONE Without Grant Option: UPDATE SQL> REVOKE UPDATE ON COLUMN EMPLOYEES.EMPLOYEE_ID FROM [rdb,rdb_doc]; SQL> SHOW PROTECTION ON COLUMN EMPLOYEES.EMPLOYEE_ID; [RDB,RDB_DOC]: With Grant Option: NONE Without Grant Option: NONE Example 4: Revoking DROP Privilege from a Sequence for a User This example shows the action of REVOKE for a SEQUENCE in an ANSI style database. SQL> create sequence EMPLOYEE_ID_GEN; SQL> grant select on sequence EMPLOYEE_ID_GEN to public; SQL> grant all privileges on sequence EMPLOYEE_ID_GEN to stuart; SQL> show protection on sequence EMPLOYEE_ID_GEN; Protection on Sequence EMPLOYEE_ID_GEN [DOCS,STUART]: With Grant Option: NONE Without Grant Option: SELECT,SHOW,ALTER,DROP,DBCTRL,REFERENCES [DOCS,FREEMAN]: With Grant Option: SELECT,SHOW,ALTER,DROP,DBCTRL,REFERENCES Without Grant Option: NONE [*,*]: With Grant Option: NONE Without Grant Option: SELECT SQL> revoke drop on sequence EMPLOYEE_ID_GEN from stuart; SQL> show protection on sequence EMPLOYEE_ID_GEN; Protection on Sequence EMPLOYEE_ID_GEN [DOCS,STUART]: With Grant Option: NONE Without Grant Option: SELECT,SHOW,ALTER,DBCTRL,REFERENCES [DOCS,FREEMAN]: With Grant Option: SELECT,SHOW,ALTER,DROP,DBCTRL,REFERENCES Without Grant Option: NONE [*,*]: With Grant Option: NONE Without Grant Option: SELECT SQL>