connect through for OraOLEDB/connecting from one user to another using OraOLEDB/Connect from one use-CodePudding

I had granted connect through privileges for one user to another using below code

alter user user_a grant connect through user_b;

Then using sqlplus I was able to connect to user_a by using user_id and password for user_b using command below

sqlplus user_b[user_a]/password_b@MyDatabase

How can I do it using OraOLEDB

I had tried below but it gave user id string error

connstring = "Provider=OraOLEDB.Oracle;Persist Security Info=False;Data Source=MyDatabase;User ID=user_b[user_a];Password=password_b;Data Provider=MSDASQL"

CodePudding user response：

Hello

ODP.NET applications can use proxy authentication by setting the "Proxy User Id" and "Proxy Password" attributes in the connection string. The real user is specified by the "User Id" attribute. Optionally, to enforce greater security, the real user's password can be provided through the "Password" connection string attribute. When using distributed transactions in conjunction with proxy authentication, the real user's password is no longer optional, and it must be supplied.

connect through should be configured in the OLEDB connection string using the aforementioned format:

Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Proxy User Id=pUserId;Proxy Password=pPassword;

CodePudding user response：

Looks like only option available with ADODB connection for EXCEL macro Visual basic is to run below alter statement

alter session set CURRENT_SCHEMA=proxy_user

but then grants need to be handled explicitly for the actual user, this is an disadvantage using this.