15.10 - Example: Preventing An Injected SET QUERY_BAND From Changing the Proxy User - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

You can use the standard application programming interfaces such as CLIv2, JDBC, or .NET to enable an application to designate an SQL request as being either trusted or not trusted.

If the application submits SQL requests created or modified by a client user, you might want to require that all SET QUERY_BAND requests that set a proxy user be submitted in trusted requests.

To do this, you grant the CONNECT THROUGH WITH TRUST_ONLY privilege to the trusted user. See SQL Data Control Language, B035-1149. With this privilege, Teradata Database requires that any SET QUERY_BAND requests submitted by a trusted user that either set or remove a proxy user be designated as a trusted request; otherwise, it rejects the request and returns an error to the requestor.

By means of the API it is using, the application can mark requests from user input as being not trusted to prevent the client from injecting a SET QUERY_BAND request that changes the proxy user or proxy role.

To use this feature in JDBC, you must create a JDBC DataSource with the TRUSTED_SQL=ON connection parameter. All requests made on the DataSource are then trusted unless they are downgraded using the {fn teradata_untrusted} escape function.

For requests containing user-input SQL requests, the application can prepend the {fn teradata_untrusted} escape function to the SQL call before the request is passed to the Teradata JDBC Driver. This escape function sets the request to the not trusted state. For example,

     untrustedSQL = "{fn teradata_untrusted}"   untrustedSQL ;