REVOKE (Revoke privileges)
Function
REVOKE revokes the DBA, schema definition, CONNECT, and private user RDAREA usage privileges (see subsection (1) below); it also revokes access privileges granted to users (see subsection (2) below).
This command can revoke the access privilege granted to a role when the Sun Java System Directory Server linkage facility is in use.
(1) REVOKE DBA (revoke DBA privilege), REVOKE SCHEMA (revoke schema definition privilege), REVOKE CONNECT (revoke CONNECT privilege), and REVOKE RDAREA (revoke RDAREA usage privilege)
Privileges
- Users with the DBA privilege
A user with the DBA privilege can revoke the DBA, schema definition, CONNECT, and private user RDAREA usage privileges.
Format
REVOKE {DBA FROM authorization-identifier [, authorization-identifier]...
|SCHEMA FROM authorization-identifier
[, authorization-identifier]...
|CONNECT FROM authorization-identifier
[, authorization-identifier]...
| [RDAREA RDAREA-name [,RDAREA-name]...
FROM {authorization-identifier
[, authorization-identifier]...|PUBLIC}
Operands
- DBA FROM authorization-identifier [, authorization-identifier]...
- DBA FROM
- Specifies that one or more users' DBA privilege is to be revoked.
- authorization-identifier [, authorization-identifier]...
- Specifies the authorization identifiers of the users whose DBA privilege is to be revoked.
- SCHEMA FROM authorization-identifier [, authorization-identifier]...
- SCHEMA FROM
- Specifies that one or more users' schema definition privilege is to be revoked.
- authorization-identifier [, authorization-identifier]...
- Specifies the authorization identifiers of the users whose schema definition privilege is to be revoked.
- CONNECT FROM authorization-identifier [, authorization-identifier]...
- CONNECT FROM
- Specifies that one or more users' CONNECT privilege is to be revoked.
- authorization-identifier [, authorization-identifier]...
- Specifies the authorization identifiers of the users whose CONNECT privilege is to be revoked.
- RDAREA RDAREA-name [, RDAREA-name]...
FROM {authorization-identifier [, authorization-identifier]...|PUBLIC}
- RDAREA-name [, RDAREA-name]
- Specifies the names of RDAREAs for which the RDAREA usage privilege is to be revoked.
- authorization-identifier [, authorization-identifier]...
- Specifies the authorization identifiers of the users whose usage privilege for the specified RDAREAs is to be revoked.
- PUBLIC
- Specifies that the usage privilege for the specified RDAREAs as public user RDAREAs is to be revoked.
Notes
- REVOKE can revoke a privilege that has not been granted or a privilege that has already been revoked.
- A user's RDAREA usage privilege cannot be revoked if the user has tables or indexes in the RDAREA.
- The schema privilege for a specific schema cannot be revoked if the schema contains tables.
- A maximum of 16 private user RDAREAs can be specified.
- A maximum of 1,600 authorization identifiers can be specified.
- Privileges granted with the PUBLIC option of GRANT must be revoked with the PUBLIC option of REVOKE.
- A user cannot revoke his or her own DBA privilege.
- The CONNECT privilege of a user who has either the DBA privilege or a schema cannot be revoked.
- Revoking the CONNECT privilege also revokes the schema definition privilege.
- The REVOKE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- The auditor's schema definition privilege or CONNECT privilege cannot be revoked.
Examples
- Revoke the DBA privilege for the user whose authorization identifier is USER1:
REVOKE DBA FROM USER1
- Revoke the schema definition privilege for the user whose authorization identifier is USER2:
REVOKE SCHEMA FROM USER2
- Revoke the CONNECT privilege for the user whose authorization identifier is USER3:
REVOKE CONNECT FROM USER3
- Revoke RDAREA usage privileges for RDAREAs RDA1 and RDA2 for the users whose authorization identifiers are USER4, USER5, and USER6:
REVOKE RDAREA RDA1,RDA2
FROM USER4,USER5,USER6
- Redefine public user RDAREA RDA3 as a private user RDAREA and grant RDAREA usage privilege to USER1:
REVOKE RDAREA RDA3 FROM PUBLIC
GRANT RDAREA RDA3 TO USER1
(2) REVOKE access privilege (revoke access privileges)
Privileges
- Owner of the table
The owner of a table can revoke an access privilege granted with GRANT access privilege.
Format
REVOKE {access-privilege [, access-privilege]...|All [privilege]}
ON [authorization-identifier.] table-identifier
FROM [authorization-identifier [, authorization-identifier]...| PUBLIC
|GROUP role-name[, role-name]...}
[WITH PROGRAM]
access-privilege::={SELECT|INSERT|DELETE|UPDATE}
Operands
- {access-privilege [, access-privilege]...|All [privilege]}
- access-privilege
- Specifies an access privilege for a specified table that is to be revoked. Identical access privileges cannot be specified.
- ALL [PRIVILEGES]
- Specifies that all applicable access privileges for a specified table are to be revoked.
- access-privilege::={SELECT|INSERT|DELETE|UPDATE}
- SELECT
- Specifies that the SELECT privilege is to be revoked.
- INSERT
- Specifies that the INSERT privilege is to be revoked.
- DELETE
- Specifies that the DELETE privilege is to be revoked.
- UPDATE
- Specifies that the UPDATE privilege is to be revoked.
- ON [authorization-identifier.] table-identifier
- authorization-identifier
- Specifies the authorization identifier of the owner of the table whose access privileges are to be revoked.
- To revoke access privileges to public views, specify the word PUBLIC in authorization-identifier, in uppercase characters enclosed in double quotation marks (").
- table-identifier
- Specifies the name of the table to which access privileges are to be revoked.
- FROM [authorization-identifier [, authorization-identifier] ... |PUBLIC|
GROUP role-name [, role-name]...} [WITH PROGRAM]
- authorization-identifier
- Specifies the authorization identifier of a user whose access privileges for the specified table are to be revoked. A maximum of 1,600 user authorization identifiers can be specified. Duplicate authorization identifiers are not allowed.
- PUBLIC
- Specifies that the privilege granted by means of the PUBLIC option of GRANT is to be revoked.
- GROUP role-name
- Specifies the role name whose access privilege is to be revoked. The role name is one of the information items managed by the Sun Java System Directory Server.
- The following rules apply to role names:
- A maximum of 1,600 role names can be specified.
- Each role name that is specified must be unique.
- A role name can be specified only when the directory server linkage facility is used.
- WITH PROGRAM
- When revoking the SELECT privilege for a table that is the base for a view table, this option is specified to nullify the SQL object for which functions, procedures, and triggers are in effect that use the view table that is deleted by the revocation of the SELECT privilege.
Common rules
- A user cannot revoke his or her own access privileges.
- REVOKE can revoke a privilege that has not been granted or a privilege that has already been revoked.
- Any privilege granted using the PUBLIC option of GRANT must be revoked using the PUBLIC option of REVOKE.
If a privilege was granted by specifying the PUBLIC option and it is desired to block access by a specific user, the privilege must be revoked using the PUBLIC option and regranted to the desired users by specifying their authorization identifiers.
- When the table owner revokes the SELECT privilege that was granted to another person, any view table defined by that person using that table is deleted.
- The following shows the rules for revoking access privileges to tables used in a view definition or the access privileges to the base table for a view table.
Defining view table V1:
CREATE VIEW V11 (VPCODE,VPNAME,VPRICE)
AS SELECT PCODE, PNAME, PRICE
FROM STOCK2 WHERE PCODE =
(SELECT PCODE FROM ORDERS3)
Defining view table V2:
CREATE VIEW V24 (VSPCODE, VSDATE_IN_STOCK)
AS SELECT PCODE, DATE_IN_STOCK
FROM INTO_STOCK5
Defining view table VV1 from view table V1:
CREATE VIEW VV16 (VVPCODE, VVPRICE)
AS SELECT VPCODE, VPRICE
FROM V11 WHERE PCODE =
(SELECT VSPCODE FROM V24)
WHERE VSDATE_IN_STOCK >
DATE('1995-09-21'))
- When the SELECT privilege of the owner of a view table for a table (2, 3, 5) used in a view definition is revoked, both the view table itself (1, 4) and any other view tables (6) that are defined in terms of the view table are also deleted.
- Example 1:
- When the SELECT privilege of the owner of the view table for ORDERS table (5) is revoked, both V1 (4) and VV1 (6) are deleted.
- Example 2:
- When the SELECT privilege of the owner of the view table for INTO_STOCK table (5) is revoked, both V2 (4) and VV1 (6) are deleted.
When the access privilege of the owner of the view table for a base table (2, 5) for a view table is revoked, the access privilege to the view table itself is also revoked (1, 4) as well as the access privileges to any other view tables (6) that are defined in terms of the view table. In this context, the term access privilege refers to privileges other than the SELECT privilege.
- Example 1:
- When the access privilege of the owner of the view table for STOCK table (2) is revoked, access privileges to V1 (1) and VV1 (6) are also revoked.
- Example 2:
- When the access privilege of the owner of the view table for INTO_STOCK table (5) is revoked, the access privilege to V2 (4) is also revoked.
- To regrant an access privilege that has been revoked, reassign the access privilege to the base table on which the privilege-revoke view table is defined, delete the view table, and then redefine the view table.
- For Example 1, grant access privileges to the owner of the STOCK view table (2), delete V1 (1) (this also deletes VV1), and then redefine V1 (1) and VV1 (6) in the indicated order.
- For Example 2, grant access privileges to the owner of the INTO_STOCK view table (5), delete V2 (4), and then redefine V2 (4).
- If WITH PROGRAM is omitted, and if there is an SQL object for which a procedure and trigger are in effect that use the view table that is deleted by the revocation of the SELECT privilege, that privilege cannot be deleted.
- REVOKE cannot be executed from within a Java procedure if execution results in invalidation of the SQL object being executed.
Note
- The revocation of the privileges granted to PUBLIC, to a specific user, or to a role can produce different results depending on various combinations. Figure 3-1 shows various combinations.
Figure 3-1 Revocation of privileges granted to PUBLIC, to a specific user, or to a role
![[Figure]](figure/zu3s0100.gif)
- The REVOKE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- If WITH PROGRAM is specified to nullify an SQL object for which a function, a procedure, and a trigger are in effect, any information in the SQL_ROUTINE_RESOURCES dictionary table about the nullified function, procedure, and trigger is deleted.
- Before executing the SQL object of a function, procedure, and trigger that is nullified by specifying WITH PROGRAM, you need to execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER to recreate the SQL object for which the function, procedure, and trigger are in effect.
Examples
- Revoke the privilege (SELECT privilege) to retrieve the stock table (STOCK) for the user whose authorization identifier is USER1:
REVOKE SELECT ON STOCK FROM USER1
- For the stock table (STOCK), revoke only the DELETE privilege from the access privileges that were granted to all users:
REVOKE DELETE ON STOCK FROM PUBLIC
- Revokes all privileges that were granted to the role (role name: ADMIN) for access to the inventory table (STOCK).
REVOKE ALL ON STOCK GROUP ADMIN