Introduction

In today’s world, many companies run a huge part of their applications on Linux, UNIX, and Windows (LUW) systems, while legacy core applications still run on mainframe systems like z/OS. In most of these cases, applications run on different platforms, and the corresponding data also resides in data stores on different platforms. Therefore, companies typically using IBM Db2 z/OS as data store for their mainframe applications are using Db2 LUW as data store for their applications running on LUW systems. Data stored in Db2 z/OS and Db2 LUW, however, is seldomly isolated from each other. Many applications have the need to access data in their own data store (e.g., Db2 z/OS) as well as data placed in a data store on the other platform (Db2 LUW). Usually, such remote data access scenarios lead to user authentication challenges since the different platforms use different authentication mechanisms. Typically, authentication on z/OS is done using RACF, whereas authentication in the LUW-world is done using Active Directory or LDAP. In this article, you’ll learn how a user’s identity can be propagated from Db2 z/OS to Db2 LUW and vice versa, without the need for authentication on the remote system.

This article assumes you have some basic knowledge about:

  • Trusted contexts/trusted connections
  • The Db2 z/OS Communications Database (CDB)
  • Db2 LUW’s Federation Component

See the Resources section at the end of this article for information about these topics.

Db2 z/OS to Db2 LUW user-ID translation problem

In general, when a remote connection from Db2 z/OS to Db2 LUW is established, an outbound translation of the z/OS user-ID to a LUW user-ID occurs. This applies to z/OS technical users as well as to z/OS personal users. The user mapping is defined in one of the Communications Database (CDB) tables: SYSIBM.USERNAMES. In this table, a z/OS user-ID is mapped to a LUW user-ID + password. For a limited number of z/OS technical users (e.g. technical batch users), this approach is feasible, because they can be mapped to corresponding LUW technical users that have a fixed password. For personal users, however, this approach is not feasible, because:

  • A mapping for each possible personal z/OS user to the corresponding LUW user and password would be required.
  • In general, the passwords of personal LUW users must be changed every X months.
  • The passwords of personal LUW users must not be known to other persons (like the Db2 z/OS DBA who maintains the SYSIBM.USERNAMES entries).

As an alternative, a public user mapping could be defined in SYSIBM.USERNAMES, i.e., each (personal) z/OS user-ID is mapped to the same (technical) LUW user-ID. However, this approach introduces a security breach, because a user’s identity gets lost for the remote access and each and every user has the same privileges when accessing the remote Db2 LUW database.

Solution to the user-ID translation problem

Usage of a trusted context in Db2 LUW represents a solution to the user-ID translation problem. A trusted context definition in the Db2 LUW database allows Db2 z/OS to establish a trusted connection to Db2 LUW using a proxy LUW technical user-ID and a corresponding password. For a trusted connection, the proxy technical LUW user-ID used to establish the trusted connection can be switched to any other user-ID without requiring authentication provided that the trusted context is defined accordingly. Db2 z/OS makes use of this trusted connection feature to switch to the z/OS technical or personal user-ID executing the z/OS application right after the trusted connection has been established. The switch occurs automatically and does not require any changes to the application code. As a result, the authorization-ID of the connection to the Db2 LUW database is identical to the z/OS user-ID and Db2 LUW database privileges can be granted to the z/OS user-ID.

The following scenario illustrates usage of a trusted context for remote access from Db2 z/OS to Db2 LUW:

The user John Doe has a RACF user-ID “USER4711” which he usually uses to execute SQL-statements in the Db2 z/OS database via SPUFI (short name of the z/OS-tool “SQL Processing Using File Input”). Now John would also like to query a remote table in a Db2 LUW database via SPUFI. A z/OS-administrator has configured remote access to the Db2 LUW database in the CDB using the trusted context approach described above. The proxy user-ID that has been configured for establishing trusted connections to the Db2 LUW database is “LUWPROXY”. The figure below shows what happens when John executes the remote query on the Db2 LUW table (see description of steps 1 to 8 in the figure). The quintessence is that John accesses the remote Db2 LUW table with the same authorization-ID that he uses on z/OS, namely “USER4711”. So only if SELECT-privilege has been granted to “USER4711” in the Db2 LUW database, the query will succeed. Besides “USER4711” must also have CONNECT-privilege for the Db2 LUW database, otherwise the switch of the user-ID on the trusted connection will fail.

Figure 1. Db2 z/OS to Db2 LUW remote access using a trusted context

