17.10 - Query Bands and Trusted Sessions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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 currently logged on user. See Teradata Vantage™ - SQL Data Control Language, B035-1149 for the syntax and rules for using GRANT CONNECT THROUGH requests.

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. See Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100 for an overview of the security issues presented by trusted sessions.

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 referred to as a proxy user.

While it is possible to combine query bands and roles to obtain 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 just one request, while you would otherwise need to submit two individual SET QUERY_BAND and SET ROLE requests to achieve the same result.
  • ProxyUser is a separate column in the query log, while you would have to extract it from a query band.

    Trusted sessions push the knowledge of what role can be set for an end user into the database, which is very advantageous for application development.

Proxy users do not log onto 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 in Teradata Vantage™ - SQL Data Control Language, B035-1149.

The following table describes the options for using trusted sessions.

IF a proxy user is … THEN …
a permanent database user Privileges, roles, or both can be granted to each of the permanent users.

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 onto 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.

an application user who is not known 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 onto 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.