1  REVOKE
   Removes privileges from or entirely deletes an entry in the
   Oracle Rdb access control list (ACL) for a database object. Each
   entry in an access control list consists of an identifier (or
   role) and a list of privileges assigned to the identifier.

   o  Each identifier specifies a user or a set of users.

   o  The list of privileges specifies which operations that user or
      user group can perform on the database, table, column, module,
      procedure, function or sequence.

   When a user tries to perform an operation on a database, SQL
   reads the associated ACL from top to bottom, comparing the
   identifier of the user with each entry. As soon as SQL finds the
   first match, it grants the rights listed in that entry and stops
   the search. All identifiers that do not match a previous entry
   are compared with the subsequent entry, and if no match occurs,
   they receive the rights of ("fall through" to) the entry [*,*],
   if it exists. If no entry has the user identifier [*,*], then
   unmatched user identifiers are denied all access to the database,
   table, or column. For this reason, both the entries and their
   order in the list are important.

   To create an entry or add privileges to an entry in the Oracle
   Rdb access control list for a database object, see the GRANT
   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 --------> ON DATABASE ALIAS -++-> <alias> ++----------+-+
  |                                         |+----- , <--+|          | |
  |                                         +-----> * ----+          | |
  +-> table-privs -----> ON -+-----------+--++-+-> <table-name> +-++-+ |
  |                          +-> TABLE --+  || +-> <view-name> -+ || | |
  |                                         |+------- , <---------+| | |
  |                                         +-------> * -----------+ | |
  +-> column-privs ----> ON COLUMN ---+-> <column-name> -+-----------+ |
  |                                   +-------- , <------+           | |
  +-> module-privs ----> ON MODULE --++-> <module-name> -++----------+ |
  |                                  |+-------- , <------+|          | |
  |                                  +--------> * --------+          | |
  +-> ext-routine-privs -+-> ON FUNCTION --+++-> <ext-rout-name> -++-+ |
  |                      +-> ON PROCEDURE -+|+----------- , <-----+| | |
  |                                         +-----------> * -------+ | |
  +-> sequence-privs --> ON SEQUENCE -++-><sequence-name> -++--------+ |
                                      |+------- , <--------+|          |
                                      +-------> * ----------+          |
 +---------------------------------------------------------------------+
 +-> revoke-from ------------------------------------------------------->



 db-privs=
 
 ----+---+-+-> SELECT -----+-+----+->
     |   | +-> INSERT -----+ |    |
     |   | +-> OPERATOR ---+ |    |
     |   | +-> DELETE -----+ |    |
     |   | +-> CREATE -----+ |    |
     |   | +-> ALTER ------+ |    |
     |   | +-> DROP -------+ |    |
     |   | +-> DBCTRL -----+ |    |
     |   | +-> DBADM ------+ |    |
     |   | +-> SHOW -------+ |    |
     |   | +-> REFERENCES -+ |    |
     |   | +-> UPDATE -----+ |    |
     |   | +-> SECURITY ---+ |    |
     |   | +-> DISTRIBTRAN + |    |
     |   +------- , <--------+    |
     +---+---> ALL PRIVILEGES -+--+
         +---> ENTRY ----------+



 table-privs=
 
 -+--+-+-> SELECT -------------------------------+-+-+-->
  |  | +-> INSERT -------------------------------+ | |
  |  | +-> DELETE -------------------------------+ | |
  |  | +-> CREATE -------------------------------+ | |
  |  | +-> ALTER --------------------------------+ | |
  |  | +-> DROP ---------------------------------+ | |
  |  | +-> DBCTRL -------------------------------+ | |
  |  | +-> SHOW ---------------------------------+ | |
  |  | +-> REFERENCES +--------------------------+ | |
  |  | |              +> ( +> <column-name> +> ) + | |
  |  | |                   +----- , <-------+    | | |
  |  | +-> UPDATE +------------------------------+ | |
  |  |            +> ( +> <column-name> --+--> ) + | |
  |  |                 +----- , <---------+        | |
  |  +------------------- , <----------------------+ |
  +---+--> ALL PRIVILEGES --+------------------------+
      +--> ENTRY -----------+
 



 column-privs =
 
  ---+-+-+-> UPDATE -----+-+--+-->
     | | +-> REFERENCES -+ |  |
     | +------- , <--------+  |
     +---+-> ALL PRIVILEGES -++
         +-> ENTRY ----------+



 module-privs =
 
 -+--+-+-> ALTER ------------------------------+-+--+-->
  |  | +-> DBCTRL -----------------------------+ |  |
  |  | +-> DROP -------------------------------+ |  |
  |  | +-> EXECUTE ----------------------------+ |  |
  |  | +-> REFERENCES -------------------------+ |  |
  |  | +-> SHOW -------------------------------+ |  |
  |  +------------------- , <--------------------+  |
  +--+---> ALL PRIVILEGES -----+--------------------+
     +---> ENTRY --------------+



 ext-routine-privs =
 
 -+--+-+-> ALTER ------------------------------+-+--+-->
  |  | +-> DBCTRL -----------------------------+ |  |
  |  | +-> DROP -------------------------------+ |  |
  |  | +-> EXECUTE ----------------------------+ |  |
  |  | +-> REFERENCES -------------------------+ |  |
  |  | +-> SHOW -------------------------------+ |  |
  |  +------------------- , <--------------------+  |
  +--+---> ALL PRIVILEGES ---+----------------------+
     +---> ENTRY ------------+



 sequence-privs =
 
 -+-+-+-> ALTER -------------------------------+-+--+-->
  | | +-> DBCTRL ------------------------------+ |  |
  | | +-> DROP --------------------------------+ |  |
  | | +-> REFERENCES --------------------------+ |  |
  | | +-> SELECT ------------------------------+ |  |
  | | +-> SHOW --------------------------------+ |  |
  | +------- , <---------------------------------+  |
  +--> ALL PRIVILEGES ------------------------------+



 revoke-from =
 
 --> FROM --+--+-> identifier ---+-+---------------------------++------>
            |  +-> PUBLIC -------+ +-> AFTER +-> identifier ---+|
            |                      |         +-> PUBLIC -------+|
            |                      +-> POSITION <n> -----------+|
            +---------------------- , <-------------------------+



 identifier =
 
 --+-+-> user-identifier ----+-+->
   | +-> general-identifier -+ |
   | +-> system-identifier  -+ |
   | +-> role-name ----------+ |
   +------------ + <-----------+

 

