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).

(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
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
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
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-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

  1. REVOKE can revoke a privilege that has not been granted or a privilege that has already been revoked.
  2. When revoking a user's privilege for an RDAREA, if that user has a table, index, or sequence generator in that RDAREA, their privilege cannot be revoked.
  3. The schema privilege for a specific schema cannot be revoked if the schema contains tables.
  4. A maximum of 16 private user RDAREAs can be specified.
  5. A maximum of 1,600 authorization identifiers can be specified.
  6. Privileges granted with the PUBLIC option of GRANT must be revoked with the PUBLIC option of REVOKE.
  7. A user cannot revoke his or her own DBA privilege.
  8. The CONNECT privilege of a user who has either the DBA privilege or a schema cannot be revoked.
  9. Revoking the CONNECT privilege also revokes the schema definition privilege.
  10. The REVOKE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  11. The auditor's schema definition privilege or CONNECT privilege cannot be revoked.

Examples

  1. Revoke the DBA privilege for the user whose authorization identifier is USER1:

    REVOKE DBA FROM USER1

  2. Revoke the schema definition privilege for the user whose authorization identifier is USER2:

    REVOKE SCHEMA FROM USER2

  3. Revoke the CONNECT privilege for the user whose authorization identifier is USER3:

    REVOKE CONNECT FROM USER3

  4. 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

  5. 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}
    [WITH PROGRAM]

access-privilege::={SELECT|INSERT|DELETE|UPDATE}

Operands

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.
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.
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.
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.
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

  1. A user cannot revoke his or her own access privileges.
  2. REVOKE can revoke a privilege that has not been granted or a privilege that has already been revoked.
  3. 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.
  4. 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.
  5. 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.
    [Figure]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).
  6. 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.
  7. REVOKE cannot be executed from within a Java procedure if execution results in invalidation of the SQL object being executed.

Note

  1. The results of revocation of privileges granted to PUBLIC and to a specific user vary depending on the combination. The following figure shows the possible combinations.

    Figure 3-1 Revocation of privileges granted to PUBLIC and to a specific user

    [Figure]

  2. The REVOKE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  3. 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.
  4. 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 re-create the SQL object for which the function, procedure, and trigger are in effect.

Examples

  1. 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

  2. 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