If you have installed the distributed join installation feature and set the DBM configuration variable federated to 'YES', your DB2 system is operating as a federated system. Database authentication settings in a federated system differ slightly from standard DB2 definitions. More importantly, in a federated system you must consider the authentication requirements of your data sources. In general, data sources (DB2, Oracle, DB2 for OS/390, and so on) are set up to require authentication. That means you must ensure that IDs and passwords (as required) can flow to data sources. DB2 provides several methods for supporting authentication at data sources, all of which are explained in this section.
Exercise extreme caution when using CLIENT authentication. Consider this form of authentication only for secure networks. A user has SYSADM authority for the federated database when the following conditions are met:
Exercise caution when authentication is set to DCS. Authentication is done at neither the client nor at DB2. Any user who knows the SYSADM authentication name can assume SYSADM authority for the federated server.
Other DB2 authentication settings are possible, and one or more can result in the availability of a password at DB2 for transmission to data sources. If DB2 and client authentication settings result in the transmission of a password to DB2, that password is available for additional authentication processing at data sources. See Table 22 for more information.
There are four ways to control the transmission of authentication information to data sources: DB2 authentication settings, user mappings, server options, and APPC security settings:
The purpose of this section is to clarify how authentication settings influence global authentication processing in a federated system (the definitions for authentication settings are in Authentication Settings). For example, if DB2 authentication is set to SERVER or DCS, a user ID and password are required for a connection. Therefore, a user ID and password are available for transmission to data sources. If authentication is set to DCE or CLIENT, and authentication is not taking place at the DB2 system containing the federated database, only a user ID is available. If data source authentication processing requires a password (or perhaps a different user ID and a password), you must create a user mapping. If authentication is set to CLIENT, and the trust_clntauth parameter setting is SERVER, it is possible that a password is sent to DB2 and that it is available for transmission to data sources.
DB2 can send either the authorization name used to connect to DB2 or an authorization name defined at DB2. User mappings store authorization names defined at DB2. They are created with the CREATE USER MAPPING statement.
User mappings are flexible: you can map an ID to a new ID and password or just a password. You can use them to provide missing information or to change an ID and password to values accepted at the data source.
To create or alter a user mapping, you must hold one of the SYSADM or DBADM authorities, or your authentication ID must match the authorization name specified for the statement.
An example of a user mapping statement is:
CREATE USER MAPPING FOR "SHAWN" SERVER DB21 OPTIONS (REMOTE_AUTHID "SHAWNBCA", REMOTE_PASSWORD "MAPLELEAF")
where a DB2 authentication ID (SHAWN) is mapped to the remote ID SHAWNBCA and remote password MAPLELEAF for a server named DB21.
If the only difference between the authorization name (or password) at DB2 and the authorization name (or password) at the data source is the case of the passed string, consider using server options to fold the case to the desired setting instead of creating new IDs and passwords. See Server Options for more information.
You must create a user mapping when your authentication setting is DCE and a data source requires authentication processing (a password is expected). DB2 will only pass the DCE user ID to data sources. A password must be mapped to that user ID and then sent to the data source.
Server options can be used to provide overall authentication support. Use them to indicate if passwords are passed to data sources (typically yes) and whether user IDs and passwords need to be folded to uppercase or lowercase. Server options are set using the CREATE SERVER, ALTER SERVER, and SET SERVER OPTION statements.
Server options specific to authentication processing are discussed in the rest of this section. A more complete list of server options is in Using Server Options to Help Define Data Sources and Facilitate Authentication Processing.
The default setting for password is 'Y' (passwords are sent to data sources). Leave or set this option to 'Y' for all cases where a data source will perform authentication and is not expecting an encrypted password.
DB2 can transmit encrypted passwords. Set the server option password to 'ENCRYPTION' if passwords should be sent in an encrypted form to DB2 Family data sources. It is recommended that you set password to 'ENCRYPTION' if your authentication setting at DB2 is DCS_ENCRYPT or SERVER_ENCRYPT.
A user ID is always sent to data sources.
ID and Password Folding Options:
Authorization names and passwords, in some cases, might need to change. Different data sources can have different authorization name and password requirements (regarding the use of uppercase or lowercase) for IDs and passwords.
DB2 provides two server options that can help you resolve naming differences. The option names are fold_id and fold_pw, and their settings are:
The null setting might seem attractive because it covers many possibilities. However, from a performance perspective, it is best to set these options so that only one attempt is made for connections. If both the fold_id and fold_pw options are set to null, it is possible that DB2 will make four attempts to send the authorization name and password:
If you are connecting to a DRDA data source, across APPC, that requires a user ID and password, or if your authentication setting is DCS and you are authenticating at a DRDA data source, ensure that your APPC security setting is PROGRAM for the connection between DB2 and that data source.
This section provides an overview of federated system authentication and authorization steps. See Figure 23 for an overview of federated database authentication and authorization processing.
Figure 23. Federated Database Authentication and Authorization Processing
|
The task in this scenario is to enable the user DJINSTL to perform a UNION operation against two nicknames (NN1 and NN2). The nicknames represent two tables. One data source is a DB2 for OS/390 system where DJINSTL has a different user ID and password (see Figure 23) named MVS1. A user mapping will be required to access information at MVS1. The other data source is a DB2 system where DJINSTL's ID and password are the same. This data source, DB21, simply requires that the user ID and password are sent in uppercase.
DB2 authentication is set to SERVER. DJINSTL will access DB2 from a Windows NT client across a TCP/IP connection. The connection from DB2 to DB2 for OS/390 is also TCP/IP. The federated database name is DJDB1.
First ensure that DB2 is expecting a password and that a password is being sent. Also, ensure that the client and server authentication types match. Check the DB2 server authentication type by issuing the command:
GET DATABASE MANAGER CONFIGURATION
from the DB2 server. Check the client authentication type by issuing the command:
LIST DATABASE DIRECTORY
from the client. In both cases, ensure that authentication is set to SERVER. If the setting for the client is DCS or CLIENT, you can change it by using the UNCATALOG DATABASE and CATALOG DATABASE commands.
Next, ensure that passwords will be sent to the data sources. After connecting to the federated database DJDB1, issue the commands:
ALTER SERVER MVS1 OPTIONS (SET password 'Y') ALTER SERVER DB21 OPTIONS (SET password 'Y')
Next, ensure that passwords are sent to the DB21 data source in the proper case:
ALTER SERVER DB21 OPTIONS (ADD fold_id 'U') ALTER SERVER DB21 OPTIONS (ADD fold_pw 'U')
The next step is to grant privileges allowing the user DJINSTL to connect to the federated database DJDB1 and select nicknames:
GRANT CONNECT ON DATABASE DJDB1 TO DJINSTL;
Now, map DJINSTL's DB2 ID and password to the correct user ID and password for the MVS1 server:
CREATE USER MAPPING FOR "DJINSTL" SERVER MVS1 OPTIONS (REMOTE_AUTHID "SHAWN", REMOTE_PASSWORD "MVS4YOU")
At this point, the DB2 user ID DJINSTL can send requests to data sources. Additional steps might be required to access data source objects referenced by nicknames (privileges are usually required for tables and views referenced by nicknames).