CREATE USER MAPPING (Define user mapping)

Function

When accessing a foreign database using a foreign database user ID and a password that are different from the authorization identifier and the password used for the execution of an SQL statement in HiRDB, defines a mapping of authorization identifiers and passwords.

Privileges

Users with the DBA privilege
These users can define user mapping for any user.

Format

CREATE USER MAPPING
  FOR PUBLIC
  SERVER foreign-server-name
  OPTIONS (option-name 'option-value' [, option-name 'option-value']...)

Operands

Defines user mapping for all authorized identifiers that are registered in HiRDB. The user mapping is also effective for authorization identifiers that are added after the execution of CREATE USER MAPPING.

Specifies the name of the foreign server that is accessed by converting the authorization identifier and the password.

Specify any of the following options; each option can be specified only once:

USER 'user-ID'
Specifies the user ID of the foreign database that is used for access to a foreign database.
The operand user-ID can be specified in a maximum of 30 bytes. This is a required option.
PASSWD 'password'
Specifies the password for the foreign database to be used for access to a foreign database.
The operand password can be specified in a maximum of 30 bytes. The default is for HiRDB to connect to the foreign database without a password. For details about how to establish a connection to a foreign database without a password, see the respective DBMS manuals.

Common rules

  1. If access is made to a foreign server for which user mapping is not defined, a runtime error may occur. When defining a foreign server using CREATE SERVER, be sure to define user mapping at the same time.
  2. If user mapping is defined, any access to a foreign database, specified in foreign-server-name, is performed using the user ID and the password that are specified in OPTIONS.
  3. Executing CREATE USER MAPPING for a second time on a foreign server for which user mapping is previously defined may result in a runtime error.

Notes

  1. To change a user mapping definition, first delete the user mapping by using DROP USER MAPPING, and then define it by using CREATE USER MAPPING.
  2. CREATE USER MAPPING cannot be specified from an X/Open compliant UAP running under OLTP.