Hello Guys,
we try to create a proc sql view that fetches data for each user individually,
Unfortunately, if we use %str(%')&sysuserid.%str(%')
the user ID of the creator of this view gets replaced and is fixed.
How would we create a view that is creating a dynamic WHERE clause?
proc sql ;
connect to oracle as oradb (user='sas' password='{xxxxx
path=xxxadas);
create view mysas.ph_filtered_facts as
select *
from connection to oradb
(
SELECT FAKT FROM DEV.FAK
WHERE Wer IN
(SELECT Wer FROM DEV.vk WHERE SK = 1 AND
VK IN
(SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = %str(%')&sysuserid.%str(%') ))
);
run;
disconnect from oradb;
quit;
run;
proc print data=pt_example;
title "Using SQL Pass-Through";
run;
We also tried:
%str(%')symget(_metauser)%str(%') ))
I'm not an Oracle expert, so I can't really answer for certain. I suppose the OS user is just part of the information the Oracle driver passes from your system to the Oracle database - it makes sense that the information would be available as some setups would use this as the authentication method instead of specifying a username/passwords.
So while I log into Oracle as the user "oracle_user", so my oracle username/password gets sent, Oracle also gets told I'm logged into my local operating system as "DF" - and that's what goes into OS_USER. When it's a SAS process that connects to Oracle, then Oracle is told the username that the SAS process is running under.
There's lots of other information available using the same function: http://psoug.org/reference/sys_context.html might be worth having a look at.
Is the pass-through view now working as desired?
You could do this before the proc sql step:
data _null_;
call symput('usr',"'"||"&sysuserid"||"'");
run;
then change saslogin=&usr in proc sql.
Hi FriedEgg,
a good try but the view is still not dynamic. The describe statement still contains the creators user ID, even when executing the describe with other users
data _null_;
call symput('usr',"'"||"&sysuserid"||"'");
run;
proc sql ;
connect to oracle as oradb (user='....' password='.....'
path='.....');
create view mysas.ph_filtered_facts as
select *
from connection to oradb
(
SELECT FAKT FROM DEV.FAKTEN
WHERE Wer IN
(SELECT Wer FROM DEV.vk WHERE SK = 1 AND
VK IN
(SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = &usr ))
);
run;
disconnect from oradb;
quit;
run;
/*also tried: (SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = %str(%')&sysuserid.%str(%') ))*/
/*
proc sql;
describe view mysas.ph_filtered_facts
/*Result*/
select *
from connection to oradb
/* dbms=oracle, connect options=(user='.....' password=XXXXX path='.....') */
( SELECT FAKT FROM DEV.FAKTEN WHERE Wer IN (SELECT Wer FROM DEV.vk WHERE SK = 1 AND
VK IN (SELECT VK FROM DEV.SASLOGINS WHERE SASLOGIN = 'S39' )) );
*/
How are you running the script as different user? Did you check that &sysuserid is showing the expected user appropriatly during your runs?
I run it in EG, changing the connection profile.
I also use the view in an information map,changing connection profiles, still one and the same result.
%put &sysuserid; /* shows user id */
With the describe statement, I check how the view is defined for each user,
besides that, I also know the data, and that must be different for each user
but it is not, always shows S39.
proc sql;
describe view mysas.ph_filtered_facts;
/* still shows the user of the creator of the view,S39 , hence wrong data when I use user SDW */
What authentication mode are you using to spawn the conncetions, does each profile use the same user? &sysuserid is the system user, so it should be equal to the value of the user that performs the login to that session.
Is it necessary to use SQL in pass-thru mode? If you were able to write it directly against the oracle database (i.e. it'd have to be linked using libname) then you could use SYMGET() in your view definition instead.
@FriedEgg,What do you mean?
The "connect to oracle as oradb (user='....' password='.....' path='.....'); is always the same users" is obviously always
the same users, its the schema that contains the oracle tables that I use in the SQL statement.
I am a bit worried that there is no straight answer to that. Has none thought of using SQL PassThrough with the current user ID before?
FriedEgg, I am aware what the sysuserid is and I see that the %put &sysuserid; shows me the correct one.
If I execute as S39 it shows S39, if I execute as SDW is shows SDW but the PROC SQL itself, always shows S39, the creator.
To me, the problem I have looks almost logical considering the logic that the SAS team applies when developing the great software for us.
When I execute the CREATE VIEW and substitute the sysuserid in the WHERE clause then that is nicely done during creation time
but that does not mean its a "dynamic" view.
@DF, good idea. I have an oracle (SAS ACCESS) library. but I am very reluctant to use a DATA step.
creating a SAS data set is not viable. one of the three tables contains a million rows and I
cant do that for every user. - hence the usage of a VIEW.
using a data step, I wonder how the nesting or join would work.
I cant loop through the millions of rows.
/*Nesting: http://www.repole.com/dinosaur/fuzzymerge.html*/
data mysas.PH_TEST_VIEW (keep=VK);
set dev.FAKTEN;
where WER IN ('S39');
run;
1. DEV.SASLOGINS about 1000 rows
user login to vk groups
2. DEV.vk about 1000 rows
vk groups to individual Wer
3. DEV.FAK millions of rows
individual Wer to all their data
Assuming your db is linked in SAS as "Dev", I was thinking something like:
proc sql;
create view mysas.ph_filtered_facts as
select
fakt
from dev.fak
inner join dev.vk on fak.wer = vk.wer and vk.sk=1
inner join dev.saslogins on saslogins.vk = vk.vk
where
saslogins.login = symget('sysuserid');
quit;
Obviously I can't test it, and I might have confused your table linkage - I was trying to remove the subqueries and I think SAS wouldn't deal with these very well!
It looks to me like you are trying to reference a SAS macro variable in the code that is running in the database. I do not think that is possible.
You might want to instead look for an ORACLE (or whatever your database is) function that can return the ORACLE userid. But that might not match the userid values that are stored in your database.
If you want to do this on just the SAS side then you could use the SYMGET function in your view. Try this program in SAS.
proc sql noprint ;
create view test1 as
select * from sashelp.class
where name = symget('name')
;
quit;
%let name=Mary ;
data _null_;
set test1;
put name=;
run;
%let name=Philip;
data _null_;
set test1;
put name=;
run;
That's a good idea. The below works on my Oracle setup:
SELECT SYS_CONTEXT('USERENV','OS_USER') os_user from DUAL
You could just plug that into the original SQL view and not bother with the macro variable, assuming it returns the correct user in your setup.
Hello Guys,
Many thanks for your suggestions. I think I never got so many responses here.
First, yes, the Oracle User ID is very different from the user ID of the
view user. There is and will never be a match (@DF). We
have a thousand users for SAS but not a thousand Oracle USERIDs.
@Tom, you pointed it out rightly, I cant use a SAS macro variable in the code that is
running on the database, hence my describe view statement always showed the
sasuserid of the creator of the view. I was hopeing this can be substituted at
runtime but obviously not.
Now, we try to do a SAS DATA VIEW to allow putting the sysuserid of the individual executer
in the view and create a truly user-dependent-dynamic-view of data.
The data is quite large and I wonder how to efficiently link it using
a SAS DATA VIEW. I cant merge the tables, it will create millions of rows.
Regards,
Bob
Hi Bob,
The Oracle code I posted above returns the local OS username, rather than the oracle login. In my case, even though I've logged into oracle as the user "SAS", it returns "DF" because that's the login I've used on the system sas is running on.
I've tested it here using several logins and even though my code uses the same oracle username/password, the query returns the OS user I'm logged in as.
HTH.
You could see if SAS is smart enough to push the WHERE clause from the SAS side of the query into the database.
create view mysas.ph_filtered_facts as
select *
from connection to oradb
(select A.FAKT,C.SASLOGIN
from DEV.FAKTEN A
, DEV.VK B
, DEV.SASLOGINS C
where A.Wer = B.Wer
and B.vk = C.vk
and B.sk = 1
)
where SASLOGIN = symget('sysuserid')
;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.