connect to oracle db 11g using proxy user and SEPS

[ABCade]

Hello,

I'm trying to connect to an Oracle DB 11g using OCI and proxy users.
As long as I add "oci session pool user id=" and "oci session pool user password=" everything works fine, but I want to use SEPS which means that the credentials are taken from a wallet.
I think that the SEPS is configured right because when I connect with sqlplus like this:
sqlplus []/@
it works,
but when I try to leave the "oci session pool user id" or "password" empty, I get:
ORA-01005 null paword given, logon denied

Any help will be appriciated ...

[ABCade]

Another thing I should add is that when I used the Oracle provider, I could do this by putting "/" as the "proxy user id" in the connection string (the part which is like the "oci session pool user id" in the devart provider)

[Shalex]

1. What is SEPS? Could you please give us a link to the article which describes the way how to set and use SEPS?
2. You mean that this functionality works with ODP.NET, don't you?

[ABCade]

Thanks for your reply,

1. SEPS - Secure External Password Store - a mechanism that replaces the "External authentication" used untill 11g:
http://www.oracle-base.com/articles/10g ... _10gR2.php
http://www.oracle.com/technetwork/datab ... 133399.pdf

2. yes

[ABCade]

I need some way to init the connection string parameters so that the provider will connect to the DB with something like this:
[]/@

Is there a way to override some method so that giving the db user for "User Id" and "/" for "oci session pool user id" and omitting the "oci session pool user password" will connect to the DB with a connection string as written above ?

Thanks,
AB

[Shalex]

We are investigating the SEPS issue and will post here about the results as soon as possible.

[azize]

Hello,

We have the same problem.
DevArt, do you have any solution or workarround?

Regards

[Shalex]

We are investigating the issue.

[Shalex]

The proxy SEPS connection can be established with dotConnect for Oracle using two Devart.Data.Oracle.OracleConnection objects:
1st - its connection string is without explicit user id and password (uses SEPS), create and open this connection;
2nd - set only user id in its connection string and pass the 1st connection as a parameter: conn2.Open(conn1)
http://www.devart.com/dotconnect/oracle ... on%29.html

Proxy connections can be used in Entity Framework as well: pass conn2 from the previous example to the constructor of DataContext (EF v4.1/v4.2/v4.3). The scenario is more complicated when using ObjectContext (EFv1, EFv4) because EntityConnection (not OracleConnection) has to be passed to it. EntityConnection includes OracleConnection and MetadataWorkspace with loaded metadata.

We are investigating the possibility of implementing the Proxy User Id and Proxy Password connection string parameters.

[Bassal]

Greatings,

We find this interesting as well, and will be most happy to have these features.

Bassal

[amitr]

this issue can be very useful for us.
we are looking foreword for updates.

[Shalex]

Specifying proxy connection via the connection string with the new connection string parameters "Proxy User Id" and "Proxy Password" is supported (only in the OCI mode) in dotConnect for Oracle starting from the 8.2.80 build.

Examples of connecting via proxy user:

a) with two OracleConnection objects

Code: Select all

      using (OracleConnection proxyConnection = new OracleConnection("Data Source=ORA; User Id=my_proxy_user; Password=my_proxy_user_password;")) {
        proxyConnection.Open();

        OracleConnection scottConnection = new OracleConnection("User Id=scott; Pooling=false;");
        scottConnection.Open(proxyConnection);

        OracleCommand cmd = scottConnection.CreateCommand();
        cmd.CommandText = "SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual";
        using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
          reader.Read();
          Console.WriteLine("PROXY_USER '{0}'", reader.GetString(0));   // 'MY_PROXY_USER'
          Console.WriteLine("SESSION_USER '{0}'", reader.GetString(1)); // 'SCOTT'
        }
      }

b) with one OracleConnection object and the Proxy User Id, Proxy Password connection string parameters

Code: Select all

      using (OracleConnection proxyConnection = new OracleConnection("Data Source=ORA; Proxy User Id=my_proxy_user; Proxy Password=my_proxy_user_password; User Id=scott;")) {
        proxyConnection.Open();

        OracleCommand cmd = proxyConnection.CreateCommand();
        cmd.CommandText = "SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual";
        using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
          reader.Read();
          Console.WriteLine("PROXY_USER '{0}'", reader.GetString(0));   // 'MY_PROXY_USER'
          Console.WriteLine("SESSION_USER '{0}'", reader.GetString(1)); // 'SCOTT'
        }
      }