Scenario: End-to-end federated trusted connections, with user mappings

User mappings are required when users do not have the same user ID and password on the federated server and on the remote data source. For this scenario, you create trusted user mappings, as well as configure trusted contexts.

User mapping requirements

The federated server receives inbound connection requests and makes outbound connection requests to a remote data source. When users have the same user ID and password on the federated server and on the remote Db2® database server, user mappings are not required. However, when user credentials do not match, a user mapping is required. The user mapping maps the user's user ID on the federated server to the user's user ID, and to the user's password if one is specified, on the remote database server.

In a federated system that uses end-to-end trusted contexts, users whose name and password on the federated server and on the remote Db2 database server do not match require trusted user mapping. A trusted user mapping specifies that the user has permission to use a trusted context. To create a trusted user mapping or alter an existing user mapping, you set the USE_TRUSTED_CONTEXT user mapping option to 'Y'.

Who can create and alter a trusted user mapping is carefully controlled. Only a user who has SECADM authority can create or drop a trusted user mapping or alter an existing user mapping to add, set, or drop the USE_TRUSTED_CONTEXT user mapping option. A user who has a trusted user mapping can alter only the REMOTE_PASSWORD option of his own user mapping.

The scenario

Here is a simple graphic that illustrates a typical multi-tier federated system that uses user mappings. Although this scenario includes an application server, any database client can establish a trusted connection.

An end-to-end federated trusted connection scenario that uses user mappings

This scenario has four users:

BOSS, who is the connection originator, has the same user ID and password on the federated server and on the remote Db2 database server. Therefore, BOSS does not have a user mapping .
Mary does not have a user mapping. She uses the same user ID on both the federation server and on the Db2 database server. Because the trusted context specifies that PUBLIC can reuse the connection without authenticating, Mary's password is not required.
Alice has a trusted user mapping that specifies the REMOTE_AUTHID option. Alice has a different user ID on the federated server and on the remote Db2 database server. Because the trusted context specifies that anyone can reuse the connection without authenticating, Alice's password is not required.
On the federated server, Emma uses the user ID EMMA. This user ID maps to the user ID EGREENE and the password MYPASS on the Db2 database server. Because the trusted context specifies that Emma must authenticate, Emma has a trusted user mapping that specifies both the REMOTE_AUTHID option and the REMOTE_PASSWORD option.

This scenario has three servers:

The application server, which hosts the insurance application and has the IP address 9.44.111.111.
The federation server, which has the IP address 9.44.111.222.
The remote Db2 database server, which is cataloged on the federated server as JUPITER.

To configure this scenario, the SECADM completes these steps:

On the remote Db2 database server, create the trusted context object:
```
CREATE TRUSTED CONTEXT MY_DB2_TCX

BASED UPON CONNECTION USING

SYSTEM AUTHID BOSS

ATTRIBUTES (ADDRESS '9.44.111.222')

WITH USE FOR EMMA WITH AUTHENTICATION,

PUBLIC WITHOUT AUTHENTICATION

ENABLE
```
This trusted context specifies that BOSS is the trusted connection originator and that the request for the connection must come from the federated server that has the IP address 9.44.111.222. After the trusted connection is established, any valid database user can reuse the connection by providing only a user ID.
On the federated server, create the trusted context object:
```
CREATE TRUSTED CONTEXT MY_WFS_TCX

BASED UPON CONNECTION USING

SYSTEM AUTHID BOSS

ATTRIBUTES (ADDRESS '9.44.111.111')

WITH USE FOR EMMA WITH AUTHENTICATION,

PUBLIC WITHOUT AUTHENTICATION

ENABLE
```
This trusted context specifies that BOSS is the trusted connection originator and that the request for the connection must come from IP address 9.44.111.111, which identifies the application server. After the trusted connection is established, Emma can reuse the connection, but she must authenticate. Any valid database user must provide only a user ID to reuse the connection.
On the federation server, create this server definition:
```
CREATE SERVER JUPITER TYPE db2/udb 

VERSION 9.5 WRAPPER drda

OPTIONS(DBNAME 'remotedb', ...);
```
This server definition contains the information that the federated server requires to connect to the remote Db2 database named remotedb.
On the federation server, create this trusted user mapping for Alice:
```
CREATE MAPPING FOR USER ALICE 

SERVER JUPITER

OPTIONS

(REMOTE_AUTHID 'AJACKSON', USE_TRUSTED_CONTEXT 'Y');
```
This user mapping specifies that a trusted connection can be reused by user ALICE who maps to user ID AJACKSON and on the remote Db2 database server.
On the federation server, create this trusted user mapping for Emma:
```
CREATE MAPPING FOR USER EMMA 

SERVER JUPITER

OPTIONS

(REMOTE_AUTHID 'EGREENE', REMOTE_PASSWORD 'MYPASS', USE_TRUSTED_CONTEXT 'Y');
```
This user mapping specifies that a trusted connection can be reused by user EMMA who maps to user ID EGREENE and password MYPASS on the remote Db2 database server.

The scenario, step-by-step

The application server requests a trusted inbound connection for BOSS.
BOSS performs a task, and the user ID BOSS is propagated through the federated server to the Db2 database server, where the actions that BOSS performs can be audited.
Emma logs into the insurance application, which is hosted on the application server. The application server requests that the federated inbound connection be switched from BOSS to Emma, after authenticating Emma.
Emma performs a task within the application.
The federated server switches the federated outbound connection from BOSS to Emma, and her user mapped user ID and password are propagated through the federated server to the Db2 server, where the actions that EGREENE (Emma's remote user ID) performs can be audited.
Alice logs into the insurance application. The application server requests that the federated inbound connection be switched from Emma to Alice, without authenticating Alice.
Alice performs a task within the application.
The federated server switches the federated outbound connection from Emma to AJACKSON (Alice's mapped user ID), and her user ID is propagated through the federated server to the Db2 database server, where the actions that AJACKSON perform can be audited.
Mary logs into the insurance application. Mary does not require authentication; therefore, the application server switches the federated inbound trusted connection to Mary, without providing a password.
Mary performs a task within the application.
The federated server switches the federated outbound connection from AJACKSON to Mary, and Mary's user ID is propagated through the federated server to the Db2 database server, where the actions that Mary performs can be audited.