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.
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
(B)0[m[1;4mREVOKE[m[1m qqqqqqqqqqqqqqqqqqqqqqqk [m [1m [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m [m
[1mmwq> db-privs qqqqqqqq> [1;4mON[m[1m [1;4mDATABASE[m[1m ALIAS qwwq> <alias> wwqqqqqqqqqqwqk [m
[1m x [m [1mxmqqqqq , <qqjx x x [m
[1m x[m [1mmqqqqq> * qqqqj [m [1mx[m [1mx[m
[1m tq> table-privs qqqqq> [1;4mON[m[1m qwqqqqqqqqqqqwqqwwqwq> <table-name> wqwwqu x [m
[1m x mq> [1;4mTABLE[m[1m qqj [m [1mxx mq> <view-name> qj xx x x [m
[1m x [m [1mxmqqqqqqq , <qqqqqqqqqjx x x [m
[1m x[m [1mmqqqqqqq> * qqqqqqqqqqqj[m [1mx[m [1mx[m
[1m tq> column-privs qqqq> [1;4mON[m[1m [1;4mCOLUMN[m[1m qqqwq> <column-name>[m [1mqwqqqqqqqqqqqu x [m
[1m x mqqqqqqqq , <qqqqqqj x x [m
[1m tq> module-privs qqqq> [1;4mON[m[1m [1;4mMODULE[m[1m qqwwq> <module-name>[m [1mqwwqqqqqqqqqqu x [m
[1m x xmqqqqqqqq , <qqqqqqjx x x [m
[1m x[m [1mmqqqqqqqq> * qqqqqqqqj[m [1mx[m [1mx[m
[1m tq> ext-routine-privs qwq> [1;4mON[m[1m [1;4mFUNCTION[m[1m qqwwwq> <ext-rout-name> qwwqu x [m
[1mx mq> [1;4mON[m[1m [1;4mPROCEDURE[m[1m qjxmqqqqqqqqqqq , <qqqqqjx x x [m
[1mx[m [1mmqqqqqqqqqqq> * qqqqqqqj x[m [1mx [m
[1m mq> sequence-privs qq> [1;4mON[m[1m [1;4mSEQUENCE[m[1m qwwq><sequence-name> qwwqqqqqqqqj x [m
[1m xmqqqqqqq , <qqqqqqqqjx [m [1m x [m
[1m mqqqqqqq> * qqqqqqqqqqj x[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmq> revoke-from qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>[m
(B)0[m[1mdb-privs= [m
[1m [m
[1mqqqqwqqqwqwq> [1;4mSELECT[m[1m qqqqqwqwqqqqwq>[m
[1m x x tq> [1;4mINSERT[m[1m qqqqqu x x [m
[1m x x tq> [1;4mOPERATOR[m[1m qqqu x x [m
[1m x x tq> [1;4mDELETE[m[1m qqqqqu x x [m
[1m x x tq> [1;4mCREATE[m[1m qqqqqu x x [m
[1m x x tq> [1;4mALTER[m[1m qqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqu x x [m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqu x x [m
[1m x x tq> [1;4mDBADM[m[1m qqqqqqu x x [m
[1m x x tq> [1;4mSHOW[m[1m qqqqqqqu x x [m
[1m x x tq> [1;4mREFERENCES[m[1m qu x x [m
[1m x x tq> [1;4mUPDATE[m[1m qqqqqu x x [m
[1m x x tq> [1;4mSECURITY[m[1m qqqu x x [m
[1m x x mq> [1;4mDISTRIBTRAN[m[1m j x x [m
[1m x mqqqqqqq , <qqqqqqqqj x [m
[1m mqqqwqqq> [1;4mALL[m[1m PRIVILEGES qwqqj [m
[1m mqqq> [1;4mENTRY[m[1m qqqqqqqqqqj [m
(B)0[m[1mtable-privs= [m
[1m [m
[1mqwqqwqwq> [1;4mSELECT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqwqq>[m
[1m x x tq> [1;4mINSERT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDELETE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mCREATE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mREFERENCES[m[1m wqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x x m> ( w> <column-name> w> ) u x x [m
[1m x x x mqqqqq , <qqqqqqqj x x x [m
[1m x x mq> [1;4mUPDATE[m[1m wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x m> ( w> <column-name> qqwqq> ) j x x [m
[1m x x mqqqqq , <qqqqqqqqqj x x [m
[1m x mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqwqq> [1;4mALL[m[1m PRIVILEGES qqwqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqq> [1;4mENTRY[m[1m qqqqqqqqqqqj [m
[1m [m
(B)0[m[1mcolumn-privs = [m
[1m [m
[1mqqqwqwqwq> [1;4mUPDATE[m[1m qqqqqwqwqqwqq>[m
[1m x x mq> [1;4mREFERENCES[m[1m qj x x [m
[1m x mqqqqqqq , <qqqqqqqqj x [m
[1m mqqqwq> [1;4mALL[m[1m PRIVILEGES qwj [m
[1m mq> [1;4mENTRY[m[1m qqqqqqqqqqj [m
(B)0[m[1mmodule-privs = [m
[1m [m
[1mqwqqwqwq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>[m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mEXECUTE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x[m [1mtq> [1;4mREFERENCES[m qqqqqqqqqqqqqqqqqqqq[1mqqqqqu[m [1mx[m [1mx[m
[1m x x mq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x x [m
[1m x mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqwqqq> [1;4mALL[m[1m PRIVILEGES qqqqqwqqqqqqqqqqqqqqqqqqqqj [m
[1mmqqq> [1;4mENTRY[m[1m qqqqqqqqqqqqqqj[m
(B)0[m[1mext-routine-privs = [m
[1m [m
[1mqwqqwqwq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>[m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mEXECUTE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1mx[m [1mx[m [1mtq>[m [1;4mREFERENCES[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1mx[m [1m [m
[1m x x mq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x x [m
[1m x mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqwqqq> [1;4mALL[m[1m PRIVILEGES qqqwqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqqq> [1;4mENTRY[m[1m qqqqqqqqqqqqj [m
(B)0[m[1msequence-privs =[m
[1mqwqwqwq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>[m
[1mx[m [1mx[m [1mtq> [1;4mDBCTRL[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1m x[m
[1mx[m [1mx[m [1mtq> [1;4mDROP[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1m x[m
[1mx[m [1mx[m [1mtq>[m [1;4mREFERENCES[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1mx[m
[1mx[m [1mx tq> [1;4mSELECT[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1m x[m
[1mx[m [1mx[m [1mmq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x x[m
[1mx[m [1mmqqqqqqq[m [1m,[m [1m<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m [1m x[m
[1mmqq> [1;4mALL[m[1m PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
(B)0[m[1mrevoke-from =[m
[1mqq> [1;4mFROM[m[1m qqwqqwq> identifier qqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwwqqqqqq> [m
[1m x mq> [1;4mPUBLIC[m[1m qqqqqqqj tq> [1;4mAFTER[m[1m wq> identifier qqqux [m
[1m x x mq> [1;4mPUBLIC[m[1m qqqqqqqux [m
[1m x mq> [1;4mPOSITION[m[1m <n> qqqqqqqqqqqjx [m
[1m mqqqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1midentifier = [m
[1m [m
[1mqqwqwq> user-identifier qqqqwqwq> [m
[1m x tq> general-identifier qu x [m
[1m x tq> system-identifier qu x [m
[1m x[m [1mmq> role-name qqqqqqqqqqj[m [1mx [m
[1m mqqqqqqqqqqqq + <qqqqqqqqqqqj [m
3 – Arguments
3.1 – 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.2 – 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.3 – ENTRY
Deletes the entire entry in the ACL, including the identifier.
3.4 – 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.5 – 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.6 – 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.7 – 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.8 – 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.9 – 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.10 – 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.11 – 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.
4 – 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)