DB2 10.5 for Linux, UNIX, and Windows

ALTER TRUSTED CONTEXT statement

The ALTER TRUSTED CONTEXT statement modifies the definition of a trusted context at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include SECADM authority.

Syntax


>>-ALTER TRUSTED CONTEXT--context-name-------------------------->

   .-------------------------------------------------------------------------------------------.   
   |                .----------------------------------------------------------------------.   |   
   V    (1)         V                                                                      |   |   
>----+-------ALTER----+-SYSTEM AUTHID--authorization-name--------------------------------+-+-+-+-><
     |                |                    .-,--------------------------------------.    |   |     
     |                |            (2)     V                                        |    |   |     
     |                +-ATTRIBUTES------(----+-| address-clause |-----------------+-+--)-+   |     
     |                |                      |  (3)                               |      |   |     
     |                |                      '-------ENCRYPTION--encryption-value-'      |   |     
     |                +-+-NO DEFAULT ROLE---------+--------------------------------------+   |     
     |                | '-DEFAULT ROLE--role-name-'                                      |   |     
     |                '-+-DISABLE-+------------------------------------------------------'   |     
     |                  '-ENABLE--'                                                          |     
     |                        .-,------------------.                                         |     
     |                (2)     V                    |                                         |     
     +-ADD ATTRIBUTES------(----| address-clause |-+--)--------------------------------------+     
     |                         .-,----------------------.                                    |     
     |                 (2)     V                        |                                    |     
     +-DROP ATTRIBUTES------(----ADDRESS--address-value-+--)---------------------------------+     
     '-| user-clause |-----------------------------------------------------------------------'     

address-clause

|--ADDRESS--address-value--------------------------------------->

>--+-----------------------------------+------------------------|
   '-WITH ENCRYPTION--encryption-value-'   

user-clause

                  .-,-----------------------------------------------------------------------.         
                  V                                              .-WITHOUT AUTHENTICATION-. |         
|--+-ADD USE FOR----+-authorization-name--+-----------------+-+--+------------------------+-+-----+--|
   |                |                     '-ROLE--role-name-' |  '-WITH AUTHENTICATION----'       |   
   |                '-PUBLIC----------------------------------'                                   |   
   |                  .-,-----------------------------------------------------------------------. |   
   |                  V                                              .-WITHOUT AUTHENTICATION-. | |   
   +-REPLACE USE FOR----+-authorization-name--+-----------------+-+--+------------------------+-+-+   
   |                    |                     '-ROLE--role-name-' |  '-WITH AUTHENTICATION----'   |   
   |                    '-PUBLIC----------------------------------'                               |   
   |               .-,----------------------.                                                     |   
   |               V                        |                                                     |   
   '-DROP USE FOR----+-authorization-name-+-+-----------------------------------------------------'   
                     '-PUBLIC-------------'

Notes:

Each of the ATTRIBUTES, DEFAULT ROLE, ENABLE, and WITH USE clauses can be specified at most once (SQLSTATE 42614).
Each attribute name and corresponding value must be unique (SQLSTATE 4274D).
ENCRYPTION cannot be specified more than once (SQLSTATE 42614); however, WITH ENCRYPTION can be specified for each ADDRESS that is specified.

Description

context-name

Identifies the trusted context that is to be altered. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The context-name must identify a trusted context that exists at the current server (SQLSTATE 42704).

ALTER

Alters the options and attributes of a trusted context.

SYSTEM AUTHID authorization-name

Specifies that the context is a connection established by system authorization ID authorization-name, which must not be associated with an existing trusted context (SQLSTATE 428GL). It cannot be the authorization ID of the statement (SQLSTATE 42502).

ATTRIBUTES (...)

Specifies a list of one or more connection trust attributes, upon which the trusted context is defined, that are to be modified. Existing values for the specified attributes are replaced with the new values. If an attribute is not currently part of the trusted context definition, an error is returned (SQLSTATE 4274C). Attributes that are not specified retain their previous values.

ADDRESS address-value

Specifies the actual communication address used by the client to communicate with the database server. The only protocol supported is TCP/IP. Previous ADDRESS values for the specified trusted context are removed. The ADDRESS attribute can be specified multiple times, but each address-value pair must be unique for the set of attributes (SQLSTATE 4274D).

