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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3089 views
  • 7 likes
  • 4 in conversation