by Dirk Fechner | Published November 28, 2018
AnalyticsData managementHybrid Cloud
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:
See the Resources section at the end of this article for information about these topics.
Communications Database (CDB)
The CDB is not really a separate database. Instead the CDB consists of a few Db2 z/OS tables containing configuration data for access to remote database systems.
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:
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.
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
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.
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
For remote access to a Db2 LUW database using a trusted context, the following entries have to be made to the CDB tables.
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.
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 (
A unique location name for the accessible server. This is the name by which the remote server is known to local Db2 SQL applications.
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.
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 (
TYPE = S
The row is used to obtain the system authorization ID for establishing a trusted connection.
Authorization ID of the proxy user used to establish the trusted connection between Db2 z/OS and Db2 LUW.
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 of the proxy user.
Listing 3. Sample INSERT-Statement for SYSIBM.USERNAMES
INSERT INTO SYSIBM.USERNAMES (
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
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
NO DEFAULT ROLE
WITH USE FOR PUBLIC WITHOUT AUTHENTICATION
If the trusted connection from Db2 z/OS to Db2 LUW cannot be established for any reason, the user switch request will fail. In this case, you might receive an error that does not really indicate the real cause of the problem, e.g. SQLCODE = -30020, ERROR: EXECUTION FAILED DUE TO A DISTRIBUTION PROTOCOL ERROR THAT CAUSED DEALLOCATION OF THE CONVERSATION: REASON 124C. A common reason for this problem is that the trusted context is not defined with the correct domain name(s)/IP-addresses. So this should be checked first. See the resources section for more tips concerning trusted context problem
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.
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:
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:
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
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
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
AUTHORIZATION "DBA0007" PASSWORD "pwzosdba"
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
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
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’).
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
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
NO DEFAULT ROLE
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.
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
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
OPTIONS (REMOTE_AUTHID 'ZOSPROXY', REMOTE_PASSWORD 'pwzosproxy');
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.
The following resources provide basic knowledge to help you understand the solutions/scenarios described in this article:
May 2, 2019
Back to top