DYNAMICRULES bind option

If DYNAMICRULES is specified for REBIND PLAN, the DYNAMICRULES value has an effect only if an existing package has no DYNAMICRULES value. If REBIND PLAN with a DYNAMICRULES value is issued, and the package list includes the package without a DYNAMICRULES value, the package inherits the DYNAMICRULES value from the plan when the package runs.

The following DYNAMICRULES option descriptions include a description of the dynamic SQL statement behavior for each run time environment. This information is summarized in Table 1.

(RUN)

Processes dynamic SQL statements using the standard attribute values for dynamic SQL statements, which are collectively called run behavior:

• DB2 uses the authorization ID of the application process and the SQL authorization ID (the value of the CURRENT SQLID special register) for authorization checking of dynamic SQL statements.
• DB2 uses the value of the CURRENT SCHEMA special register as the default schema of table, view, index, and alias names.
• Dynamic SQL statements use the values of application programming options that were specified during installation. The installation option USE FOR DYNAMICRULES has no effect.
• GRANT, REVOKE, CREATE, ALTER, DROP, and RENAME statements can be executed dynamically.

( BIND )

Processes dynamic SQL statements using the following attribute values, which are collectively called bind behavior:

• DB2 uses the authorization ID of the package for authorization checking of dynamic SQL statements.
• Unqualified table, view, index, and alias names in dynamic SQL statements are implicitly qualified with value of the bind option QUALIFIER; if you do not specify QUALIFIER, DB2 uses the authorization ID of the package owner as the default schema.
• The attribute values that are described in Common attribute values for bind, define, and invoke behaviors.
• If you are running a trusted context with a role, or when the owner is a role, DB2 uses the authorization ID of the owner of the package for authorization checking of dynamic SQL statements. The same rules that are used to determine the authorization ID for static (embedded) statements are used for dynamic statements. DB2 uses the authorization ID of the owner of the package for authorization checking of dynamic SQL statements. The privilege set is the privileges that are held by the authorization ID of the owner of the package. The owner can also be a role. The identifier specified in the QUALIFIER option of the bind command that is used to bind the SQL statements is the implicit qualifier for all unqualified tables, views, aliases, indexes, and sequences. If this bind option was not used when the package was created or last rebound, the implicit qualifier is the authorization ID of the owner of the package. The owner can also be a role.

( DEFINEBIND )

Processes dynamic SQL statements using one of two behaviors, define behavior or bind behavior.

When the package is run as or runs under a stored procedure or user-defined function package, DB2 processes dynamic SQL statements using define behavior, which consists of the following attribute values:

• DB2 uses the authorization ID of the user-defined function or stored procedure owner for authorization checking of dynamic SQL statements in the application package.
• The default qualifier for unqualified objects is the user-defined function or stored procedure owner.
• The attribute values that are described in Common attribute values for bind, define, and invoke behaviors.
• If you are running a trusted context with a role, or when the owner is a role, then define behavior applies only if the dynamic SQL statement is in a package that is run as a stored procedure or user-defined function (or runs under a stored procedure or user-defined function package), and the package is bound with DYNAMICRULES (DEFINEBIND). DB2 uses the authorization ID of the stored procedure or user-defined function owner for authorization checking of dynamic SQL statements in the application package. This can be a primary or secondary authorization ID or a role. In this case, the privilege set is the privileges that are held by the authorization ID of the stored procedure or user-defined function owner. This owner can be a primary or secondary authorization ID or a role. The authorization ID of the stored procedure or user-defined function owner is also the implicit qualifier for unqualified table, view, alias, index, and sequence names. The owner can also be a role.

When the package is run as a stand-alone program, DB2 processes dynamic SQL statements using bind behavior, which is described in BIND keyword.

( DEFINERUN )

Processes dynamic SQL statements using one of two behaviors, define behavior or run behavior.

When the package is run as or runs under a stored procedure or user-defined function package, dynamic SQL statements have define behavior, which is described in DEFINEBIND keyword.

When the package is run as a stand-alone program, DB2 processes dynamic SQL statements using run behavior, which is described in RUN keyword.

If you are running a trusted context with a role, or when the owner is a role, then define behavior applies only if the dynamic SQL statement is in a package that is run as a stored procedure or user-defined function (or runs under a stored procedure or user-defined function package), and the package is bound with DYNAMICRULES (DEFINERUN). DB2 uses the authorization ID of the stored procedure or user-defined function owner for authorization checking of dynamic SQL statements in the application package. This can be a primary or secondary authorization ID or a role. In this case, the privilege set is the privileges that are held by the authorization ID of the stored procedure or user-defined function owner. This owner can be a primary or secondary authorization ID or a role. The authorization ID of the stored procedure or user-defined function owner is also the implicit qualifier for unqualified table, view, alias, index, and sequence names. The owner can also be a role.