Figure 1. Db2 z/OS to Db2 LUW Remote Access Using a Trusted Context
Flow Chart

To better illustrate the switch of the user-ID, the figure above shows the switch as part of the connect request. Strictly speaking, the switch of the user-ID does not happen until the first SQL-statement is executed via the trusted connection.

Configuring Db2 z/OS to Db2 LUW remote access using a trusted context

Configure access to Db2 LUW database in Db2 z/OS CDB

Gather the following data required for configuring access to the Db2 LUW database using a trusted context:

Table 1. Data required for configuring access to the Db2 LUW database

Configuration Parameter Description Sample Value
Db2 LUW Database Name Name of the Db2 LUW database SAMPLE
Db2 LUW Server Domain Name or IP-Address Domain name or IP-address of the server hosting the Db2 LUW database luwsrv42.de.ibm.com
Db2 LUW Listener Port Listener port of the Db2 LUW database 50000
Db2 LUW Proxy User-ID ID of the proxy user for establishing the trusted connection to the Db2 LUW database LUWPROXY
Db2 LUW Proxy User Password Corresponding password pwluwproxy
Link Name This link name is only an identifier used by the CDB to link together the entries in the different CDB tables LUWSAMPLE

For remote access to a Db2 LUW database using a trusted context, the following entries have to be made to the CDB tables.

SYSIBM.IPNAMES

LINKNAME = The value specified in this column must match the value specified in the LINKNAME column of the associated row in SYSIBM.LOCATIONS.

SECURITY_OUT = P Outbound connection requests contain an authorization ID and a password. The password is obtained from the SYSIBM.USERNAMES table.

USERNAMES = S Row in the SYSIBM.USERNAMES table is used to obtain the system AUTHID used to establish a trusted connection. The letter S signifies the system authorization ID, within a trusted context, obtained from the SYSIBM.USERNAMES table. If the system authorization ID that is specified in the AUTHID column is different from the primary authorization ID, Db2 sends the switch user request on behalf of the primary authorization ID after successfully establishing the trusted connection.

IPADDR = This column contains an IPv4 or IPv6 address, or domain name of a remote TCP/IP host.

Listing 1. Sample INSERT-Statement for SYSIBM.IPNAMES

INSERT INTO SYSIBM.IPNAMES (
LINKNAME,
SECURITY_OUT,
USERNAMES,
IBMREQD,
IPADDR
)
VALUES (
'LUWSAMPLE',
'P',
'S',
'N',
'luwsrv42.de.ibm.com'
);

SYSIBM.LOCATIONS

LOCATION = A unique location name for the accessible server. This is the name by which the remote server is known to local Db2 SQL applications.

LINKNAME = A row exists in SYSIBM.IPNAMES whose LINKNAME matches the value specified in the SYSIBM.LOCATIONS LINKNAME column. This row specifies the TCP/IP communication attributes for the remote location.

PORT = TCP/IP port number of the remote database server.

TRUSTED = Y Location is trusted. Access to the remote location requires trusted context defined at the remote location.

Listing 2. Sample INSERT-Statement for SYSIBM.LOCATIONS

INSERT INTO SYSIBM.LOCATIONS (
LOCATION,
LINKNAME,
IBMREQD,
PORT,
TRUSTED,
SECURE
)
VALUES (
'SAMPLE',
'LUWSAMPLE',
'N',
'50000',
'Y',
'N'
);

SYSIBM.USERNAMES

TYPE = S The row is used to obtain the system authorization ID for establishing a trusted connection.

AUTHID = Authorization ID of the proxy user used to establish the trusted connection between Db2 z/OS and Db2 LUW.

LINKNAME = A row exists in SYSIBM.IPNAMES whose LINKNAME matches the value specified in the SYSIBM.USERNAMES LINKNAME column. This row specifies the TCP/IP host associated with this name translation rule.

NEWAUTHID = Blank This value is not used in case of a trusted connection. Therefore it only contains a blank character.

PASSWORD = Password of the proxy user.

Listing 3. Sample INSERT-Statement for SYSIBM.USERNAMES

INSERT INTO SYSIBM.USERNAMES (
TYPE,
AUTHID,
LINKNAME,
NEWAUTHID,
PASSWORD,
IBMREQD
)
VALUES (
'S',
'LUWPROXY',
'LUWSAMPLE',
' ',
'pwluwproxy',
'N'
);

