DB2 Version 9.7 for Linux, UNIX, and Windows

ALTER USER MAPPING statement

The ALTER USER MAPPING statement is used to change the authorization ID or password that is used at a data source for a specified federated server authorization ID.

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 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 altering a public user mapping, the privileges held by the authorization ID of the statement must include DBADM authority.

Syntax


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

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

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

Description

authorization-name

Specifies the authorization name under which a user or application connects to a federated database.

USER

The value in the special register USER. When USER is specified, then the authorization ID of the ALTER USER MAPPING statement will be mapped to the data source authorization ID that is specified in the REMOTE_AUTHID user 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

Identifies the data source accessible under the remote authorization ID that maps to the local authorization ID that's denoted by authorization-name or referenced by USER.

OPTIONS

Indicates what user options are to be enabled, reset, or dropped for the mapping that is being altered.

ADD: Enables a user option.
SET: Changes the setting of a user option.
user-option-name: Names a user option that is to be enabled or reset.
string-constant: Specifies the setting for user-option-name as a character string constant.
DROP user-option-name: Drops a user option.

Notes

A user option cannot be specified more than once in the same ALTER USER MAPPING statement (SQLSTATE 42853). When a user option is enabled, reset, or dropped, any other user options that are in use are not affected.
An ALTER USER MAPPING statement within a given unit of work (UOW) cannot be processed (SQLSTATE 55007) if the UOW already includes one of the following:
- A SELECT statement that references a nickname for a table or view at the data source that is to be included in the mapping
- An open cursor on a nickname for a table or view at the data source that is to be included in the mapping
- Either an INSERT, DELETE, or UPDATE issued against a nickname for a table or view at the data source that is to be included in the 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: Jim uses a local database to connect to an Oracle data source called ORACLE1. He accesses the local database under the authorization ID KLEEWEIN; KLEEWEIN maps to CORONA, the authorization ID under which he accesses ORACLE1. Jim is going to start accessing ORACLE1 under a new ID, JIMK. So KLEEWEIN now needs to map to JIMK.

   ALTER USER MAPPING FOR KLEEWEIN
     SERVER ORACLE1
     OPTIONS ( SET REMOTE_AUTHID 'JIMK'  )

Example 2: Mary uses a federated database to connect to a DB2® for z/OS® data source called DORADO. She uses one authorization ID to access DB2 and another to access DORADO, and she has created a mapping between these two IDs. She has been using the same password with both IDs, but now decides to use a separate password, ZNYQ, with the ID for DORADO. Accordingly, she needs to map her federated database password to ZNYQ.

   ALTER USER MAPPING FOR MARY
     SERVER DORADO
     OPTIONS ( ADD REMOTE_PASSWORD 'ZNYQ'  )