Altering a user mapping

A user mapping is the association between the authorization ID at the federated server and the authorization ID at the data source. User mappings enable distributed requests can be sent to the data source.

Before you begin

If the authorization ID that issues the statement is different than the authorization ID that is mapped to the data source, ensure that the authorization ID that issues the statement has either either SYSADM or DBADM authority on the federated database.

About this task

The federated server cannot process an ALTER USER MAPPING statement within a particular unit of work (UOW) if the UOW already includes one of the following statements:
  • A SELECT statement that references a nickname for a table or view at the data source the mapping includes.
  • An open cursor on a nickname for a table or view at the data source that the mapping includes.
  • An INSERT, DELETE, or UPDATE statement that is issued for a nickname of a table or view at the data source that the mapping includes.

Procedure

Use one of the following methods:

  • Command line:

    Issue the ALTER USER MAPPING statement.

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

    Example: Jenny uses the federated server to connect to a Sybase server that is called SYBSERVER. She accesses the federated server with the authorization ID of jennifer. The authorization ID jennifer is mapped to the authorization ID jenn on the Sybase server. The authorization ID for Jenny on the Sybase server is changed to jen123. The ALTER USER MAPPING statement to map jennifer to jen123 is as follows: 

    ALTER USER MAPPING FOR jennifer SERVER SYBSERVER
   OPTIONS (SET REMOTE_AUTHID 'jen123')

    Tomas uses the federated server to connect to an Oracle server that is called ORASERVER. He accesses the federated server with the authorization ID of tomas. The authorization ID tomas is mapped to the authorization ID tom on the Oracle server. The password for Tomas on the Oracle server is changed to day2night. The ALTER USER MAPPING statement to map tomas to the new password is as follows:

    ALTER USER MAPPING FOR tomas SERVER ORASERVER
   OPTIONS (SET REMOTE_PASSWORD 'day2night')

    The REMOTE_AUTHID and REMOTE_PASSWORD user options are case sensitive unless you set the FOLD_ID and FOLD_PW server options to 'U' or 'L' in the CREATE SERVER statement.

  • Web console:
    1. Click Administer > Remote Tables.
    2. Click Manage Servers.
    3. Select one or more servers.
    4. Click Manage User Mappings.