Conditional SQL based on user.

[dirklieske]

I would like to do the following in SQL: (My goal is to be as efficient as possible in a View not a macro or procedure)

IF (Userid = 'BOB')
SELECT *
FROM table A
WHERE Condition = 1
IF (UserID = 'BILL')
SELECT *
FROM table A
WHERE condition = 2


I know I could write the above as:
SELECT *
FROM table A
WHERE (USERID = 'BILL' AND condition = 1)
OR (USERID = 'BOB' AND condition = 2)

I could also write the above as:

SELECT *
FROM table A
WHERE USERID = 'BILL AND condition = 1
UNION
SELECT *
FROM table A
WHERE USERID = 'BOB' and condition = 2;

I could also write the above as:
SELECT *
FROM table A
WHERE condition = CASE (WHEN USERID = 'BILL'
THEN 1
ELSE 2);

Again my goal is to first evaluate the USERID before firing any other SQL. I do not want the optimizer to think about anything other then the USERID. (A final approach could be to change all views depending on the user that logs on)

BILL LOGS on the following fires:
replace view EXAMPLE AS
SELECT *
FROM TABLE A
WHERE CONDITION = 1;

BOB LOGS on the following fires:
replace view EXAMPLE AS
SELECT *
FROM TABLE A
WHERE CONDITION = 2;

 

[BillDHS]

You could limit the User to their rows by putting the keyword USER in the WHERE clause:

WHERE UserID = USER

this is the same technique Teradata uses for the "X" dbc table views. This would mean the UserID is in the same domain as the Logon ID. If you wanted to limit the rows further based on other conditions, it depends, is "bill" the only one with condition 1, then only condition 1 rows are returned, if he could have 1 or 2 and you only wanted 1 returned, it would probably need a CASE statement.

 

[jgerstb]

You could do something like

CREATE VIEW myview AS
SELECT stuff
FROM mytable
WHERE (USER = 'bill' AND other conditions)
OR (USER = 'bob' AND other conditions)
OR etc etc for each user

Define the view one time then replace as new users need to be added. This way all users can use the view at the same time and view only the rows they need based on the other conditions

 

[dirklieske]

Sorry,

Please Replace the word USERID to = USER. The intention of my question was not to focus on the USER part, but thanks for pointing/telling me about USER (Which Teradata recognizes) and USERID (Which Teradata does not). What I was really trying to ASK is:

WHICH VERSION OF THE SQL WILL first ask the QUESTION:
"WHO AM I and determine what SQL TO fire"
INSTEAD OF
FIRING ALL SQL AND THEN REALIZING THAT THE USER doesn't match for a part or piece of the SQL.
MY question is one of performance.

HOW CAN I GET THE OPTIMIZER TO :
1st determine the user and only after it knows which user then fire the SQL for that user only. (Never firing or thinking about the other conditions associated to the other users)
( I AM GOING to HAVE alot of users and I do not what the optimzer looking at all the SQL JUST The SQL for a given user. PLEASE NOTE THIS MUST BE A VIEW)

Thanks Dirk

 

[BillDHS]

I think the only way to get TD to do one process before the next is either a subquery or a derived table. But it will often do things in parallel and be doing multiple tasks anyway. How slow is this thing. If you want to increase performance, an index or join index is extremely effective.

 

[jgerstb]

If you are going to replace a view as you stated in your original post aren't you going to run into problems keeping it relevant for concurent users?

BILL LOGS on the following fires:
replace view EXAMPLE AS
SELECT *
FROM TABLE A
WHERE CONDITION = 1;

BOB LOGS on the following fires:
replace view EXAMPLE AS
SELECT *
FROM TABLE A
WHERE CONDITION = 2;

Now if BILL runs a query he is looking at BOBs data because you have changed the definition of the view EXAMPLE for the user who has most recently logged on.

Am I missing something here?

 

[dirklieske]

Right you are jgerstb (Didn't think about the different users re-creating the same view over and over again). I guess that leaves only the 3 choices. Performace as of yet is not bad so I guess I will choose one of the options and hope for the best.

Thanks Dirk Lieske

 

[dnoeth]

"HOW CAN I GET THE OPTIMIZER TO :
1st determine the user and only after it knows which user then fire the SQL for that user only"

The only way i can think of is to create different views in different databases:

create database bill_views;
grant select on original_db to bil_views with grant option;

create view billviews.view1 as ...;
...

grant select on bill_views to bill;

modify user bill as default database bill_views;

When Bill logs on and submits
select * from view1;
without specifying the database, he's using billviews.view1.

In worst case there'll be a database per user, but maybe you have groups of users sharing the same SQL.

Beware, this may be a nightmare to maintain...



It looks like you want to implement fine-grained access control. This is usually done by creating table(s), which map users to rights.
Your queries will look similar to dbc x-views.

Dieter