Effect of DYNAMICRULES bind option on dynamic SQL

In particular, the DYNAMICRULES parameter determines what values apply at run time for the following dynamic SQL attributes:

• The authorization ID that is used during authorization checking.
• The qualifier that is used for qualification of unqualified objects.
• Whether the package can be used to dynamically prepare the following statements: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE statements.

In addition to the DYNAMICRULES value, the runtime environment of a package controls how dynamic SQL statements behave at run time. The two possible runtime environments are:

• The package runs as part of a stand-alone program
• The package runs within a routine context

The combination of the DYNAMICRULES value and the runtime environment determine the values for the dynamic SQL attributes. That set of attribute values is called the dynamic SQL statement behavior. The four behaviors are:

Run behavior

Db2® uses the authorization ID of the user (the ID that initially connected to the Db2 database) executing the package as the value to be used for authorization checking of dynamic SQL statements and for the initial value used for implicit qualification of unqualified object references within dynamic SQL statements.

Bind behavior

At run time, Db2 uses all the rules that apply to static SQL for authorization and qualification. That is, take the authorization ID of the package owner as the value to be used for authorization checking of dynamic SQL statements and the package default qualifier for implicit qualification of unqualified object references within dynamic SQL statements.

Define behavior

Define behavior applies only if the dynamic SQL statement is in a package that is run within a routine context, and the package was bound with DYNAMICRULES DEFINEBIND or DYNAMICRULES DEFINERUN. Db2 uses the authorization ID of the routine definer (not the routine's package binder) as the value to be used for authorization checking of dynamic SQL statements and for implicit qualification of unqualified object references within dynamic SQL statements within that routine.

Invoke behavior

Invoke behavior applies only if the dynamic SQL statement is in a package that is run within a routine context, and the package was bound with DYNAMICRULES INVOKEBIND or DYNAMICRULES INVOKERUN. Db2 uses the current statement authorization ID in effect when the routine is invoked as the value to be used for authorization checking of dynamic SQL and for implicit qualification of unqualified object references within dynamic SQL statements within that routine. This is summarized by the following table:

Invoking Environment	ID Used
Any static SQL	Implicit or explicit value of the OWNER of the package the SQL invoking the routine came from.
Used in definition of view or trigger	Definer of the view or trigger.
Dynamic SQL from a run behavior package	ID used to make the initial connection to the Db2 database.
Dynamic SQL from a define behavior package	Definer of the routine that uses the package that the SQL invoking the routine came from.
Dynamic SQL from an invoke behavior package	Current authorization ID invoking the routine.

The following table shows the combination of the DYNAMICRULES value and the runtime environment that yields each dynamic SQL behavior.

The following table shows the dynamic SQL attribute values for each type of dynamic SQL behavior.