Oracle Proxy User Question

[TomC4188]

Given the following Oracle proxy user grant

   alter user service-account-name grant connect through personal-account-name;

how might one establish a connection to an Oracle database using
"personal-account-name[service-account-name]" as the user name in SAS?

I don't have access to SAS myself but I work with people who do which makes it difficult for me to diagnose issues with Oracle proxy user access from within SAS.
The only person I worked with so far could not get it to work.
So I am  trying to understand if SAS is capable of seeing and using the "[service-account-name]" clause in the user name field.

I think the following is a valid example.

proc sql;
connect to oracle as myconn (USER='personal-account-name[service-account-name]' PASSWORD='personal-account-password' PATH='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WhateverHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=WhateverServiceName)))');
select * from connection to myconn (select sys_context('USERENV','PROXY_USER') PROXY_USER, sys_context('USERENV','SESSION_USER') SESSION_USER from dual);
disconnect from myconn;
quit;

Thank you.

[SASJedi]

Using the Oracle doc  as a guide, I created these users 

-- Create SCHEMA_OWNER.
create user schema_owner identified by SecretPassword1;
grant create session to schema_owner;

-- Create the proxy user MY_USER_1.
create user my_user_1 identified by MyPassword1;
grant create session to my_user_1;

-- Allow MY_USER_1 to make a proxy connection through SCHEMA_OWNER
alter user schema_owner grant connect through my_user_1;

After that, this SAS code worked fine for me (I find using a LIBNAME statement and then CONNECT USING in PROC SQL is easier to write and understand):

libname myconn oracle path="//my.oracle.server:1521/MY_INSTANCE" 
          user="my_user_1[schema_owner]" password="MyPassword1";
proc sql;
connect using myconn;
select * from connection to myconn 
   (select sys_context('USERENV','PROXY_USER') PROXY_USER, 
           sys_context('USERENV','SESSION_USER') SESSION_USER from dual);
disconnect from myconn;
quit;

                  Results

PROXY_USER	SESSION_USER
MY_USER_1	SCHEMA_OWNER

Hope this helps. May the SAS be with you!
Mark

[TomC4188]

Thank you for your reply.

From what I read PROXY_USER= deals with access to proxy network connections to http or https servers, rather than database account access within an Oracle database.
When researching the topic with web browers I got a lot of hits concerning that PROXY_USER setting so I got some familiarity with what it is used for.

This Stack Overflow link was the closest that I got to finding an SAS database connection example, though it has no database proxy user reference within it, and I have no means to try a test based on it myself:
Connecting to Oracle from SAS - Stack Overflow

[SASJedi]

@TomC4188 The SAS code above shows that, without a doubt, SAS passes the 'personal-account-name[service-account-name]' combo straight to Oracle as-is, and that Oracle recognizes the proxy connection and grants access.

Have your SAS user try this code:

/* Replace these values with those that work for your system: */
%let OracleServer=//my.oracle.server:1521/MY_INSTANCE;
%let myUserID=my_user_1;
%let myPassword=MyPassword1;
%let theProxyID=schema_owner;

/* DO NOT modify the code below this line */
libname myconn oracle path="&OracleServer" 
          user="&myUserId[&theProxyID]" password="&myPassword";
proc sql;
connect using myconn;
select * from connection to myconn 
   (select sys_context('USERENV','PROXY_USER') PROXY_USER, 
           sys_context('USERENV','SESSION_USER') SESSION_USER from dual);
disconnect from myconn;
quit;

You should get results back with no error. 

If this works for your user, please click the "Accept as Solution" button so others looking for help can find the answer more easily.

[TomC4188]

This looks interesting.
Thank you.

I am presently attempting to jump through some hoops to try to get SAS installed on my workstation so as to avoid pestering the one person I know who has it to do my testing for me.
I don't know how extensive the license we have is.
I hope to be able to try that example out myself.

[TomC4188]

I subsequently learned that they are using ODBC for the SAS database connection.
Would the use of ODBC change what you posted?
Or would it be as per your example?

[SASJedi]

That changes the LIBNAME statement a bit, but it should still work. And ODBC connection requires setting up an ODBC  data source that contains the connection info. The system administrator would have set it up, and the other SAS users should know it, too. In this example, the data source is "mydatasource".  Then, the LIBNAME statement would look like this:

libname mydblib odbc datasrc=mydatasource 
        user="my_user_1[schema_owner]" password="MyPassword1";

The rest of the code would work the same. 

[TomC4188]

Thank you.
My request for SAS is wading through the mire of eBureacracy at present.
I am hoping to get it and test with it some time this week.
I passed along some info to the end user having SAS that I have been working with (or  trying to).
I had to reinstall my Oracle client but was able to set up Oracle ODBC with the Windows ODBC data sources.
I am not sure if the end user in question is using the SAS ODBC driver or the Oracle ODBC  driver.
It won't hurt to try both.

[Reeza]

If you're using SAS on a server versus desktop, then you need to set up the ODBC on the server.

[TomC4188]

Thank you.
No worries there.
I know for certain that there is no SAS on any server that I have access to.