CREATE TRUSTED CONTEXT statement

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

address-attributes

Notes:

• ¹ Each combination of an attribute name and its corresponding value, as a pair, 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

Names the trusted context. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The name must not identify a trusted context that already exists at the current server (SQLSTATE 42710). The name must not begin with the characters 'SYS' (SQLSTATE 42939).

BASED UPON CONNECTION USING 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.

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. Once any ADDRESS attributes are specified, a candidate connection must match at least one specified address-value.

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

The following table summarizes when a trusted context can be used, depending on the encryption used by the existing connection. If the trusted context cannot be used for the connection, a warning is returned (SQLSTATE 01679) and the SQLWARN8 field of the SQLCA is set to 'Y', indicating that the connection is a regular (non-trusted) connection.

Table 1. Encryption and trusted contexts

Encryption used by existing connection	ENCRYPTION value for trusted context	Can the trusted context be used for the connection?
No encryption	'NONE'	Yes
No encryption	'LOW'	No
No encryption	'HIGH'	No
Low encryption (DATA_ENCRYPT)	'NONE'	Yes
Low encryption (DATA_ENCRYPT)	'LOW'	Yes
Low encryption (DATA_ENCRYPT)	'HIGH'	No
High encryption (SSL)	'NONE'	Yes
High encryption (SSL)	'LOW'	Yes
High encryption (SSL)	'HIGH'	Yes

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. The default is NO DEFAULT ROLE.

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.

DISABLE or ENABLE

Specifies whether the trusted context is created in the enabled or disabled state. The default is DISABLE.

DISABLE

Specifies that the trusted context is created in the disabled state. A trusted context that is disabled is not considered when a trusted connection is established.

ENABLE

Specifies that the trusted context is created in the enabled state.

WITH USE FOR

Specifies who can use a trusted connection that is based on this trusted context.

authorization-name

Specifies that the trusted connection can be used by the specified authorization-name. The authorization-name must not be specified more than once in the WITH USE FOR clause (SQLSTATE 428GM). It must also not be the authorization ID of the statement (SQLSTATE 42502). If the definition of a trusted context allows access by both PUBLIC and a list of users, the specifications for a user override the specifications for PUBLIC. For example, assume that a trusted context is defined that allows access by both PUBLIC WITH AUTHENTICATION and JOE WITHOUT AUTHENTICATION. If the trusted context is used by JOE, authentication is not required. However, if the trusted context is used by GEORGE, authentication is required.

ROLE role-name

Specifies that role-name is the role to be used for the user when a trusted connection is using the trusted context. 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 be specified more than once (SQLSTATE 428GM). All users using such a trusted connection make use of the privileges associated with the default role for the associated trusted context. If a default role is not defined for the trusted context, there is no role associated with the users that use a trusted connection based on this trusted context.

WITHOUT AUTHENTICATION or WITH AUTHENTICATION

Specifies whether or not switching the user on a trusted connection requires authentication of the user. The default is WITHOUT AUTHENTICATION.

WITHOUT AUTHENTICATION

Specifies that switching the current user on a trusted connection to this user does not require authentication.

WITH AUTHENTICATION

Specifies that switching the current user on a trusted connection to this user requires authentication.

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.
• Specifying a role in the definition of a trusted context: The definition of a trusted context can designate a role for a specific authorization ID, and a default role to be used for authorization IDs for which a specific role has not been specified in the definition of the trusted context. This role can be used with a trusted connection based on the trusted context, but it does not make the role available outside of a trusted connection based on the trusted context.
• When issuing a data manipulation language (DML) SQL statement using a trusted connection, the privileges held by a context-assigned role in effect for the authorization ID within the definition of the associated trusted context are considered in addition to other privileges directly held by the authorization ID of the statement, or indirectly by other roles held by the authorization ID of the statement.
• The privileges held by a context-assigned role in effect for the authorization ID within the definition of the associated trusted context are not considered for data definition language (DDL) SQL statements. For example, to create an object, the authorization ID of the statement must be able to do so without including the privileges held by the context-assigned role.
• When installing a new application that authenticates to the database server using the same credentials as an existing application on the same machine, and which takes advantage of a trusted context, the new application might also take advantage of the same trusted context object (inheriting the trusted context role, for example). This might not be the security administrator's intention. The security administrator might want to turn on the database audit facility to find out what applications are taking advantage of trusted context objects.
• 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.

Examples

• Example 1: Create a trusted context such that the current user on a trusted connection based on this trusted context can be switched to two different user IDs. When the current user of the connection is switched to user ID JOE, authentication is not required. However, authentication is required when the current user of the connection is switched to user ID BOB. Note that the trusted context has a default role called context-role. This implies that users working within the confines of this trusted context inherit the privileges associated with role context-role.

     CREATE TRUSTED CONTEXT APPSERVER
       BASED UPON CONNECTION USING SYSTEM AUTHID WRJAIBI
       DEFAULT ROLE CONTEXT_ROLE
       ENABLE
       ATTRIBUTES (ADDRESS '9.26.113.204')
       WITH USE FOR JOE WITHOUT AUTHENTICATION
         BOB WITH AUTHENTICATION

• Example 2: Create a trusted context such that the current user of a trusted connection based on this trusted context can be switched to any user ID without authentication.

     CREATE TRUSTED CONTEXT SECUREROLE
       BASED UPON CONNECTION USING SYSTEM AUTHID PBIRD
       ENABLE
       ATTRIBUTES (ADDRESS '9.26.113.204')
       WITH USE FOR PUBLIC WITHOUT AUTHENTICATION

• Example 3: Create a trusted context such that the current user of a trusted connection based on this trusted context can be switched to any user ID without authentication. The difference between this trusted context and the trusted context created in example 2, is that this trusted context has an additional attribute called ENCRYPTION. The ENCRYPTION attribute setting for trusted context SECUREROLEENCRYPT states that the encryption setting used by a connection must be at least "low encryption" (see Table 1) to match this trusted context attribute.

     CREATE TRUSTED CONTEXT SECUREROLEENCRYPT
       BASED UPON CONNECTION USING SYSTEM AUTHID SHARPER
       ENABLE
       ATTRIBUTES (ADDRESS '9.26.113.204'
         ENCRYPTION 'LOW')
       WITH USE FOR PUBLIC WITHOUT AUTHENTICATION

• Example 4: Create a trusted context, such that connections made by user WRJAIBI from addresses 9.26.146.201 and 9.26.146.203 are trusted when no encryption is used, but a connection made by user WRJAIBI from address 9.26.146.202 requires a LOW level of encryption to be trusted.

     CREATE TRUSTED CONTEXT WALIDLOCSENSITIVE
       BASED UPON CONNECTION USING SYSTEM AUTHID WRJAIBI
       ENABLE
       ATTRIBUTES (ADDRESS '9.26.146.201',
         ADDRESS '9.26.146.202' WITH ENCRYPTION 'LOW',
         ADDRESS '9.26.146.203'
         ENCRYPTION 'NONE')