User mappings

A user mapping is an association between an authorization ID on the federated server and the information that is required to connect to the remote data source.

To create a user mapping, you specify the following information in the CREATE USER MAPPING statement:

local authorization ID
local name of the remote data source server as specified in the server definition
remote ID and password

For example:


CREATE USER MAPPING FOR <db2inst1>
   SERVER <server_name>
   OPTIONS (
      REMOTE_AUTHID '<admin>',
      REMOTE_PASSWORD '<password>);

where

db2inst1 specifies the local authorization ID in the Db2® instance. You should use the keyword USER or PUBLIC, or the Db2 instance name. USER is for current Db2 user, PUBLIC is for all Db2 users.
server_name specifies the server definition name that you defined in the CREATE SERVER statement for the JDBC data source. The user mapping is paired with the server statement.
admin specifies the remote user ID for the remote data source (for example, MySQL). The value is case-sensitive unless you set the FOLD_ID server parameter to "U" or "L" in the CREATE SERVER statement.
password specifies the remote password for the remote data source (for example, MySQL). The value is case-sensitive unless you set the FOLD_PW server option to "U" or "L" in the CREATE SERVER statement.

By default, the federated server stores user mapping in the SYSCAT.USEROPTIONS view in the global catalog and encrypts the remote passwords. As an alternative, you can use an external repository, for example a file or an LDAP server, to store user mappings. To provide the interface between the federated server and the external repository, you create a user mapping plug-in.

No matter how you store user mappings, carefully restrict access to them. If user mappings are compromised, data in the remote databases might be vulnerable to unauthorized activity.

In Federation component, you can also create public user mappings to allow all local database users to access a data source through a single remote user ID and password.

The local authorization ID could either be the system user that specifies the authorization ID that connected to the database, or the session user that specifies the current runtime authorization ID that is being used for the current session.

For example, assume that you connected to the local database using the authentication ID 'Mary'. To give 'Mary' access to the remote server, create this user mapping (assume server object 'argon' has been created):

CREATE USER MAPPING FOR Mary SERVER argon OPTIONS (REMOTE_AUTHID 'remote_ID', REMOTE_PASSWORD 'remote_pw')

'Mary' is the system user in this example. You can also create a user mapping for session user. For example, assume the session username is 'sessionuser1', you can create a user mapping as follows:

CREATE USER MAPPING FOR sessionuser1 SERVER argon OPTIONS (REMOTE_AUTHID 'remote_ID', REMOTE_PASSWORD 'remote_pw')

When you use the 'SET SESSION AUTHORIZATION' statement or 'SET SESSION_USER=' statement the value of the SESSION_USER special register changes to 'sessionuser1'; still you can connect to the remote database.

Related information:

SESSION AUTHORIZATION

SESSION_USER

SYSTEM_USER