REVOKE (table or view privileges)
This form of the REVOKE statement revokes privileges on one or more tables or views.
Syntax
.-PRIVILEGES-. .-TABLE-. >>-REVOKE--+-ALL--+------------+-+--ON--+-------+---------------> | .-,--------------. | | V | | '---+-ALTER------+-+--' +-DELETE-----+ +-INDEX------+ +-INSERT-----+ +-REFERENCES-+ +-SELECT-----+ +-TRIGGER----+ '-UPDATE-----' .-,--------------. .-,----------------------. V | V | >----+-table-name-+-+--FROM----+-authorization-name-+-+---------> '-view-name--' +-ROLE--role-name----+ '-PUBLIC-------------' >--+------------------------------------+-----------------------> | .-,----------------------. | | V | | '-BY--+---+-authorization-name-+-+-+-' | '-ROLE--role-name----' | '-ALL------------------------' >--+------------------------------------+---------------------->< +-INCLUDING DEPENDENT PRIVILEGES-----+ '-NOT INCLUDING DEPENDENT PRIVILEGES-'
Description
- ALL or ALL PRIVILEGES
- If
you specify ALL, the authorization ID of the statement must have granted
a least one privilege on each identified table or view to each authorization-name.
The privilege revoked from an authorization ID are those privileges
on the table or view that the authorization ID of the statement granted
to the authorization ID.
If you do not use ALL, you must use one or more of the keywords listed below. Each keyword revokes the privilege described, but only as it applies to the tables or views named in the ON clause.
- ALTER
- Revokes the privilege to alter the specified table or create a trigger on the specified table.
- DELETE
- Revokes the privilege to delete rows in the specified table or view.
- INDEX
- Revokes he privilege to create an index on the specified table.
- INSERT
- Revokes the privilege to insert rows into the specified table or view.
- REFERENCES
- Revokes the privilege to define and drop referential constraints. Although you can use a list of column names with the GRANT statement, you cannot use a list of column names with REVOKE; the privilege is revoked for all columns.
- SELECT
- Revokes the privilege to create a view or read data from the specified table or view. A view or a materialized query table is dropped when the SELECT privilege that was used to create it is revoked, unless the owner of the view or materialized query table was directly granted the SELECT privilege from another source before the view or materialized query table was created.
- TRIGGER
- Revokes the privilege to create a trigger on the specified table.
- UPDATE
- Revokes the privilege to update rows in the specified table or view. A list of column names can be used only with GRANT, not with REVOKE.
- ON table-name or view-name
- Names one or more tables or views on which you are revoking the privileges. The list can consist of table names, view names, or a combination of the two. A table or view must not be identified more than one time, and a declared temporary table and a table that is implicitly created for an XML column must not be identified.
- FROM
- Refer to REVOKE for a description of the FROM clause.
- BY
- If you omit BY, you must have granted each named privilege to
each of the named users. More precisely, each privilege must have
been granted to each user by a GRANT statement whose authorization
ID is also the authorization ID of your REVOKE statement. Each of
these grants is then revoked. (No single privilege need be granted
on all tables and views.)
If BY is specified, each named grantor must satisfy the above requirement. In that case, the authorization ID of the statement need not satisfy the requirement unless it is one of the named grantors.
Refer to REVOKE for a description of the BY clause.
- INCLUDING DEPENDENT PRIVILEGES or NOT INCLUDING DEPENDENT PRIVILEGES
- Specifies
whether revoking a privilege or an authority from an authorization
ID or a role also results in revoking the grants that were made by
that user. The default value is based on the authority that is being
revoked and the REVOKE_DEP_PRIVILEGES system parameter:
- When ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
- When the REVOKE_DEP_PRIVILEGES system parameter is set to NO, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and an error is returned if the statement includes INCLUDING DEPENDENT PRIVILEGES.
- Otherwise, INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
- INCLUDING DEPENDENT PRIVILEGES
- Specifies that revoking a privilege or an authority from an authorization
ID or a role also results in revoking dependent privileges. This means
that any grants that were made by the user will continue to be revoked,
until all grants in the chain have been revoked.
INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set to NO, which enforces the behavior to not include the dependent privileges.
- NOT INCLUDING DEPENDENT PRIVILEGES
- Specifies that revoking a privilege or an authority from an authorization
ID or a role does not cause the grants that were made by the user
to be revoked. However, for the revoked privileges, all implications
of the privilege being revoked are applied. For example, if the revoked
privileges were required to bind a package successfully, that package
would continue to be invalidated as a result of the package owner
losing these privileges. An object might be dropped if a privilege
is revoked that was used to create the object.
NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked.
NOT INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set toYES, which enforces the behavior to include dependent privileges in the revoke.
Notes
For a created temporary table, only ALL or ALL PRIVILEGES can be revoked. Specific table privileges cannot be revoked.
For a view of a created temporary table, either ALL or the specific UPDATE, DELETE, INSERT and SELECT privileges can be revoked.
For a declared temporary table, no privileges can be revoked because none can be granted. When a declared temporary table is defined, PUBLIC implicitly receives all table privileges (without GRANT authority) for the table. These privileges are not recorded in the DB2® catalog.
Examples
REVOKE SELECT ON TABLE DSN8A10.EMP FROM PULASKI;
REVOKE UPDATE ON TABLE DSN8A10.EMP FROM PUBLIC;
REVOKE ALL ON TABLE DSN8A10.EMP FROM KWAN,THOMPSON;
REVOKE SELECT, UPDATE ON TABLE DSN8A10.DEPT
FROM PUBLIC;
REVOKE ALTER ON TABLE DSN8A10.EMP
FROM ROLE ROLE1;