When establishing a trusted connection, if multiple values are defined for the ADDRESS attribute of a trusted context, a candidate connection is considered to match this attribute if the address used by the connection matches any of the defined values for the ADDRESS attribute of the trusted context.

address-value

Specifies a string constant that contains the value to be associated with the ADDRESS trust attribute. The address-value must be an IPv4 address, an IPv6 address, or a secure domain name.

An IPv4 address must not contain leading spaces and is represented as a dotted decimal address. An example of an IPv4 address is 9.112.46.111. The value 'localhost' or its equivalent representation '127.0.0.1' will not result in a match; the real IPv4 address of the host must be specified instead.
An IPv6 address must not contain leading spaces and is represented as a colon hexadecimal address. An example of an IPv6 address is 2001:0DB8:0000:0000:0008:0800:200C:417A. IPv4-mapped IPv6 addresses (for example, ::ffff:192.0.2.128) will not result in a match. Similarly, 'localhost' or its IPv6 short representation '::1' will not result in a match.
A domain name is converted to an IP address by the domain name server where a resulting IPv4 or IPv6 address is determined. An example of a domain name is corona.torolab.ibm.com. When a domain name is converted to an IP address, the result of this conversion could be a set of one or more IP addresses. In this case, an incoming connection is said to match the ADDRESS attribute of a trusted context object if the IP address from which the connection originates matches any of the IP addresses to which the domain name was converted. When creating a trusted context object, it is advantageous to provide domain name values for the ADDRESS attribute instead of static IP addresses, particularly in Dynamic Host Configuration Protocol (DHCP) environments. With DHCP, a device can have a different IP address each time it connects to the network. So, if a static IP address is provided for the ADDRESS attribute of a trusted context object, some device might acquire a trusted connection unintentionally. Providing domain names for the ADDRESS attribute of a trusted context object avoids this problem in DHCP environments.

WITH ENCRYPTION encryption-value

Specifies the minimum level of encryption of the data stream or network encryption for this specific address-value. This encryption-value overrides the global ENCRYPTION attribute setting for this specific address-value.

encryption-value

Specifies a string constant that contains the value to be associated with the ENCRYPTION trust attribute for this specific address-value. The encryption-value must be one of the following values (SQLSTATE 42615):

NONE, no specific level of encryption is required
LOW, a minimum of light encryption is required; the authentication type on the database manager must be DATA_ENCRYPT if an incoming connection is to match the encryption setting for this specific address
HIGH, Secure Sockets Layer (SSL) encryption must be used for data communication between the database client and the database server if an incoming connection is to match the encryption setting for this specific address

ENCRYPTION encryption-value

Specifies the minimum level of encryption of the data stream or network encryption. The default is NONE.

encryption-value

NONE, no specific level of encryption is required for an incoming connection to match the ENCRYPTION attribute of this trusted context object
LOW, a minimum of light encryption is required; the authentication type on the database manager must be DATA_ENCRYPT if an incoming connection is to match the ENCRYPTION attribute of this trusted context object
HIGH, Secure Sockets Layer (SSL) encryption must be used for data communication between the database client and the database server if an incoming connection is to match the ENCRYPTION attribute of this trusted context object

For details about the ENCRYPTION trust attribute, see "CREATE TRUSTED CONTEXT".

NO DEFAULT ROLE or DEFAULT ROLE role-name

Specifies whether or not a default role is associated with a trusted connection that is based on this trusted context. If a trusted connection for this context is active, the change comes into effect on the next switch user request or a new connection request.

NO DEFAULT ROLE: Specifies that the trusted context does not have a default role.
DEFAULT ROLE role-name: Specifies that role-name is the default role for the trusted context. The role-name must identify a role that exists at the current server (SQLSTATE 42704). This role is used with the user in a trusted connection, based on this trusted context, when the user does not have a user-specific role defined as part of the definition of the trusted context.

ENABLE or DISABLE

Specifies whether the trusted context is enabled or disabled.

ENABLE: Specifies that the trusted context is enabled.
DISABLE: Specifies that the trusted context is disabled. A trusted context that is disabled is not considered when a trusted connection is established.

ADD ATTRIBUTES

Specifies a list of one or more additional trust attributes on which the trusted context is defined.

ADDRESS address-value

