BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metalray
Calcite | Level 5

@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
Fluorite | Level 6 DF
Fluorite | Level 6

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
Calcite | Level 5

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
Fluorite | Level 6 DF
Fluorite | Level 6

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
Calcite | Level 5

Hi DF,

works great. thanks a lot.

DF
Fluorite | Level 6 DF
Fluorite | Level 6

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 3000 views
  • 7 likes
  • 4 in conversation