SQL Reference

CREATE USER MAPPING

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

Syntax

>>-CREATE USER MAPPING FOR----+-authorization-name-+------------>
                              '-USER---------------'
 
>-----SERVER--server-name--------------------------------------->
 
                  .-,-----------------------------------------------.
                  V  .-ADD--.                                       |
>-----OPTIONS--(-----+------+---user-option-name--string-constant---+---)->
 
>--------------------------------------------------------------><
 

Description

authorization-name

Specifies the authorization name under which a user or application connects to a federated database. This name is to be mapped to an identifier under which the data source denoted by server-name can be accessed.

USER

The value in the special register USER. When USER is specified, then the authorization ID of the CREATE USER MAPPING statement will be mapped to the data source authorization ID that is specified in the REMOTE_AUTHID user option.

SERVER server-name

Identifies the data source that is accessible under the mapping authorization ID.

OPTIONS

Indicates what user options are to be enabled. Refer to User Options for descriptions of user-option-names and their settings.

ADD

Enables one or more user options.

user-option-name

Names a user option that will be used to complete the user mapping that is being created.

string-constant

Specifies the setting for user-option-name as a character string constant.

Notes

• A user mapping cannot be created in a given unit of work (UOW) if the UOW already includes a SELECT statement that references a nickname for a table or view at the data source that is to be included in the mapping.

Examples

Example 1: To access a data source called S1, you need to map your authorization name and password for your local database to your user ID and password for S1. Your authorization name is RSPALTEN, and the user ID and password that you use for S1 are SYSTEM and MANAGER, respectively.

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

Example 2: Marc already has access to a DB2 data source. He now needs access to an Oracle data source, so that he can create joins between certain DB2 and Oracle tables. He acquires a username and password for the Oracle data source; the username is the same as his authorization ID for the federated database, but his Oracle and federated database passwords are different. To be able to access Oracle from the federated database, he must map the two passwords together.

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