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.
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).
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.
- 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.
- 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.
- 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.
- Specify configuration options for the user mapping to be created. Which options you can specify depends on the data source of the object for which a user mapping is being created. Each option value is a character string constant that must be enclosed in single quotation marks.
- User mappings are required only for the following data sources: the IBM® database 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.
CREATE USER MAPPING FOR <db2inst1> SERVER <server_name> OPTIONS ( REMOTE_AUTHID '<admin>', REMOTE_PASSWORD '<password>);
- db2inst1 specifies the local authorization ID in the Db2® instance. You should use the keyword USER or PUBLIC, or the Db2 instance name. USER is for current Db2 user, PUBLIC is for all Db2 users.
- server_name specifies the server definition name that you defined in the CREATE SERVER statement for the JDBC data source. The user mapping is paired with the server statement.
- admin specifies the remote user ID for the remote data source (for example, MySQL). The value is case-sensitive unless you set the FOLD_ID server parameter to "U" or "L" in the CREATE SERVER statement.
- password specifies the remote password for the remote data source (for example, MySQL). The value is case-sensitive unless you set the FOLD_PW server option to "U" or "L" in the CREATE SERVER statement.