DRDA access with Db2 for z/OS only

To prepare and execute SQL statements using DRDA access, certain privileges are required by the package owner and additional privileges are required by the user who invokes the application.

Any static statement executed using DRDA access is in a package bound at a server other than the local Db2 subsystem. Before the package can be bound, its owner must have the BINDADD privilege and the CREATE IN privilege for the package's collection. Also required are enough privileges to execute the package's static SQL statements that refer to data on that server. All these privileges are recorded in the Db2 catalog of the server, not in the catalog of the local Db2 subsystem. Such privileges must be granted by GRANT statements executed at the server. This allows the server to control the creation and use of packages that are run from other DBMSs.

A user who invokes an application that has a plan at the local Db2 subsystem must have the EXECUTE privilege on the plan recorded in the Db2 catalog of the local subsystem. If that application uses a package that is bound at a Db2 server other than the local Db2 requester, the EXECUTE privilege on the package must also be recorded in the Db2 catalog of the server. The ID that must hold the authorization to run the package at the Db2 server depends on the value of the PRIVATE_PROTOCOL subsystem parameter at the Db2 server:

• If PRIVATE_PROTOCOL is set to NO, EXECUTE authority on the package must be explicitly granted to the primary user ID or an associated secondary ID at the Db2 server. If the local requester application invokes a stored procedure that resides at the Db2 server, EXECUTE authority on the stored procedure package must be explicitly granted at the Db2 server to the owner of the package that issues the CALL statement if either of the following is true:
  • The owner of the stored procedure does not have the authority to execute the remote stored procedure package.
  • The CALL statement is in the form of CALL: host-variable and neither the primary user ID nor an associated secondary ID has the authority to execute the remote stored procedure package.
• If PRIVATE_PROTOCOL is not set to NO, EXECUTE authority on the package must be explicitly granted to the local requester plan owner at the Db2 server. The plan owner needs no other privilege to execute the package. If the local requester application invokes a stored procedure that resides at the Db2 server, EXECUTE authority on the stored procedure package must be explicitly granted at the Db2 server to the Db2 requester plan owner of the application that issues the CALL statement if either of the following is true:
  • The owner of the stored procedure does not have the authority to execute the remote stored procedure package.
  • The CALL statement is in the form of CALL: host-variable and neither the primary user ID nor an associated secondary ID has the authority to execute the remote stored procedure package.

EXECUTE authority is also required to use a package for a user-defined function, trigger, or stored procedure that resides at the Db2 server. However, except as previously described for a specific stored procedure case, the PRIVATE_PROTOCOL subsystem parameter is not used to determine the ID that is required to hold the EXECUTE privilege on that package. The EXECUTE privilege on that package must be recorded in the Db2 catalog of the server.

Having the appropriate privileges recorded as described above allows the execution of the static SQL statements in the package, and the execution of dynamic SQL statements if DYNAMICRULES bind, define, or invoke behavior is in effect. If DYNAMICRULES run behavior is in effect, the authorization rules for dynamic SQL statements is different. Authorization for the execution of dynamic SQL statements must come from the set of authorization IDs that are derived during connection processing, and, if the process is running in a trusted connection, the role that is in effect. An application goes through connection processing when it first connects to a server or when it reuses a CICS® or IMS thread that has a different primary authorization ID.

If an application uses Recoverable Resources Manager Services attachment facility (RRSAF) and has no plan, authority to execute the package is determined in the same way as when the requester is not Db2 for z/OS.