REVOKE

Invocation for REVOKE

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

If the authorization mechanism was not activated when the Db2 subsystem was installed, an error condition occurs.

Authorization for REVOKE

If the BY clause is not specified, the authorization ID of the statement must have granted at least one of the specified privileges to every authorization-name specified in the FROM clause (including PUBLIC, if specified). If the BY clause is specified, the authorization ID of the statement must have SECADM or ACCESSCTRL authority.

To revoke the CREATE_SECURE_OBJECT privilege, with or without the BY clause, the privilege set must include SECADM authority.

If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. The owner can be a role. If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. However, if the process is running in a trusted context that is defined with the ROLE AS OBJECT OWNER AND QUALIFIER CLAUSE, the privilege set is the privileges that are held by the role that is in effect.

Description for REVOKE

authorization-specification

Specifies one or more privileges for the class of privilege. The same privilege must not be specified more than once.

FROM

Specifies from what authorization IDs the privileges are revoked.

authorization-name,...

Lists one or more authorization IDs. Do not use the same authorization ID more than one time. If the authorization-name is specified in lowercase, it must be delimited using double quotes.

The value of CURRENT RULES determines if you can use the ID of the REVOKE statement itself (to revoke privileges from yourself). When CURRENT RULES is:

  Db2

You cannot use the ID of the REVOKE statement.

  STD

You can use the ID of the REVOKE statement.

ROLE role-name

Lists one or more roles. Do not specify the same role more than one time.

PUBLIC

Revokes a grant of privileges to PUBLIC.

BY

Lists grantors who have granted privileges and revokes each named privilege that was explicitly granted to some named user by one of the named grantors. Only an authorization ID or role with SYSADM or SYSCTRL authority can use BY, even if the authorization ID or role names only itself in the BY clause.

authorization-name,…

Lists one or more authorization IDs of users who were the grantors of the privileges named. Do not use the same authorization ID more than once. Each grantor that is listed must have explicitly granted some named privilege to all of the named users or roles.

ROLE role-name

Lists one or more roles that were the grantors of the privileges named. Do not specify the same role more than one time. Each grantor that is listed must have explicitly granted some named privilege to all of the named users or roles.

ALL

Revokes each named privilege from all named users who were explicitly granted the privilege, regardless of who granted it.

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.

RESTRICT

Prevents the named privilege from being revoked when certain conditions apply. RESTRICT is the default only for the forms of the REVOKE statement that allow it. These forms are revoking the USAGE privilege on distinct types, the EXECUTE privilege on user-defined functions and stored procedures, the USAGE privilege on sequences, and READ or WRITE privileges for variables.

Notes for REVOKE

Revoked privileges

The privileges revoked from an authorization ID or a role are those that are identified in the statement and which were granted to the user by the grantor. Other privileges can be revoked as the result of revoking dependent privileges. For more on Db2 privileges, see Privileges and authorities.

Revoke dependent privileges

Revoking a privilege from a user can also cause that privilege to be revoked from other users. This was previously known as cascade revoke. When revoking a privilege from an authorization ID or a role, Db2 looks for and revokes any grants of the privilege where the grantor is the same as the authorization ID or role of the original revoke. The following rules must be true for privilege P' to be revoked from U3 when U1 revokes privilege P from U2:

• P and P' are the same privilege.
• U2 granted privilege P' to U3.
• No one granted privilege P to U2 prior to the grant by U1.
• U2 does not have installation SYSADM authority.

The rules also apply to the implicit grants that are made as a result of a CREATE VIEW statement.

Revoking dependent privileges does not occur under any of the following conditions:

• The privilege was granted by a current installation SYSADM user.
• The privilege is the USAGE privilege on a distinct type and the revokee owns any of these items:
  • A user-defined function or stored procedure that uses the distinct type
  • A table that has a column that uses the distinct type
  • A sequence whose data type is the distinct type
• The privilege is the USAGE privilege on a sequence and the revokee owns any of these items:
  • A trigger that has a NEXT VALUE or PREVIOUS VALUE expression that specifies the sequence
  • An inline SQL function that has a NEXT VALUE or PREVIOUS VALUE expression in the function body that specifies the sequence
• The privilege is the EXECUTE privilege on a user-defined function and the revokee owns any of these items:
  • A user-defined function that is sourced on the function
  • A view that uses the function
  • A trigger package that uses the function
  • A table that uses the function in a check constraint or a user-defined default type
• The privilege is the EXECUTE privilege on a stored procedure and the revoke owns any of these items:
  • A trigger package that refers to the stored procedure in a CALL statement.
