Query Bands and Trusted Sessions - Teradata VantageCloud Lake

The following reserved query bands are used by trusted sessions.

Name	Description
ProxyRole	Defines the role to be used within the trusted session. The valid value is the name of a role that has been granted to the proxy user.
ProxyUser	Sets a trusted session to the identity of the proxy user. The valid value is the name of a proxy user that has been granted the CONNECT THROUGH privilege on the logged on user. See GRANT CONNECT THROUGH for details.

Trusted sessions provide you with the ability to authorize middle tier applications to assert user identities and roles for use in checking the privileges for, and logging queries of, individual users without establishing a logon session for each end user of the application.

Trusted sessions identify permanent and application users for privilege checking and query auditing when end users make requests against Vantage through a middle tier application such as a web-based product ordering system. Trusted sessions can be used by any type of middle tier application that authenticates its end users and submits SQL requests to Vantage on their behalf.

A trusted session enables a middle tier application to assume the identity of a different user from the one who is logged on for privilege validation. Such a different user is called a proxy user.

While you can combine query bands and roles to get most of the functionality of trusted sessions, trusted sessions have the following advantages over combining the functionality of simple query bands with roles:

You can set the proxy user and role using one request, instead of submitting individual SET QUERY_BAND and SET ROLE requests.
You can see the ProxyUser column in the query log, instead of extracting the proxy user from a query band.
Trusted sessions push the knowledge of what role can be set for an end user into the database, which is advantageous for application development.

Proxy users do not log on to Vantage directly, but instead use an established database session, typically derived from a session connection pool. For a definition of connection pooling, see Query Bands, Trusted Sessions, and Connection Pooling. Once a proxy user has been switched onto an active session, all subsequent requests that user makes operate using the privileges granted to the proxy user through a trusted user and both privilege checking and query logging are done using the name of the proxy user. See GRANT CONNECT THROUGH.

The following table describes the options for using trusted sessions.

Proxy User	Options for Using Trusted Sessions
Permanent database user	Privileges, roles, or both can be granted to each permanent user. Proxy connect privileges can be granted to each permanent user through a trusted user. The application middleware can set the PROXYUSER name in the query band so the session can be switched to the proxy user. Subsequent requests can then run under the privileges of the proxy user. The permanent user can be used to connect as a proxy user or through a direct log on to Vantage. Vantage assigns the name of the proxy user in the trusted session to the name of the creator of any database objects the permanent user creates.
Application user unknown to Vantage	The security administrator can create a role or set of roles with the privileges needed for the set of application users. The security administrator can grant trusted session privileges for the application users through a trusted user using the specified roles. The application middleware can set the query band so the session can be switched to the proxy user. Subsequent requests can then run under the privileges of the active roles of the proxy user. The application user can be used to connect as a proxy user, but cannot directly log on to Vantage. Vantage assigns the name of the trusted user in the trusted session to the name of the creator of any database objects the application user creates.

Proxy User

Options for Using Trusted Sessions

Permanent database user

Privileges, roles, or both can be granted to each permanent user.

Proxy connect privileges can be granted to each permanent user through a trusted user.

The application middleware can set the PROXYUSER name in the query band so the session can be switched to the proxy user.

Subsequent requests can then run under the privileges of the proxy user.

The permanent user can be used to connect as a proxy user or through a direct log on to Vantage.

Vantage assigns the name of the proxy user in the trusted session to the name of the creator of any database objects the permanent user creates.

Application user unknown to Vantage

The security administrator can create a role or set of roles with the privileges needed for the set of application users.

The security administrator can grant trusted session privileges for the application users through a trusted user using the specified roles.

The application middleware can set the query band so the session can be switched to the proxy user.

Subsequent requests can then run under the privileges of the active roles of the proxy user.

The application user can be used to connect as a proxy user, but cannot directly log on to Vantage.

Vantage assigns the name of the trusted user in the trusted session to the name of the creator of any database objects the application user creates.

High-Level Process for Trusted Sessions

The following event sequence outlines the general process stages undertaken to use a trusted session.

The security administrator creates CONNECT THROUGH privileges for an appropriate trusted_user:permanent | application_user pair using a GRANT CONNECT THROUGH request.
The middle tier application creates a connection pool to Vantage.
The application end user authenticates itself to the middle tier application and requests a service to submit a query to Vantage.
The method by which the application end user authenticates itself to the middle tier application is not described here because its authentication is the responsibility of the application, not of Vantage.
The middle tier application establishes a connection within the connection pool.
The middle tier application sets the active session identity and role for the application end user by submitting an appropriate SET QUERY_BAND request to Vantage.
Vantage verifies the application end user has been granted trusted session access through the middle tier application database connection.
The middle tier application submits an SQL request to Vantage on behalf of the application end user.
Vantage verifies the privileges for the request based on the active roles defined for the application end user.
Vantage returns the result set to the middle tier application, which then forwards the result set to the application end user.

Vantage records the identity of the application end user in any rows inserted into Access Log and Database Query Log tables as appropriate.

Proxy User with Which End User Makes Connection	Name by Which Proxy User Identity Is Logged (Specified for CONNECT THROUGH Privilege Used to Make Trusted Session)
Application	Application name.
Permanent	Permanent user name.

See GRANT CONNECT THROUGH, Trusted Sessions, and User Types for the definitions of application and permanent users.

The middle tier application returns the connection to the connection pool.
The following housekeeping activities occur when either the session is terminated or Vantage gets a Cleanup parcel (number 80).
- The proxy user is discarded.
- Any session query bands are discarded.
- Any transaction query bands are discarded.

Creator Status for Objects Created in a Trusted Session

In a trusted session:

If the proxy user is also a permanent database user, Vantage assigns the name of the permanent user as the creator of any database objects the proxy user creates.
If the proxy user is not also a permanent database user, Vantage assigns the name of the application user as the creator of any database objects the proxy user creates.