Db2 server configuration for using the TRUST PROCEDURE attribute

When establishing a connection, a trust procedure provides an additional attribute of trust for a user with SECADM authority. This feature is set by using the TRUST PROCEDURE attribute when creating or altering a trusted context object.

The trust procedure sets an SQLSTATE of 01H5B to specify a trusted connection. Any other successful result from the procedure is considered not trusted. Any error from the procedure results in a connection failure for a user that does not have SECADM authority. The TRUST PROCEDURE attribute can allow a user to make their own procedure for evaluating the trust of an incoming remote connection.

A trust procedure allows users to have more control over how they evaluate trusted connections. For example, a procedure can have access to Db2 global variables, such as CLIENT_IPADDR. This access can be used to evaluate whether the incoming connection is from a range of IP addresses, rather than having to set the addresses one-by-one in the original trusted context ADDRESS attribute.
Note: Be aware of the best practices and requirements when designing a trust procedure.

When creating or altering a trusted context object, the TRUST PROCEDURE attribute specifies the stored procedure to be called for all incoming remote connections from the specified system authid. After a trusted context object is created for a system authid by using the TRUST PROCEDURE attribute, the system authid must have the EXECUTE privilege on the specified trust procedure, either directly or indirectly through the PUBLIC authority or through an associated group or role of the authid.

The trust procedure is implicitly run on the server when an the trust of an incoming connection is being evaluated and before any subsequent requests are processed on the same connection. After the trust procedure runs successfully, the database manager commits any changes made by the procedure. If the trust procedure fails with an error, any changes made by the trust procedure are rolled back and the connection attempt fails with an error.

Dealing with errors in the trust procedure

To make notifications to the trust procedure, the TRUST PROCEDURE attribute must be dropped or replaced with a different procedure. To alter a trusted context object, a database connection and SECADM authority are both required. SECADM users must use one of the following methods to connect to the database:

  • Establish an inter-process communication (IPC) connection. This method establishes a normal connection because the trusted context feature is not supported for this type of connection.
  • Establish a regular connection. Users without SECADM authority receive a connection failed error when trying to establish a regular connection.
Important: A trust procedure is run only if all other trust attributes evaluate that a connection is trusted. Any error returned by the trust procedure ends the attempted connection. The error that is returned from running the trust procedure is returned back to the connecting application.

Best practices when using trust procedures

To avoid problems with your trust procedure, ensure that your procedure complies with the following best practices:

  • Keep the trust procedure logic simple. By introducing additional processing, a trust procedure affects the performance of CONNECT commands for every remote connection from the system authid that is specified in the trusted context object. The performance impact can be significant if the procedure is inefficient or if delays happen, such as lock contention.
  • Ensure that the procedure is well tested before establishing it as a trust procedure.
  • Avoid accessing objects in the trust procedure that are to be dropped or altered. If a dependent object in the trust procedure is dropped or privileges to access dependent objects are revoked, the trust procedure might fail. An error returned from the procedure can block new connections to the database from being established based on the logic of your procedure.
  • Avoid calling another procedure from the trust procedure. Procedures called by the trust procedure can be dropped or altered, unlike the trust procedure itself. If procedures called by the trust procedure are invalidated or dropped, the trust procedure might fail. An error returned from the trust procedure can block new connections to the database from being established, based on the logic of your procedure. Special registers changed in procedures that are called from the trust procedure do not change the special registers of the calling environment. This is different from special registers changed in the trust procedure itself, which do take effect in the application.
  • Avoid specifying the COMMIT ON RETURN clause in the trust procedure. An internal commit is processed after the implicit call of trust procedure. If the clause COMMIT ON RETURN YES is specified, the database manager processes multiple commit calls, which can affect performance. Specifying COMMIT ON RETURN NO has no effect on trust procedure processing.
  • Free all resources and close all cursors before exiting the trust procedure. Applications cannot access any resources left open by the trust procedure, such as WITH HOLD cursors. The resources held by the trust procedure after the commit is processed can be freed only when the application finishes.

Requirements and limitations

Trust procedures have the following requirements and limitations:

  • The procedure must exist on the current server (SQLSTATE 42704).
  • The procedure must be defined as LANGUAGE SQL or C (SQLSTATE 42615).
  • There must be a single procedure defined with the specified name on the current server (SQLSTATE 42704).
  • The procedure must not have any parameters (SQLSTATE 42639).
  • The trust procedure name and the associated schema name must not contain more than 128 characters (SQLSTATE 42622).
  • External C procedures must use PARAMETER STYLE SQL or DB2SQL (SQLSTATE 42639).
  • The system authid for which the trusted context is created must have EXECUTE privilege on the specified trust procedure either directly or indirectly through the PUBLIC authority or through an associated group or role of the authid.
  • The trust procedure cannot be altered or dropped through ALTER, DROP, CREATE, or REPLACE PROCEDURE statements while it is in use as a TRUST PROCEDURE attribute. To alter or drop the trust procedure, drop the TRUST PROCEDURE attribute for the trusted context or alter the trusted context to use a different procedure.
  • A trust procedure cannot use client information fields that are set by either of the following methods:
    • Calls to the sqleseti API.
    • Calls to the SQLSetConnectAttr CLI function.
    The special register for these fields contains their default server value before the trust procedure runs.
  • Client information fields, such as CLIENT USERID, CLIENT ACCTNG, CLIENT APPLNAME, and CLIENT WRKSTNNAME. or SQL special register that are set by any of the following methods are not yet updated when the trust procedure runs:
    • Calls to the sqleseti API.
    • Calls to the SQLSetConnectAttr CLI function.
    • Calls to the SQLSetEnvAttr CLI function,

  • The client information fields or SQL special register that are set by any of the following methods take precedence and override the special register values set in the trust procedure:
    • Calls to the sqleseti API.
    • Calls to the SQLSetConnectAttr CLI function.
    • Calls to the SQLSetEnvAttr CLI function.
    • Using the IBM® Data Server Driver for JDBC and SQLJ method set ClientAccountingInformation