DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE USER MAPPING statement

The CREATE USER MAPPING statement defines a mapping between an authorization ID that uses a federated database and the authorization ID and password to use at a specified data source.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

If the authorization ID of the statement is different from the authorization name that is being mapped to the data source, the privileges held by the authorization ID of the statement must include DBADM authority. Otherwise, if the authorization ID and the authorization name match, no authorities or privileges are required.

When creating a public user mapping, the privileges held by the authorization ID of the statement must include DBADM authority.

Syntax


>>-CREATE USER MAPPING FOR--+-authorization-name-+-------------->
                            +-USER---------------+   
                            '-PUBLIC-------------'   

>--SERVER--server-name------------------------------------------>

               .-,--------------------------------------------------.      
               V .-ADD-.                                            |      
>--OPTIONS--(----+-----+--user-mapping-option-name--string-constant-+--)-><

Description

authorization-name

Specifies the authorization name under which a user or application connects to a federated database. The authorization_name is mapped to the REMOTE_AUTHID user mapping option.

USER

The value in the USER special register. When USER is specified, the authorization ID issuing the CREATE USER MAPPING statement is mapped to the REMOTE_AUTHID user mapping option.

PUBLIC

Specifies that any valid authorization ID for the local federated database will be mapped to the data source authorization ID that is specified in the REMOTE_AUTHID user option.

SERVER server-name

Names the server object for the data source that the authorization-name can access. The server-name is the local name for the remote server that is registered with the federated database.

OPTIONS

Indicates the options that are enabled when the user mapping is created.

ADD: Enables one or more user mapping options.
user-mapping-option-name: Specifies the name of the option.
string-constant: Specifies the setting for the user-mapping-option-name as a character string constant.

Notes

User mappings are required only for the following data sources: the DB2® family of products, Documentum, Informix®, Microsoft SQL Server, ODBC, Oracle, Sybase, and Teradata.
The REMOTE_PASSWORD option is always required for a user mapping.
Public user mappings and non-public user mappings cannot coexist on the same federated server. This means that if you have created public user mappings, you will not be able to create non-public user mappings on the same federated server. The reverse is also true, if you have created non-public user mappings, you will not be able to create public user mappings on the same federated server.

Examples

Example 1: Register a user mapping to the DB2 for z/OS® data source server object SERVER390. Map the authorization name for the local federated database to the user ID and password for SERVER390. The authorization name is RSPALTEN. The user ID for SERVER390 is SYSTEM. The password for SERVER390 is MANAGER.

  CREATE USER MAPPING FOR RSPALTEN
    SERVER SERVER390
    OPTIONS
    (REMOTE_AUTHID 'SYSTEM',
    REMOTE_PASSWORD 'MANAGER')

Example 2: Register a user mapping to the Oracle data source server object ORACLE1. MARCR is the authorization name for the local federated database and the user ID for ORACLE1. Because the authorization name and the user ID are the same, the REMOTE_AUTHID option does not need to be specified in the user mapping. The password for MARCR on ORACLE1 is NZXCZY .

  CREATE USER MAPPING FOR MARCR
    SERVER ORACLE1
    OPTIONS
    (REMOTE_PASSWORD 'NZXCZY')

Example 3: Create a DRDA wrapper and a DB2 for z/OS data source server SERVER390. Then register a public user mapping to the server object SERVER390. PUBLIC indicates any valid authorization ID for the local federated database. The user ID for SERVER390 is APP_USER. The password for SERVER390 is secret.

  CREATE WRAPPER DRDA; 
  CREATE SERVER SERVER390 
    TYPE db2/udb VERSION 9.7 WRAPPER DRDA 
    AUTHORIZATION "APP_USER" PASSWORD "secret" 
    OPTIONS (DBNAME 'remotedb');
  CREATE USER MAPPING FOR PUBLIC SERVER SERVER390 
    OPTIONS (REMOTE_AUTHID 'APP_USER', REMOTE_PASSWORD 'secret');