Authorizing plan or package access through applications

Example: Consider the following SQL statement:

EXEC SQL
  SELECT * INTO :EMPREC FROM DSN8D10.EMP
    WHERE EMPNO='000010';

The statement permits access to the row of the employee table WHERE EMPNO='000010'. If you replace the value 000010 with a host variable, the program could supply the value of the variable and permit access to various employee numbers. Routines in the program could limit that access to certain IDs, certain times of the day, certain days of the week, or other special circumstances.

Stored procedures provide an alternative to controls in the application. By encapsulating several SQL statements into a single message to the Db2 server, a stored procedure can protect sensitive portions of the application program. Also, stored procedures can include access to non-Db2 resources, as well as Db2.

Recommendation: Do not use programs to extend security. Whenever possible, use other techniques, such as stored procedures or views, as a security mechanism. Using programs to extend security has the following drawbacks:

• Program controls are separate from other access controls, can be difficult to implement properly, are difficult to audit, and are relatively simple to bypass.
• Almost any debugging facility can be used to bypass security checks in a program.
• Other programs might use the plan without doing the needed checking.
• Errors in the program checks might allow unauthorized access.
• Because the routines that check security might be quite separate from the SQL statement, the security check could be entirely disabled without requiring a bind operation for a new plan.
• A BIND REPLACE operation for an existing plan does not necessarily revoke the existing EXECUTE privileges on the plan. (Revoking those privileges is the default, but the plan owner has the option to retain them. For packages, the EXECUTE privileges are always retained.)

For those reasons, if the program accesses any sensitive data, the EXECUTE privileges on the plan and on packages are also sensitive. They should be granted only to a carefully planned list of IDs.