• If the ACCESSCTRL administrative authority is revoked from a user, grants that are made by this ACCESSCTRL user are not revoked.

  If this user revoked grants made by it, those revokes will continue to revoke the dependent privileges, unless the behavior to not include the dependent privileges was specified either by using the system parameter REVOKE_DEP_PRIVILEGES or by using the REVOKE statement if REVOKE_DEP_PRIVILEGES is set to SQLSTMT.

• If SECADM is removed from a user, grants that are made by this SECADM user are not revoked.

  If this user revoked grants made by it, those revokes will continue to revoke the dependent privileges, unless the behavior to not include the dependent privileges was specified either by using the system parameter REVOKE_DEP_PRIVILEGES or by using the REVOKE statement if REVOKE_DEP_PRIVILEGES is set to SQLSTMT.

• If NOT INCLUDING DEPENDENT PRIVILEGES option is specified, the grants made by this user are not revoked.

Refer to the diagrams for the following example:

1. Suppose BOB grants SYSADM authority to WADE. Later, CLAIRE grants the SELECT privilege on a table with the WITH GRANT OPTION to WADE.
   
2. WADE grants the SELECT privilege to JOHN on the same table.
   
3. When CLAIRE revokes the SELECT privilege on the table from WADE, the SELECT privilege on that table is also revoked from JOHN.
   

The grant from WADE to JOHN is removed because WADE had not been granted the SELECT privilege from any other source before CLAIRE made the grant. The SYSADM authority granted to WADE from BOB does not affect the cascade revoke. For more on SYSADM and installation SYSADM authority, see Managing administrative authorities. For another example of cascading revokes, see Revoking privileges with the REVOKE statement.

Revoking a SELECT privilege that was exercised to create a view or materialized query table causes the view to be dropped, unless the owner of the view was directly granted the SELECT privilege from another source before the view was created. Revoking a SYSADM privilege that was required to create a view causes the view to be dropped. For details on when SYSADM authority is required to create a view, see Authorization in CREATE VIEW.

Invalidation of packages

A revoke or cascaded revoke of any privilege or role that was exercised to create a package makes the package invalid when the revokee no longer holds the privilege from any other source. Corresponding authorization caches are cleared even if the revokee has the privilege from any other source. For more information, see Changes that invalidate packages. ¹

Inoperative packages

A revoke or cascaded revoke of the EXECUTE privilege on a user-defined function that was exercised to create a package makes the package inoperative and causes the corresponding authorization caches to be cleared when the revokee no longer holds the privilege from any other source.¹

Privileges belonging to an authority

You can revoke an administrative authority, but you cannot separately revoke the specific privileges inherent in that administrative authority.

Let P be a privilege inherent in authority X. A user with authority X can also have privilege P as a result of an explicit grant of P. In this case:

• If X is revoked, the user still has privilege P.
• If P is revoked, the user still has the privilege because it is inherent in X.

Revoking of privileges in a trusted context

Revokes that are made in a trusted context that is defined with the ROLE AS OBJECT OWNER clause result in the revoker being the role in effect. If the statement is prepared dynamically, the revoker is the role that is associated with the user that is running the statement. If the statement is embedded in a program, the revoker is the owner of the plan or package. If the ROLE AS OBJECT OWNER clause is not specified for the trusted context, the revoker is the authorization ID of the process.

Ownership privileges

The privileges inherent in the ownership of an object cannot be revoked.

Revoke not including dependent privileges

When a privilege is revoked from a user by specifying NOT INCLUDING DEPENDENT PRIVILEGES, the grants that were made by this user are not revoked and the grantor remains unchanged. If that user is later granted the same privilege and then this privilege is revoked by specifying INCLUDING DEPENDENT PRIVILEGES, that would also revoke all the grants that were previously made by this user. Refer to the following examples:

User U1 is granted SELECT on table T1 with GRANT OPTION:

1. U1 grants this privilege to U2.
2. SELECT privilege is revoked from U1 without including dependent privileges. As a result, the grant from U1 to U2 is not revoked.
3. U1 is again granted SELECT on T1.
4. SELECT is now revoked from U1 with including dependent privileges and the grant from U1 to U2 is now revoked.

User U1 is granted SYSADM authority:

1. U1 grants privilege P1 to U2 and privilege P2 to U3.
2. SYSADM is revoked from U1 without including dependent privileges. The grants of privileges P1 and P2 to U2 and U3 are not revoked.
3. U1 is again granted SYSADM. U1 grants privilege P3 to U3.
4. SYSADM is now revoked from U1 including dependent privileges. Now, P1 granted to U2 and P2 and P3 granted to U3 are also revoked.