Example: Preventing an Injected SET QUERY_BAND from Changing the Proxy User - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 Teradata Vantage™ - SQL Data Control Language, B035-1149. With this privilege, Vantage 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 ;