2

I have been granted access to a specific schema in an Orale database via a "proxy user" / "connect through" .

In SQL Developer / TOAD, I give my username as "MyUser[SomeSchema]", and after login I am now able to query all tables etc. in SomeSchema. I can also login using just "MyUser", but then I am not able to query any tables in SomeSchema (not even if using the schema.table syntax.)

So my question is: How do I "replicate" the MyUser[SomeSchema] approach when connection from code using a connection string (using ODP.NET on .NET)?

UPDATE 1
I found that if I run the following SQL in Toad, I actually get the result I want.

connect MyUser[SomeSchema]/{password for MyUser};

My current schema is changed to SomeSchema and I can query it's tables.

Question is, whether this can be achieved via ODP.NET directly?

UPDATE 2
I finally figured out how to piece together the connection string. See my own answer below.


Research

I've googled quite a bit, and tried the following with no luck.

  • Supplying MyUser[SomeSchema] as "User Id".
  • Supplying the "Proxy User Id" and "Proxy Password" parameters in the connection string. However, I do not have a proxy user password.
  • Changing the schema after login using the "ALTER SESSION" syntax: "ALTER SESSION SET CURRENT_SCHEMA = SomeSchema". This does not fail, but I am still not able to query any tables in the schema.
  • Using SQL Tracker on Toad itself, to see if it ran any SQL to connect to the proxy, but that seems to happen "before" SQL Tracker can pick anything up. Triggers?
  • Am I assuming correctly that your proxy user is the MyUser[SomeSchema] login? Could you clarify that point in your questioin? Then why do you need to switch the user scope? You have been assigned a proxy user based on your requirements. If your requirements changed then you possibly need to apply for additional permissions. (Hint: GRANT Syntax Oracle Documentation) – John aka hot2use Jan 15 '18 at 12:31
  • This is new ground for me, so my understanding may be lacking. However, after a bit more reading, I see that the user[otheruser/schema] syntax is simply something that Toad/SqlPlus and others use to set something ClientId or CLIENT_IDENTIFIER once logged in. My guess is, that my user "MyUser" has been granted a "connect through" to "SomeSchema". Logging in using Toad as MyUser and then running connect MyUser[SomeSchema]/{password for MyUser} actually produces the result I want. So I just need to figure out the best way to do that from code. – Kristian Vinther Jan 15 '18 at 13:17
  • I have updated the question to reflect my recent finding described in my previous comment. – Kristian Vinther Jan 15 '18 at 13:24
2

I finally found out how to represent the proxy user login via my connection string.

  • Proxy User ID = MyUser
  • Proxy Password = MyUser password
  • User Id = SchemaName
  • User Password not set.

The dime finally dropped after visiting https://www.devart.com/dotconnect/oracle/docs/ProxyUserConnection.html and running the SQL query below, after having logged in via Toad. Here it was clear to me, that I had misunderstood, that MyUser was in fact the proxy user...

SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual
0

Proxy Authentication

The following example illustrates the use of ODP.NET proxy authentication:

/*  Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.
    Create a proxy user and modified scott to allow proxy connection.

     create user appserver identified by eagle;
     grant connect, resource to appserver;
     alter user scott grant connect through appserver;
*/

// C#

using System;
using Oracle.DataAccess.Client; 

class ProxyAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();

    // Connecting using proxy authentication
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);

    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

The above should work without the Password attribute. The point of proxy privilege is to enable logging in without disclosing the password of the schema.

  • Thank :D. Yes, I agree on your point. However, I did find the above example in my search, but the filled password attribute had me thinking it was needed. Also, even after solving the issue, the wording in that example still seems odd to me. – Kristian Vinther Jan 16 '18 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.