Specifies the actual communication address used by the client to communicate with the database server. The only protocol supported is TCP/IP. The ADDRESS attribute can be specified multiple times, but each address-value pair must be unique for the set of attributes (SQLSTATE 4274D).

address-value

Specifies a string constant that contains the value to be associated with the ADDRESS trust attribute. The address-value must be an IPv4 address, an IPv6 address, or a secure domain name.

An IPv4 address must not contain leading spaces and is represented as a dotted decimal address. An example of an IPv4 address is 9.112.46.111. The value 'localhost' or its equivalent representation '127.0.0.1' will not result in a match; the real IPv4 address of the host must be specified instead.
An IPv6 address must not contain leading spaces and is represented as a colon hexadecimal address. An example of an IPv6 address is 2001:0DB8:0000:0000:0008:0800:200C:417A. IPv4-mapped IPv6 addresses (for example, ::ffff:192.0.2.128) will not result in a match. Similarly, 'localhost' or its IPv6 short representation '::1' will not result in a match.
A domain name is converted to an IP address by the domain name server, where a resulting IPv4 or IPv6 address is determined. An example of a domain name is corona.torolab.ibm.com.

WITH ENCRYPTION encryption-value

encryption-value

NONE, no specific level of encryption is required
LOW, a minimum of light encryption is required; the authentication type on the database manager must be DATA_ENCRYPT if an incoming connection is to match the encryption setting for this specific address
HIGH, Secure Sockets Layer (SSL) encryption must be used for data communication between the database client and the database server if an incoming connection is to match the ENCRYPTION attribute of this trusted context object

DROP ATTRIBUTES

Specifies that one or more attributes are to be dropped from the definition of the trusted context. If the attribute and attribute value pair is not currently part of the trusted context definition, an error is returned (SQLSTATE 4274C).

ADDRESS address-value: Specifies that the identified communication address is to be removed from the definition of the trusted context. The address-value specifies a string constant that contains the value of an existing ADDRESS trust attribute.

ADD USE FOR

Specifies additional users who can use a trusted connection based on this trusted context. If the definition of a trusted context allows access by PUBLIC and a list of users, the specifications for a user override the specifications for PUBLIC.

authorization-name

Specifies that the trusted connection can be used by the specified authorization-name. The authorization-name must not identify an authorization ID that is already defined to use the trusted context, and must not be specified more than once in the ADD USE FOR clause (SQLSTATE 428GM). It must also not be the authorization ID of the statement (SQLSTATE 42502).

ROLE role-name: Specifies that role-name is the role to be used for the user. The role-name must identify a role that exists at the current server (SQLSTATE 42704). The role explicitly specified for the user overrides any default role associated with the trusted context.

PUBLIC

Specifies that a trusted connection that is based on this trusted context can be used by any user. PUBLIC must not already be defined to use the trusted context, and PUBLIC must not be specified more than once in the ADD USE FOR clause (SQLSTATE 428GM).

WITHOUT AUTHENTICATION or WITH AUTHENTICATION

Specifies whether or not switching the current user on a trusted connection based on this trusted context requires authentication.

WITHOUT AUTHENTICATION: Specifies that switching the current user on a trusted connection based on this trusted context to this user does not require authentication.
WITH AUTHENTICATION: Specifies that switching the current user on a trusted connection based on this trusted context to this user requires authentication.

REPLACE USE FOR

Specifies that the way in which a particular user or PUBLIC uses the trusted context is to change.

authorization-name

Specifies the authorization-name of the user whose use of the trusted connection is to change. The trusted context must already be defined to allow use by the authorization-name (SQLSTATE 428GN), and authorization-name must not be specified more than once in the REPLACE USE FOR clause (SQLSTATE 428GM). It must also not be the authorization ID of the statement (SQLSTATE 42502).

ROLE role-name: Specifies that role-name is the role for the user. The role-name must identify a role that exists at the current server (SQLSTATE 42704). The role explicitly specified for the user overrides any default role associated with the trusted context.

PUBLIC

Specifies that the attributes for use of the trusted connection by PUBLIC are to change. The trusted context must already be defined to allow use by PUBLIC (SQLSTATE 428GN), and PUBLIC must not be specified more than once in the REPLACE USE FOR clause (SQLSTATE 428GM).

WITHOUT AUTHENTICATION or WITH AUTHENTICATION

