proc sql create view with dynamic sas.userid macro var

[metalray]

@Tom,
good idea but with your SQL you are merging all the tables or
even creating some kind of cross/full join, something I cant do with
a million rows.

@DF,
the idea with the OS User seems to work fine when opening the
View in EG but when using the view in an information map the
data returned is always nothing/empty. Please explain me,
why does OS_User work if the sas user does not exist on the
oracle database as a user?

[DF]

Oracle knows the OS login for the connecting user, even though you use different credentials to log in.

I'm not sure about the informaion map issue - I've only tested the view using EG under a few different accounts, having created it in a shared library.

[metalray]

Hi DF,

the connecting user is always the same as specified in

proc sql ;

connect to oracle as oradb (user='....' password='.....'

path='.....');

How can it be that Oracle knows the SAS executing user? - because

the executing user (sysuserid) is the one that will have to go in the WHERE clause

to only get the records for that specific user)

[DF · Accepted solution]

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?

[metalray]

Hi DF,

works great. thanks a lot.

[DF]

I'm glad .  Credit ought to go to @Tom though - it was his idea to use the Oracle functions!