Create trusted context in Db2 LUW database

Gather the following data required for creating the trusted context in the Db2 LUW database:

Table 2. Data required for creating the trusted context in the Db2 LUW database

Configuration Parameter Description Sample Value
Db2 LUW Trusted Context Name An arbitrary name for the trusted context object to be created in the Db2 LUW database FROM_ZOS_CTX
Db2 LUW Proxy User-ID ID of the proxy user for establishing the trusted connection to the Db2 LUW database. This user only needs CONNECT privilege for the Db2 LUW database LUWPROXY
Db2 z/OS Server Domain Names or IP-Addresses Domain names or IP-addresses of the z/OS server. If the Db2 z/OS subsystem consists of several members and each member has its own domain name/IP address, then all domain names/IP addresses have to be specified in the trusted context definition zossrv01.de.ibm.com,zossrv02.de.ibm.com,zossrv03.de.ibm.com

Execute and create trusted context-statement like the following to create the trusted context in the Db2 LUW database.

Listing 4. Sample trusted context definition in Db2 LUW database

CREATE TRUSTED CONTEXT FROM_ZOS_CTX
BASED UPON CONNECTION USING SYSTEM AUTHID LUWPROXY
ATTRIBUTES (
ADDRESS 'zossrv01.de.ibm.com',
ADDRESS 'zossrv02.de.ibm.com',
ADDRESS 'zossrv03.de.ibm.com'
)
NO DEFAULT ROLE
ENABLE
WITH USE FOR PUBLIC WITHOUT AUTHENTICATION
;

The trusted context definition includes the proxy user-ID and the domain names/IP-addresses of the servers from which a trusted connection must originate. The clause WITH USE FOR PUBLIC WITHOUT AUTHENTICATION indicates that switching the user-ID on a trusted connection is allowed for every user-ID and does not require a password to be provided.

Db2 LUW to Db2 z/OS user-ID translation in a federation scenario

Now that we know how we can implement propagation of a user’s identity from Db2 z/OS to Db2 LUW, let’s have a look at the opposite direction.

The trusted context solution doesn’t only work for access from z/OS to the Db2 LUW database, but also in federation scenarios. Like the CDB on z/OS, the Db2 LUW federation component can be configured to establish trusted connections to remote databases. The Db2 LUW federation component supports trusted contexts in two flavors:

  • For an inbound non-trusted connection, the federation component can establish an outbound trusted connection to the remote database. In addition, the federation component can be configured to map the user-ID that established the inbound connection to another user-ID on the outbound connection.
  • For an inbound trusted connection, the federation component can establish an outbound trusted connection to the remote database and propagate a user-ID switch on the inbound connection to the outbound connection.

Don’t worry if these features of the Db2 LUW federation component are not 100 percent clear to you just by reading the short descriptions. In the following, we’ll look at two scenarios that illustrate the usage of these features.

We will start with exploring the first case, i.e. for an inbound non-trusted connection, the federation component can establish an outbound trusted connection to the remote database. Assume the following scenario:

The user John Doe has an Active Directory user-ID “JDOE” with which he can connect to the Db2 LUW database. In addition, as we already know, he has a RACF user-ID “USER4711” with which he can connect to the Db2 z/OS database. Now John directly connects to the Db2 LUW database using Db2 CLP (Command Line Processor), IBM Data Studio, or any other SQL-client tool on his personal workstation. The user-ID “JDOE” that he specifies for the connect is authenticated against the Active Directory (assuming that the authentication mechanism of the Db2 LUW instance is configured accordingly). After successful authentication, John is connected to the Db2 LUW database via a non-trusted connection. So why is this connection non-trusted? The simple answer is: Because the connection has not been established via a trusted context. Now John wants to execute a federated query that joins a local Db2 LUW table with a remote Db2 z/OS table. His RACF user-ID “USER4711” has the privilege to perform a SELECT on the Db2 z/OS table. At this point, a simple user mapping could be created in Db2 LUW that maps his LUW user-ID “JDOE” to the z/OS user-ID “USER4711” for remote access to the Db2 z/OS subsystem. But a simple user mapping would require to specify the password of “USER4711” as part of the user mapping. That approach would imply the same problems we already encountered before, that means:

  • The passwords of personal z/OS users must not be known to other persons like the Db2 LUW DBA who maintains the user mappings in the Db2 LUW database.
  • In general, the passwords of personal z/OS users must be changed every X months, so that the user mappings in the Db2 LUW database would have to be updated too. In reality, that is not a feasible approach.
  • Definition of a public user mapping, which is also supported by the Db2 LUW federation component, would introduce a security breach.