2  Arguments
 

3  AFTER

   Syntax options:

      AFTER identifier
      AFTER PUBLIC

   Specifies the position of the entry within the ACL. If you omit
   the AFTER or POSITION argument, SQL searches the entire ACL
   for an identifier list that matches the one specified in the
   FROM clause of the REVOKE statement. If it finds a match, it
   modifies the ACL entry by deleting the privileges specified in
   the privilege list. If there is no match, SQL generates an error
   and the REVOKE statement has no effect on the ACL.

   With the AFTER or POSITION argument, you can specify the position
   in the list from which SQL searches for an ACL entry with an
   identifier that matches the one specified in the FROM clause of
   the REVOKE statement.

   o  In the AFTER argument, the identifier specifies the entry in
      the ACL after which SQL begins its search for the entry to be
      modified or deleted. If none of the entries in the ACL has an
      identifier that matches the identifier specified in the AFTER
      argument, SQL generates an error and the statement fails.

      Starting after the entry specified by the identifier in the
      AFTER argument, SQL searches entries in the ACL. If an entry
      has an identifier that matches the identifier specified by the
      FROM clause of the REVOKE statement, SQL modifies or deletes
      that ACL entry.

      If none of the entries has an identifier that matches the
      identifier specified by the FROM clause of the REVOKE
      statement, SQL generates an error and the statement fails
      (even if an entry before the position at which SQL began its
      search had an identifier that matched).

      Specifying PUBLIC is equivalent to a wildcard specification of
      all user identifiers.

   o  In the POSITION argument, the integer specifies the earliest
      relative position in the ACL of the entry to be modified or
      deleted. If the integer is larger than the number of entries
      in the ACL, SQL generates an error and the statement fails.

      Starting with the position specified by the POSITION argument,
      SQL searches entries in the ACL. If an entry has an identifier
      that matches the identifier specified by the FROM clause of
      the REVOKE statement, SQL modifies or deletes that ACL entry.

      If none of the entries has an identifier that matches the
      identifier specified by the FROM clause of the REVOKE
      statement, SQL generates an error and the statement fails
      (even if an entry before the position at which SQL began its
      search had an identifier that matched).
 

