Plan Security

SQL plans are securable. That is, with plan security you can create a plan such that, in order to execute the plan, an accessor ID must have the plan EXECUTE privilege for that plan. The plan EXECUTE privilege can be granted with the GRANT statement and revoked with the REVOKE statement. For more information, see Plan EXECUTE and Plan BIND Privileges.

Plan security allows the Security Administrator to limit what can be accessed by a given interactive SQL product user while allowing that same user access to the restricted tables while executing the trusted plan.

After the system determines that an accessor ID has the authority to execute a plan, no further privilege-checking is done. However, when the plan was created, the binder of the plan was checked to ensure that the privileges required to execute the plan existed. Therefore, the executor of such a plan temporarily assumes the privileges of the binder of the plan, and it is up to the binder to ensure that the executor does not violate security.

There is no special privilege required to create a plan, but the ability to create a plan which uses the binder's privileges (instead of the executor's) is controlled by the use of the CHECKWHO=BINDER plan option. To use CHECKWHO=BINDER, you need the system-level CHECKBINDER privilege. For more information, see Plan Options in Plan Security and CHECKBINDER System Privilege.

If the plan is not secured, security works as follows:

Anyone can execute the plan (privileges are checked using the accessor ID of the executor of the plan at execution time),
Anyone can preprocess the plan, and
Anyone can rebind or delete the plan.

If the plan is secured, however, users must possess the proper privileges before they can execute, preprocess, rebind, or delete the plan.

Note: External security is the preferred method for securing all SQL resources.

Plan security authorizations (CHECKBINDER, PLAN EXECUTE, PLAN BIND) are checked using the following criteria. If any external security path is active, external security is checked. Otherwise, SQL GRANT/REVOKE security is checked. If neither security method is active but an attempt is made to use SQL plan security anyway, the authorization attempt is rejected as if security had been active and the user was unauthorized.

Externalization of Plan Security

Users of CA ACF2, CA Top Secret, and RACF can take advantage of SQL plan security by using statements (in their own security packages) equivalent to the SQL GRANT and REVOKE statements to control the plan EXECUTE, plan BIND, and system level CHECKBINDER privileges. For more information, see Externalization of Plan Security.