( INVOKEBIND )

Processes dynamic SQL statements using one of two behaviors, invoke behavior or bind behavior.

When the package is run as or runs under a stored procedure or user-defined function package, DB2 processes dynamic SQL statements using invoke behavior, which consists of the following attribute values:

• DB2 uses the authorization ID of the user-defined function or stored procedure invoker for authorization checking of dynamic SQL statements in the application package.

  If the invoker is the primary authorization ID of the process or the CURRENT SQLID value, secondary authorization IDs are also checked if they are needed for the required authorization. Otherwise, only one ID, the ID of the invoker, is checked for the required authorization.

• The default qualifier for unqualified objects is the user-defined function or stored procedure invoker.
• The attribute values that are described in Common attribute values for bind, define, and invoke behaviors.
• If you are running a trusted context with a role, or when the owner is a role, then invoke behavior applies only if the dynamic SQL statement is in a package that is run as a stored procedure or user-defined function (or runs under a stored procedure or user-defined function package), and the package was bound with DYNAMICRULES( INVOKEBIND). DB2 uses the authorization ID of the stored procedure or user-defined function invoker for authorization checking of dynamic SQL statements in the application package. The invoker can also be a role. The privilege set is the privileges that are held by the authorization ID of the stored procedure or user-defined function invoker. However, if the invoker is the primary authorization ID of the process or the CURRENT SQLID value, secondary authorization IDs are also checked along with the primary authorization ID's role. Therefore, the privilege set is the union of the set of privileges held by each authorization ID of the process and the primary authorization ID's role. The authorization ID of the stored procedure or user-defined function invoker is also the implicit qualifier for unqualified table, view, alias, index, and sequence names. The invoker can also be a role.

When the package is run as a stand-alone program, DB2 processes dynamic SQL statements using bind behavior, which is described in BIND keyword.

( INVOKERUN )

Processes dynamic SQL statements using one of two behaviors, invoke behavior or run behavior.

When the package is run as or runs under a stored procedure or user-defined function package, DB2 processes dynamic SQL statements using invoke behavior, which is described in INVOKEBIND keyword.

If you are running a trusted context with a role, or when the owner is a role, then invoke behavior applies only if the dynamic SQL statement is in a package that is run as a stored procedure or user-defined function (or runs under a stored procedure or user-defined function package), and the package was bound with DYNAMICRULES( INVOKERUN). DB2 uses the authorization ID of the stored procedure or user-defined function invoker for authorization checking of dynamic SQL statements in the application package. The invoker can also be a role. The privilege set is the privileges that are held by the authorization ID of the stored procedure or user-defined function invoker. However, if the invoker is the primary authorization ID of the process or the CURRENT SQLID value, secondary authorization IDs are also checked along with the primary authorization ID's role. Therefore, the privilege set is the union of the set of privileges held by each authorization ID of the process and the primary authorization ID's role. The authorization ID of the stored procedure or user-defined function invoker is also the implicit qualifier for unqualified table, view, alias, index, and sequence names. The invoker can also be a role.

When the package is run as a stand-alone program, DB2 processes dynamic SQL statements using run behavior, which is described in RUN keyword.

Common attribute values for bind, define, and invoke behavior: The following attribute values apply to dynamic SQL statements in packages that have bind, define, or invoke behavior:

• You can execute the statement SET CURRENT SQLID in a package that is bound with any DYNAMICRULES value. However, DB2 does not use the value of CURRENT SQLID as the authorization ID for dynamic SQL statements.

  DB2 always uses the value of CURRENT SQLID as the qualifier for the EXPLAIN output and optimizer hints input PLAN_TABLE. (If the value of CURRENT SQLID has an alias on PLAN_TABLE and has the appropriate privileges, that PLAN_TABLE is populated.)

• If the value of installation option USE FOR DYNAMICRULES is YES, DB2 uses the application programming default values that were specified during installation to parse and semantically verify dynamic SQL statements. If the value of USE for DYNAMICRULES is NO, DB2 uses the precompiler options to parse and semantically verify dynamic SQL statements.
• GRANT, REVOKE, CREATE, ALTER, DROP, and RENAME statements cannot be executed dynamically.

Remote DB2 servers: For a package that uses DRDA access, DB2 sends the DYNAMICRULES option to the DB2 server at bind time.

The following table summarizes the dynamic SQL statement attribute values for each behavior.

Defaults:

Catalog record: Column DYNAMICRULES of tables SYSPACKAGE and SYSPLAN.