Trusted sessions are designed to work with middle tier applications that submit SQL requests to Vantage on behalf of end users. If a middle tier application permits end users to submit SQL requests directly (this is sometimes referred to as injected SQL), then an end user could submit a SET QUERY_BAND request that switches the active session identity and role set to another proxy user who has discretionary access control privileges than that user is intended to have, thus enabling a potential security breach.
This security issue does not apply to trusted sessions when Vantage enforces row-level security for a table. For more information, see Query Bands, Trusted Sessions, and Row-Level Security.
To prevent end users from changing the active session identity, the DBA can require that all SET QUERY_BAND requests submitted by a trusted user that set or remove a proxy user be a trusted request.
This accomplished using the following steps.
- Grant the CONNECT THROUGH WITH TRUST_ONLY privilege to the trusted user. (see Teradata Vantage™ - SQL Data Control Language, B035-1149 for the syntax and rules for GRANT CONNECT THROUGH requests).
When this privilege is granted to a trusted user, Vantage requires that any SET QUERY_BAND requests submitted by that trusted user that set or remove a proxy user must be designated as a trusted request or it rejects the request.
- Using the available APIs, an application can designate an SQL request to be either trusted or not trusted.
When an application submits a SET QUERY_BAND request to set the ProxyUser, it can designate the request as trusted.
When an application submits SQL requests that are either created or modified by an end user, it can designate the requests as not trusted to prevent the client from injecting a SET QUERY_BAND request to change the proxy user or proxy role.
There is no SQL method for upgrading a request to the trusted designation.
When you either commit or roll back a transaction, Vantage removes both its query band and any proxy user who has been set with a query band for that transaction. This cannot be controlled by the TrustOnly flag. Therefore, you should use session query bands when you want to use trusted sessions.
The following table documents the behavior of SET QUERY_BAND requests associated with the different TrustOnly flag settings.
IF an application specifies that the TrustOnly flag for a request is set to this value … | THEN a SET QUERY_BAND request that sets or updates a proxy user can be performed … |
---|---|
N This is the default. |
as either of the following types of request. not trusted trusted |
Y | only as a trusted request. When you have set a proxy connection with the TrustOnly flag set to Y, then you cannot submit a SET QUERY_BAND request that removes the Proxy User unless it is performed as a trusted request. |
The following table documents how a procedure that submits a SET QUERY_BAND request becomes trusted or not trusted.
A SET QUERY_BAND request in this type of procedure … | IS … |
---|---|
SQL | trusted if the CALL request that invoked it is trusted. not trusted if the CALL request that invoked it is not trusted. |
external | trusted if the Trusted flag in the Options parcel is set to Y. not trusted if the Trusted flag in the Options parcel is set to N. |