Again the solution is to configure the Db2 LUW federation component so that a trusted connection is used to perform the remote access to Db2 z/OS. The main difference to the previous Db2 z/OS to Db2 LUW scenario is, that the federation component can not only be configured to propagate the Db2 LUW user-ID “JDOE”without authentication to Db2 z/OS. In fact, that would not be the intended solution, since we want to use the privileges already granted to John Doe’s z/OS user-ID for accessing the remote z/OS table. The federation component can also be configured to establish a trusted connection to the remote Db2 z/OS database in combination with a user mapping from the LUW user-ID “JDOE” to the z/OS user-ID “USER4711”. So although a user mapping is defined to map the user-IDs, it does not contain a password for “USER4711”, because the user-ID switch on the trusted connection does not require authentication of “USER4711” on z/OS. The figure below shows what happens when John executes the federated query in the Db2 LUW database (see description of steps 1 to 5 in the figure).

Figure 2. Db2 LUW to Db2 z/OS remote access using a trusted context

Figure 2. Db2 LUW to Db2 z/OS Remote Access Using a Trusted Context Flow Chart

Configuring Db2 LUW to Db2 z/OS remote access using a trusted context

Configure access to Db2 z/OS database in Db2 LUW federation component

Gather the following data required for configuring access to the Db2 z/OS database using a trusted context:

Table 3. Data required for configuring access to the Db2 z/OS database

Configuration Parameter Description Sample Value
Db2 z/OS Subsystem Name Name of the Db2 z/OS subsystem as it is cataloged in the database directory of the Db2 LUW instance DB2SUB01
Db2 z/OS Version Version of the Db2 z/OS subsystem 12
Db2 z/OS User-ID User-ID for creating the server definition. This user needs BIND privilege for the Db2 z/OS subsystem DBA0007
Db2 z/OS Password Corresponding password pwzosdba
Db2 z/OS Proxy User-ID ID of the proxy user for establishing the trusted connection to the Db2 z/OS subsystem ZOSPROXY
Db2 z/OS Proxy User Password Corresponding password pwzosproxy

The following remote server definition in the Db2 LUW database assumes that the Db2 z/OS subsystem has already been cataloged in the database directory of the Db2 LUW instance.

Listing 5. Sample remote server definition in Db2 LUW database

CREATE WRAPPER DRDA;

CREATE SERVER DB2SUB001
TYPE DB2/ZOS VERSION 12
WRAPPER DRDA
AUTHORIZATION "DBA0007" PASSWORD "pwzosdba"
OPTIONS (
DBNAME 'DB2SUB001',
FED_PROXY_USER 'ZOSPROXY'
);

The main difference to a “normal” remote server definition is the option FED_PROXY_USER which specifies the proxy user-ID that will be used for establishing trusted connections to the Db2 z/OS database. The other z/OS user-ID is only required for creation of the remote server object in the Db2 LUW database and won’t be used later during runtime.

Since the server definition does not contain the password for the z/OS proxy user-ID, an additional user mapping has to be created for the proxy user.

Listing 6. Sample User Mapping for z/OS Proxy User

CREATE USER MAPPING FOR ZOSPROXY
SERVER DB2SUB01
OPTIONS (REMOTE_AUTHID 'ZOSPROXY', REMOTE_PASSWORD 'pwzosproxy');

Finally a user mapping is created for each user that needs a mapping from his Db2 LUW user-ID to his Db2 z/OS user-ID when performing the remote access to the Db2 z/OS database.

Listing 7. Sample user mapping of a Db2 LUW user-ID to the corresponding Db2 z/OS user-ID

CREATE USER MAPPING FOR JDOE
SERVER DB2SUB01
OPTIONS (REMOTE_AUTHID 'USER4711', USE_TRUSTED_CONTEXT 'Y');

This user mapping tells the federation component to establish a trusted connection (option USE_TRUSTED_CONTEXT ‘Y’) when user John Doe (with Db2 LUW user-ID “JDOE”) wants to access a remote Db2 z/OS table. Besides the user mapping indicates that after establishing the trusted connection via the proxy user, the user-ID should not be switched to “JDOE”, but to John Doe’s z/OS user-ID “USER4711” (option REMOTE_AUTHID ‘USER4711’).

