Revoking privileges for plans or packages

Suppose that OPER2 has the SELECT and INSERT privileges on table T1 and creates a package that uses SELECT, but not INSERT. When privileges are revoked from OPER2, the plan is affected in the following ways:

• If the INSERT privilege is revoked, the plan is unaffected.
• If the revoked privilege was EXECUTE on a user-defined function, Db2 marks the package inoperative instead of invalid.

If authorization data is cached for a package and an ID loses EXECUTE authority on the package, that ID is removed from the cache. Similarly, if authorization data is cached for routines, a revoke or cascaded revoke of EXECUTE authority on a routine, or on all routines in a schema (schema.*), from any ID causes the ID to be removed from the cache.

If authorization data is cached for plans, a revoke of EXECUTE authority on the plan from any ID causes the authorization cache to be invalidated.

If an application is caching dynamic SQL statements, and a privilege is revoked that was needed when the statement was originally prepared and cached, that statement is removed from the cache. Subsequent PREPARE requests for that statement do not find it in the cache and therefore execute a full PREPARE. If the plan or package is bound with KEEPDYNAMIC(YES), which means that the application does not need to explicitly re-prepare the statement after the point of commit or rollback, you might get an error on an OPEN, DESCRIBE, or EXECUTE of that statement following the next COMMIT or ROLLBACK. The error can occur because a prepare operation is performed implicitly by Db2. If you no longer have sufficient authority for the prepare, the OPEN, DESCRIBE, or EXECUTE request fails.