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.

Discussion
dnoeth 86 comments Joined 11/04
14 Mar 2011

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

Dieter

pEdelsberg 1 comment Joined 12/10
28 Mar 2011

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

Phil Edelsberg, PMP.
Teradata V2R5 Certified Master
Si hoc legere scis nimium eruditionis habes

Krisav 1 comment Joined 10/12
30 Oct 2012

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.

SriniD 2 comments Joined 02/14
13 Aug 2014

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.

You must sign in to leave a comment.