Controlling remote execution of stored procedures by using trusted contexts

You can use trusted contexts and roles to control how a stored procedure can be executed. A trusted context is an independent database entity that is based on a system authorization ID (SYSTEM AUTHID) and connection trust attributes.

Before you begin

If you set field 1 (RESTART or DEFER) to DEFER and set field 2 (objects to restart or defer) to ALL in installation panel DSNTIPS, you cannot use trusted connections.

About this task

Begin general-use programming interface information. For a remote stored procedure CALL, the SYSTEM AUTHID is derived from the system user ID that is provided by an external entity, such as a middleware server. This ID is derived when the connection is initiated. The connection trust attributes are as follows, specified in the CREATE TRUSTED CONTEXT statement:

ADDRESS

IP address or domain name. (The protocol is restricted to TCP/IP only.)

SERVAUTH

A resource in the RACF® SERVAUTH class.

ENCRYPTION

Minimum level of encryption for the connection:

NONE: No encryption. This is the default value.
LOW: DRDA data stream encryption.
HIGH: Secure Sockets Layer (SSL) encryption.

Procedure

To call a stored procedure in trusted contexts:

Define a role by issuing the CREATE ROLE statement.
A role is a database entity that groups together one or more privileges and that can be assigned to users by using a trusted context. A role can be used in conjunction with a trusted context and stored procedures to identify one or more authorization IDs that can execute a stored procedure.
For example, assume that you want to call stored procedure DEVL7083.EMPDTL1C, which resides on Db2 subsystem DB9A by using authorization ID PAOLORW. Assume also that you want to define a role called SP_CALLER for use by PAOLORW. You can issue the following SQL statement:
```
CREATE ROLE SP_CALLER;
```
Grant the EXECUTE privilege on a stored procedure to that role.
For example, to grant the EXECUTE privilege to the role called SP_CALLER for the stored procedure named EMPDTL1C, you can issue the following statement:
```
GRANT EXECUTE ON PROCEDURE DEVL7083.EMPDTL1C TO ROLE SP_CALLER;
```
Have an authorized user bind the stored procedure package.
The user either needs SYSADM authority or must have explicitly bind authority for that stored procedure.
For example, assume that an authorized user wants to bind stored procedure DEVL7083.EMPDTL1C into stored procedure package DEVL7083.EMPDTL1CPKG. You can issue the following statement:
```
BIND PACKAGE(DEVL7083) MEMBER(EMPDTL1CPKG)   
```
Grant the EXECUTE privilege on the stored procedure package to the authorization ID or role that needs to run it.
For example, to grant the EXECUTE privilege on stored procedure package DEVL7083.EMPDTL1CPKG to the role named SP_CALLER, you can issue this statement:
```
GRANT EXECUTE ON PACKAGE DEVL7083.EMPDTL1CPKG TO ROLE SP_CALLER;
```
Define the trusted context.
For example, assume that you want to define a trusted context named TRUSTED_EMPDTL1C that uses:
- System authorization ID PAOLORW
- Default role SP_CALLER
- IP address 9.30.28.113
To define this trusted context, you can issue the following statement:
```
CREATE TRUSTED CONTEXT TRUSTED_EMPDTL1C
BASED UPON CONNECTION USING SYSTEM AUTHID PAOLORW
ATTRIBUTES (ADDRESS '9.30.28.113')
DEFAULT ROLE SP_CALLER
ENABLE;
```
Optional: Verify that the authorization ID can execute the stored procedure by running the application program that invokes the stored procedure and looking at the system output.
For example, assume that an application named CALLEMPD uses a CALL :host-variable statement to invoke the stored procedure named DEVL7083.EMPDTL1C. Assume also that the application program generates trace output. You might see the following system output:
```
DEVL7083.CALLEMPD - Run started.
Data returned in result sets is limited to the first 50 rows.
Data returned in result set columns is limited to the first 100 
     bytes or characters.
DEVL7083.CALLEMPD - Calling the stored procedure.
DEVL7083.CALLEMPD - Run completed.
```