3  ALL_PRIVILEGES

   Specifies that SQL should revoke all privileges in the ACL entry.
   The REVOKE ALL PRIVILEGES statement differs from the REVOKE ENTRY
   statement in that it does not delete the entire entry from the
   ACL. The identifier remains, but without any privileges. An empty
   ACL entry denies all access to users matching the identifier,
   even if an entry later in the ACL grants PUBLIC access.
 

3  ENTRY

   Deletes the entire entry in the ACL, including the identifier.
 

3  FROM

   Syntax options:

      FROM identifier
      FROM PUBLIC

   Specifies the identifiers for the ACL entry to be modified
   or deleted. Specifying PUBLIC is equivalent to a wildcard
   specification of all user identifiers.

   You can specify foyr types of identifiers:

   o  User identifiers

   o  General identifiers

   o  System-defined identifiers

   o  Role names

   You can specify more than one identifier by combining them
   with plus signs (+).  Such identifiers are called  multiple
   identifiers. They identify only those users who are common to all
   the groups defined by the individual identifiers. Users who do
   not match all the identifiers are not controlled by that entry.

   For instance, the multiple identifier SECRETARIES + INTERACTIVE
   specifies only members of the group defined by the general
   identifier SECRETARIES that are interactive processes. It does
   not identify members of the SECRETARIES group that are not
   interactive processes.

   For more information about identifiers, see your operating system
   documentation.
 

3  general-identifier

   Identifies groups of users on the system and are defined by the
   OpenVMS system manager in the system privileges database. The
   following are possible general identifiers:

   o  DATAENTRY

   o  SECRETARIES

   o  MANAGERS
 

3  ON_object-type

   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  POSITION_n

   Specifies the position of the entry within the ACL. If you omit
   the AFTER or POSITION argument, SQL searches the entire ACL
   for an identifier list that matches the one specified in the
   FROM clause of the REVOKE statement. If it finds a match, it
   modifies the ACL entry by deleting the privileges specified in
   the privilege list. If there is no match, SQL generates an error
   and the REVOKE statement has no effect on the ACL.

   With the AFTER or POSITION argument, you can specify the position
   in the list from which SQL searches for an ACL entry with an
   identifier that matches the one specified in the FROM clause of
   the REVOKE statement.

   o  In the AFTER argument, the identifier specifies the entry in
      the ACL after which SQL begins its search for the entry to be
      modified or deleted. If none of the entries in the ACL has an
      identifier that matches the identifier specified in the AFTER
      argument, SQL generates an error and the statement fails.

      Starting after the entry specified by the identifier in the
      AFTER argument, SQL searches entries in the ACL. If an entry
      has an identifier that matches the identifier specified by the
      FROM clause of the REVOKE statement, SQL modifies or deletes
      that ACL entry.

      If none of the entries has an identifier that matches the
      identifier specified by the FROM clause of the REVOKE
      statement, SQL generates an error and the statement fails
      (even if an entry before the position at which SQL began its
      search had an identifier that matched).

      Specifying PUBLIC is equivalent to a wildcard specification of
      all user identifiers.

   o  In the POSITION argument, the integer specifies the earliest
      relative position in the ACL of the entry to be modified or
      deleted. If the integer is larger than the number of entries
      in the ACL, SQL generates an error and the statement fails.

      Starting with the position specified by the POSITION argument,
      SQL searches entries in the ACL. If an entry has an identifier
      that matches the identifier specified by the FROM clause of
      the REVOKE statement, SQL modifies or deletes that ACL entry.

      If none of the entries has an identifier that matches the
      identifier specified by the FROM clause of the REVOKE
      statement, SQL generates an error and the statement fails
      (even if an entry before the position at which SQL began its
      search had an identifier that matched).
 

3  privileges

   Types of privileges:

      db-privs
      table-privs
      column-privs
      module-privs
      ext-routine-privs
      sequence-privs

   Specifies the list of privileges you want to remove from an
   existing ACL entry. The operations permitted by a given privilege
   keyword differ, depending on whether it was granted for a
   database, table, column, module, external routine, or sequence.
   The Privileges table in the GRANT statement help topic lists
   the privilege keywords and their meanings for databases, tables,
   modules, columns, external routines, and sequences.
 