Specifies whether or not switching the current user on a trusted connection based on this trusted context requires authentication.

WITHOUT AUTHENTICATION: Specifies that switching the current user on a trusted connection based on this trusted context to this user does not require authentication.
WITH AUTHENTICATION: Specifies that switching the current user on a trusted connection based on this trusted context to this user requires authentication.

DROP USE FOR

Specifies who can no longer use the trusted context. The users who are removed from the definition of the trusted context are those users who are currently allowed to use the trusted context. If one or more, but not all, users can be removed from the definition of the trusted context, the specified users are removed and a warning is returned (SQLSTATE 01682). If none of the specified users can be removed from the definition of the trusted context, an error is returned (SQLSTATE 428GN).

authorization-name: Removes the ability of the specified authorization ID to use this trusted context.
PUBLIC: Removes the ability of all users (except the system authorization ID and individual authorization IDs that have been explicitly enabled) to use this trusted context.

Rules

A trusted context-exclusive SQL statement must be followed by a COMMIT or a ROLLBACK statement (SQLSTATE 5U021). Trusted context-exclusive SQL statements are:
- CREATE TRUSTED CONTEXT, ALTER TRUSTED CONTEXT, or DROP (TRUSTED CONTEXT)
A trusted context-exclusive SQL statement cannot be issued within a global transaction; for example, an XA transaction or a global transaction that is initiated as part of two-phase commit for federated transactions (SQLSTATE 51041).

Notes

When providing an IP address as part of a trusted context definition, the address must be in the format that is in effect for the network. For example, providing an address in an IPv6 format when the network is IPv4 will not result in a match. In a mixed environment, it is advantageous to specify both the IPv4 and the IPv6 representations of the address, or better yet, to specify a secure domain name (for example, corona.torolab.ibm.com), which hides the address format details.
Only one uncommitted trusted context-exclusive SQL statement is allowed at a time across all database partitions. If an uncommitted trusted context-exclusive SQL statement is executing, subsequent trusted context-exclusive SQL statements will wait until the current trusted context-exclusive SQL statement commits or rolls back.
Changes are written to the system catalog but do not take effect until they are committed, even for the connection that issues the statement.
Order of operations: The order of operations within an ALTER TRUSTED CONTEXT statement is:
- DROP
- ALTER
- ADD ATTRIBUTES
- ADD USE FOR
- REPLACE USE FOR
Effect of changes on existing trusted connections: If trusted connections exist for the trusted context being altered, the connections remain trusted with the definition in effect before the ALTER TRUSTED CONTEXT statement until the next switch user request or the connection terminates. If the trusted context is disabled while trusted connections for this context are active, the connections remain trusted until the next switch user request or the connection terminates. If trust attributes are changed with the ALTER TRUSTED CONTEXT statement, trusted connections that exist at the time of the ALTER TRUSTED CONTEXT statement that use the trusted context are allowed to continue.
Role privileges: If there is no role associated with the user or the trusted context, only the privileges associated with the user are applicable. This is the same as not being in a trusted context.

Examples

Example 1: Assume that trusted context APPSERVER exists and that it is enabled. Issue an ALTER TRUSTED CONTEXT statement to allow Bill to use the trusted context APPSERVER, but put the trusted context in the disabled state.
```
   ALTER TRUSTED CONTEXT APPSERVER
     DISABLE
     ADD USE FOR BILL
```
Example 2: Assume that trusted context SECUREROLE exists. Issue an ALTER TRUSTED CONTEXT statement to modify the existing user Joe to use the trusted context with authentication and to add everyone else to use the trusted context without authentication.
```
   ALTER TRUSTED CONTEXT SECUREROLE
     REPLACE USE FOR JOE WITH AUTHENTICATION
     ADD USE FOR PUBLIC WITHOUT AUTHENTICATION
```
Example 3: Assume that trusted context SECUREROLEENCRYPT exists with ADDRESS attribute values '9.13.55.100' and '9.12.30.112', and ENCRYPTION attribute value 'NONE'. Issue an ALTER statement to modify the ADDRESS attribute values and the encryption attribute to 'LOW'.
```
   ALTER TRUSTED CONTEXT SECUREROLEENCRYPT
     ALTER ATTRIBUTES (ADDRESS '9.12.155.200',
       ENCRYPTION 'LOW')
```