Create trusted context in Db2 z/OS database

Gather the following data required for creating the trusted context in the Db2 z/OS database:

Table 4. Data required for creating the trusted context in the Db2 z/OS database

Configuration Parameter Description Sample Value
Db2 z/OS Trusted Context Name An arbitrary name for the trusted context object to be created in the Db2 z/OS database FROM_LUW_CTX
Db2 z/OS Proxy User-ID ID of the proxy user for establishing the trusted connection to the Db2 z/OS database ZOSPROXY
Db2 LUW Server Domain Name or IP-Address Domain name or IP-address of the server hosting the Db2 LUW database luwsrv42.de.ibm.com

Execute and create trusted context-statement like the following to create the trusted context in the Db2 z/OS database.

Listing 8. Sample trusted context definition in Db2 z/OS database

CREATE TRUSTED CONTEXT FROM_LUW_CTX
BASED UPON CONNECTION USING SYSTEM AUTHID ZOSPROXY
ATTRIBUTES (
ADDRESS 'luwsrv42.de.ibm.com'
)
NO DEFAULT ROLE
ENABLE
WITH USE FOR PUBLIC WITHOUT AUTHENTICATION
;

The trusted context definition looks essentially the same as the trusted context definition we already know from the Db2 z/OS to Db2 LUW scenario. It contains the proxy user-ID and the domain name/IP-address of the server from which the trusted connection must originate (in this case, this is the server hosting the Db2 LUW database). Again, the clause WITH USE FOR PUBLIC WITHOUT AUTHENTICATION indicates that switching the user-ID on a trusted connection is allowed for every user-ID and does not require a password to be provided.

Regarding usage of the WITH USE FOR PUBLIC WITHOUT AUTHENTICATION option for trusted contexts, be aware of the following implications. This is a very powerful option. Indeed, defining a trusted context with this option delegates responsibility for all users to the client. E.g., for the Db2 LUW to Db2 z/OS case, you could now have the Db2 LUW DBA create a user that matches a user on Db2 z/OS having DBADM or SECADM authority and they will be able to connect as that user since they don’t need to know that user’s password on z/OS. So there is a level of trust one system is putting in the other.

User-ID Propagation in an advanced federation scenario

We will conclude this article with an advanced federation scenario that demonstrates the second option of how the Db2 LUW federation component supports trusted connections i.e., for an inbound trusted connection, the federation component can establish an outbound trusted connection to the remote database and propagate a user-ID switch on the inbound connection to the outbound connection.

The scenario: John Doe has written a COBOL program running on the z/OS system that performs a join between a Db2 z/OS table and a Db2 LUW table. He will execute the COBOL program under his z/OS user-ID “USER4711”. Because Db2 z/OS does not support joins between tables in different databases, the COBOL program has to send the query to the Db2 LUW database where it can be executed by the federation component. Db2 z/OS as well as Db2 LUW have been configured to use trusted connections for remote access – that’s what we saw in the two previous scenarios. The interesting point in this final scenario is, that John Doe’s z/OS user-ID “USER4711” is propagated from Db2 z/OS to Db2 LUW and then back to Db2 z/OS, because the federation component automatically propagates the user-ID switch on the inbound trusted connection to the outbound trusted connection. The following figure illustrates this behaviour (see description of steps 1 to 5 in the figure).

Figure 3. Advanced federation scenario using trusted contexts

Figure 3. Advanced federation scenario using trusted contexts flow chart

To make this final scenario work, only an additional user mapping for the Db2 LUW proxy user on the inbound connection to the Db2 z/OS proxy user on the outbound trusted connection has to be created in the Db2 LUW database.

Listing 9. Sample user mapping for LUW proxy user

CREATE USER MAPPING FOR LUWPROXY
SERVER DB2SUB01
OPTIONS (REMOTE_AUTHID 'ZOSPROXY', REMOTE_PASSWORD 'pwzosproxy');

Summary

This article explains how trusted contexts/trusted connections can be used to propagate a user’s identity when performing remote accesses from Db2 z/OS to Db2 LUW and vice versa, without the need for user authentication at the remote location. It covers three different scenarios which are typcial in a distributed environment consisting of Db2 z/OS and Db2 LUW databases. Therefore, the solutions described in this article can be adopted with only minimal adjustments in similar environments.

Resources

The following resources provide basic knowledge to help you understand the solutions/scenarios described in this article: