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.
1 – 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 -++-> <alias> -++-----------++
| |+--- , <-----+| ||
| +---> * -------+ ||
+-> table-privs-ansi ----> ON +----->----+--++-+-> <table-name> +++------+|
| +-> TABLE -+ || +-> <view-name> -+|| ||
| |+--------- , <------+| ||
| +---------> * --------+ ||
+-> column-privs-ansi ---> ON COLUMN ---+-> <column-name> +-------------+|
| +-------- , <------+ ||
+-> module-privs-ansi ---> ON MODULE --++-> <module-name> ++------------+|
| |+-------- , <------+| ||
| +--------> * --------+ ||
+-> ext-routine-privs-ansi -+> ON FUNCTION --+++-> <ext-routine-name> -+++|
| +> ON PROCEDURE -+|+---------- , <---------+|||
| +----------> * -----------+||
+-> sequence-privs-ansi -> ON SEQUENCE --++- > <sequence-name> -++-------+|
|+-------- , <---------+| |
+--------> * -----------+ |
+----------------------------<--------------------------------------------+
+-> 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 +---------------------------+ | |
| | | +> ( +> <column-name> +> ) -+ | |
| | | +----- , <-------+ | | |
| | +-> UPDATE -+---------------------- -------+ | |
| | +> ( +> <column-name> --+--> ) -+ | |
| | +----- , <---------+ | |
| +------------------- , <------------------------+ |
+------> 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 -------->
3 – Arguments
3.1 – ALL_PRIVILEGES
Specifies that SQL should revoke all privileges in the access
privilege set entry.
3.2 – 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.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.4 – 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.5 – 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.
4 – 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>