15.00 - Query Bands and Trusted Sessions - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Query Bands and Trusted Sessions

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 SQL Data Control Language 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 Security Administration 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 Teradata Database 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 Teradata Database 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 Teradata Database directly, but instead use an established database session, typically derived from a session connection pool (see “Query Bands, Trusted Sessions, and Connection Pooling” on page 860 for a definition of 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 (see “GRANT CONNECT THROUGH” in SQL Data Control Language for details), and both privilege checking and query logging are done using the name of the proxy user.

    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 Teradata Database.

    Teradata Database 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 Teradata Database

    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 Teradata Database.

    Teradata Database 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.