Teradata Query Bands and Trusted Sessions

When a middle-tier application is used together with the Teradata database, it typically logs on to the database as a permanent database user (application user) and establishes a connection pool. End-users that access the database through the middle-tier application are given all authorized database privileges and are audited based on that single application user.

For the sites that require users to be individually identified, authorized, and audited, the middle-tier application can be configured to offer trusted sessions. Application users that access the database through a trusted session must be set up as proxy users and assigned one or more database roles, which determine their access rights in the database. When a proxy user requests database access, the application forwards the user identity and applicable role information to the database.

For more information about Teradata trusted sessions, refer to https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Security-Administration/Introduction-to-Security-Administration.

The system uses a proxy user if the query band contains the reserved name PROXYUSER. In order for the proxy user to access sensitive data, UDFs and UDTs need to know the requestor of the data. They obtain this information from the query band parameters.

For more information about query bands, refer to https://docs.teradata.com/r/Teradata-VantageCloud-Lake/SQL-Reference/SQL-Data-Definition-Language

If a proxy user is found among the query band parameters, then it is used in the authorization process instead of the regular data user (which could be a different user). This means that only the proxy user’s permissions apply. This is similar to how the Teradata permissions work for trusted sessions. The database permissions for the proxy user are used, and not the application user’s permissions.

Before such a user can access the database, a Grant Connect through Access right should be given by the database administrator to the user. The following example provides the query to ensure that the user ‘JSMITH’ can connect through.

The application My_App is confiured to connect to Teradata with a service account My_App_User that is not part of the Protegrity security policy. However, in case the app user JSMITH which does not exist in Teradata needs to see the data in the clear. Then, the database administrator must first Grant Connect access to the user, JSMITH.

GRANT CONNECT THROUGH My_App_User
TO JSMITH
WITH ROLE AppRole;

The user JSMITH can now access the database. However, since JSMITH does not exist in the database, Teradata needs to know what role it needs to inherit. This can be any role already configured within Teradata.

Then, every time JSMITH wants to run a SQL command through My_App, the following query band statement needs to be executed first: SET QUERY_BAND=‘PROXYUSER=JSMITH;’ FOR SESSION;

The UDF getqueryband is provided by Teradata.

select getqueryband();
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
getqueryband()
---------------------------------------------------------------------------

select pty_varcharlatinenc('abcd','AES',123,0,0);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
pty_varcharlatinenc('abcd','AES',123,0,0)
---------------------------------------------------------------------------
E3AE49B5C44E4CE64CC7AB3A20F82325

SET QUERY_BAND='PROXYUSER=JSMITH;' FOR SESSION;
*** Set QUERY_BAND accepted.
*** Total elapsed time was 1 second.

select getqueryband();
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
getqueryband()
---------------------------------------------------------------------------
=S> PROXYUSER=JSMITH;

select pty_varcharlatinenc('abcd','AES',123,0,0);
*** Failure 7504 in UDF/XSP/UDM SYSLIB.pty_varcharlatinenc: SQLSTATE U0001:
No such user
Statement# 1, Info =0
*** Total elapsed time was 1 second.
AUDIT TRACE:
Thu Dec 30 01:21:53.530 2010 JSMITH AES 0 1 0 0 Insert, unknown user dbp 1

SET QUERY_BAND=NONE FOR SESSION;
*** Set QUERY_BAND accepted.
*** Total elapsed time was 1 second.

select pty_varcharlatinenc('abcd','AES',123,0,0);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
pty_varcharlatinenc('abcd','AES',123,0,0)
---------------------------------------------------------------------------
E3AE49B5C44E4CE64CC7AB3A20F82325

Important: The Data Warehouse Protector supports user names that are up to 255 characters in length. However, the Teradata platform supports user name lengths of 128 characters only. Hence, the user name is limited to the value supported by the Teradata platform.