The CREATE TRUSTED CONTEXT statement defines 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
>>-CREATE TRUSTED CONTEXT--context-name------------------------->
>--BASED UPON CONNECTION USING---------------------------------->
>--SYSTEM AUTHID--authorization-name--●--ATTRIBUTES------------->
.-,-----------------------------------------------------------------------.
V (1) |
>--(----------+-ADDRESS--address-value--+-----------------------------------+-+-+--)--●-->
| '-WITH ENCRYPTION--encryption-value-' |
| (2) |
'-------ENCRYPTION--encryption-value----------------------------'
.-NO DEFAULT ROLE---------. .-DISABLE-.
>--+-------------------------+--●--+---------+--●--------------->
'-DEFAULT ROLE--role-name-' '-ENABLE--'
>--+-------------------------------------------------------------------------------------------+-->
| .-,-----------------------------------------------------------------------. |
| V .-WITHOUT AUTHENTICATION-. | |
'-WITH USE FOR----+-authorization-name--+-----------------+-+--+------------------------+-+-'
| '-ROLE--role-name-' | '-WITH AUTHENTICATION----'
'-PUBLIC----------------------------------'
>--●-----------------------------------------------------------><
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.
- 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
- 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 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
contextsEncryption 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')