ALTER TRUSTED CONTEXT statement

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

Important: The DATA_ENCRYPT authentication type is deprecated and might be removed in a future release. To encrypt data in-transit between clients and Db2® databases, we recommend that you use the Db2 database system support of Transport Layer Security (TLS).

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

Read syntax diagramSkip visual syntax diagramALTER TRUSTED CONTEXTcontext-name 1ALTERSYSTEM AUTHIDauthorization-nameATTRIBUTES2(,address-clause3ENCRYPTIONencryption-value)NO DEFAULT ROLEDEFAULT ROLErole-nameDISABLEENABLEADD ATTRIBUTES2(,address-clause)DROP ATTRIBUTES2(,ADDRESSaddress-value)user-clause
address-clause
Read syntax diagramSkip visual syntax diagramADDRESSaddress-valueWITH ENCRYPTIONencryption-value
user-clause
Read syntax diagramSkip visual syntax diagramADD USE FOR,authorization-nameROLErole-namePUBLICWITHOUT AUTHENTICATIONWITH AUTHENTICATIONREPLACE USE FOR,authorization-nameROLErole-namePUBLICWITHOUT AUTHENTICATIONWITH AUTHENTICATIONDROP USE FOR,authorization-namePUBLIC
Notes:
  • 1 Each of the ATTRIBUTES, DEFAULT ROLE, ENABLE, and WITH USE clauses can be specified at most once (SQLSTATE 42614).
  • 2 Each attribute name and corresponding value must be unique (SQLSTATE 4274D).
  • 3 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, or equivalent, 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
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 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, or equivalent, 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).

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.
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, or equivalent, 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')