connect to oracle db 11g using proxy user and SEPS

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ABCade
Posts: 8
Joined: Sun 30 Oct 2011 12:36

connect to oracle db 11g using proxy user and SEPS

Post by ABCade » Sun 30 Oct 2011 12:54

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
Posts: 8
Joined: Sun 30 Oct 2011 12:36

Another thing

Post by ABCade » Mon 31 Oct 2011 08:22

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 03 Nov 2011 15:34

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
Posts: 8
Joined: Sun 30 Oct 2011 12:36

Post by ABCade » Mon 07 Nov 2011 07:14

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
Posts: 8
Joined: Sun 30 Oct 2011 12:36

Perhaps a different approach

Post by ABCade » Wed 09 Nov 2011 19:01

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 17 Nov 2011 17:07

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

azize
Posts: 1
Joined: Tue 20 Dec 2011 13:16

Solution

Post by azize » Tue 20 Dec 2011 13:27

Hello,

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

Regards

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 29 Dec 2011 09:15

We are investigating the issue.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 11 Jan 2012 12:19

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
Posts: 1
Joined: Sun 15 Jan 2012 08:16

Good News!

Post by Bassal » Sun 15 Jan 2012 08:23

Greatings,

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

Bassal

amitr
Posts: 1
Joined: Sun 15 Jan 2012 11:24

Proxy User Id and Proxy Password connection string parameter

Post by amitr » Sun 15 Jan 2012 11:33

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

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: connect to oracle db 11g using proxy user and SEPS

Post by Shalex » Mon 21 Apr 2014 15:11

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'
        }
      }

Post Reply