3  role-name

   The name of a role, such as one created with the CREATE ROLE
   statement. If the role name exists as an operating system group
   or rights identifier, then Oracle Rdb will create the role
   automatically when you issue the GRANT statement. A role that
   is created automatically always has the attribute of IDENTIFIED
   EXTERNALLY.
 

3  system-identifier

   Automatically defined by the OpenVMS system when the rights
   database is created at system installation time. System-defined
   identifiers are assigned depending on the type of login you
   execute. The following are all valid system-defined identifiers:

   o  BATCH

   o  NETWORK

   o  INTERACTIVE

   o  LOCAL

   o  DIALUP

   o  REMOTE
 

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]

   You can use the asterisk (*)  wildcard character as part of a
   user identifier. For example, if you want to specify all users
   in a group on an OpenVMS system, you can enter [341,*] as the
   identifier.

   When Oracle Rdb creates a database, it automatically creates an
   ACL entry with the identifier [*,*], which grants all privileges
   except DBCTRL to any user.

   You cannot use more than one user identifier in a multiple
   identifier.
 

2  Example

   Example 1: Using REVOKE to manage user access to the database and
   tables

   SQL> attach 'filename DB$:MF_PERSONNEL';
   SQL>
   SQL> -- examine current privileges
   SQL> show protection on database RDB$DBHANDLE;
   Protection on Alias RDB$DBHANDLE
       (IDENTIFIER=SQLNET4RDB,ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
         DROP+DBCTRL+OPERATOR+DBADM+SECURITY+DISTRIBTRAN)
       (IDENTIFIER=[DOC,DOC_READER],ACCESS=SELECT+CREATE)
       (IDENTIFIER=[DOC,DOC_WRITER],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
         ALTER+DROP+DBCTRL+OPERATOR+DBADM+REFERENCES)
       (IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+DROP+
         OPERATOR+DBADM+REFERENCES)
   SQL>
   SQL> -- revoke selected privileges
   SQL> revoke CREATE on database alias RDB$DBHANDLE from DOC_WRITER;
   SQL> revoke DISTRIBTRAN on database alias RDB$DBHANDLE from DOC_REVIEWER;
   SQL> show protection on database RDB$DBHANDLE;
   Protection on Alias RDB$DBHANDLE
       (IDENTIFIER=SQLNET4RDB,ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
         DROP+DBCTRL+OPERATOR+DBADM+SECURITY+DISTRIBTRAN)
       (IDENTIFIER=[DOC,DOC_READER],ACCESS=SELECT)
       (IDENTIFIER=[DOC,DOC_WRITER],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+ALTER+
         DROP+DBCTRL+OPERATOR+DBADM+REFERENCES)
       (IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+DROP+
         OPERATOR+DBADM+REFERENCES)
   SQL>
   SQL> -- No longer all access to DOC_REVIEWER, use wildcard for all tables
   SQL> revoke ALL PRIVILEGES on table * from DOC_REVIEWER;
   SQL> commit;

   Example 2: Revoking DROP Sequence Privileges from a User

   SQL> CREATE SEQUENCE EMPID;
   SQL> SHOW PROTECTION ON SEQUENCE EMPID
   Protection on Sequence EMPID
       (IDENTIFIER=[RDB,STUART],ACCESS=SELECT+SHOW+ALTER+DROP+DBCTRL)
       (IDENTIFIER=[*,*],ACCESS=NONE)
   SQL> GRANT SELECT ON SEQUENCE EMPID TO PUBLIC;
   SQL> SHOW PROTECTION ON SEQUENCE EMPID;
   Protection on Sequence EMPID
       (IDENTIFIER=[RDB,STUART],ACCESS=SELECT+SHOW+ALTER+DROP+DBCTRL)
       (IDENTIFIER=[*,*],ACCESS=SELECT)
   SQL> REVOKE DROP ON SEQUENCE EMPID FROM STUART;
   SQL> SHOW PROTECTION ON SEQUENCE EMPID;
   Protection on Sequence EMPID
       (IDENTIFIER=[RDB,STUART],ACCESS=SELECT+SHOW+ALTER+DBCTRL)
       (IDENTIFIER=[*,*],ACCESS=SELECT)