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 items:
- 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
- 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' )
- 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' )