Using Teradata Query Banding to handle Security Views

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

Using Teradata Query Banding to handle Security Views

One of the biggest benefits of Query Banding is the ability to pass parameters into an SQL script.

Establishing an associative security table lookup between a userid and allowable company codes may allow easy access to the base data tables.  

One of my clients recently had the need to allow specific individuals access to specific retailer’s sales info.   Rather that creating multiple views with separate where statements for each retailer they wanted to create a security table.  Their BI tool would only logon to Teradata through one user id.  All the BI users access Teradata through this one id.

 For instance; if you want to limit user access by Retailer, a table could be built like this:

SECURITY_TBL

USER_ID

RETAILER_ID

i303444

WM

i303444

MJ

i303555

KM

i303777

CV

 Some third-part Business Intelligence (BI) tools login to Teradata with a single id whereas we need a separate id for each user.

We can have the BI tool issue the query banding instruction to set the user id to the suggested field name, ClientUser.

The Query band may be set at the FOR TRANSACTION or FOR SESSION level.

Use GetQueryBandValue(1,'ClientUser'); as tthe UDF for obtaining the Transaction level value or

Use GetQueryBandValue(2,'ClientUser'); for obtaining the Session level value.

To test query banding I wrote the following BTEQ code.

.logon localtd/SysAdm,xxxxx
select date, time;
SET QUERY_BAND = 'ClientUser=i303444;' FOR SESSION ;
SEL GetQueryBandValue(2,'ClientUser');
SET QUERY_BAND = 'ClientUser=i303555;' FOR SESSION ;
SEL GetQueryBandValue(2,'ClientUser');
select time;
.logoff
.quit
.exit

We can have a Sales_Daily_View:

Locking row for access
Select flda, fldb, fldc from
Sales.Sales_Daily, Security.Security_Tbl
where
Sales_Daily.Retailer_ID = Security_Tbl.Retailer_ID
and Security_Tbl.User_ID = GetQueryBandValue(2,'ClientUser');

The Sales_Daily base table contains the proper two character Retailer_ID in each row.

The view will select the appropriate data rows from the Sales_Daily table based on the ClientUser ID that was passed to the system using Query banding.

4 REPLIES
Senior Apprentice

Re: Using Teradata Query Banding to handle Security Views

Hi Phil,
there's only one drawback: getQueryBandValue is evaluated once per row, thus increasing CPU usage.
It's currently defined as NOT DETERMINISTIC, but can be changed to DETERMINISTIC without risk in TD13.
Dipdem.bteq will be fixed in a coming patch level.

i wrote a blog about global and session parameters:
http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql

Dieter
Teradata Employee

Re: Using Teradata Query Banding to handle Security Views

Great point Dieter; thanks for making everyone aware of it until the patch is created.
N/A

Re: Using Teradata Query Banding to handle Security Views

If I use SET QUERY_BAND = 'ClientUser=i303444;' FOR SESSION ;
and if the user logs in using SQL Assistant then he or she can change the access by using the same command set query_band which would defeat the purpose. I would like to Can DBA set the QUERY_BAND in such way that user cannot make any change to QUERY_BAND properties.
Enthusiast

Re: Using Teradata Query Banding to handle Security Views

Hi Dieter,

As you mentioned the drawback: "getQueryBandValue is evaluated once per row, thus increasing CPU usage", later versions of TD (13 & 14) overome this drawback? if yes, can you please explain how it is achieved?

And, can you please explain more about your second statement:

"It's currently defined as NOT DETERMINISTIC, but can be changed to DETERMINISTIC without risk in TD13.

Dipdem.bteq will be fixed in a coming patch level."

Thanks in advance,

Sri.