Entity Framework Proxy Authentication with Oracle

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Sg1team
Posts: 3
Joined: Mon 22 Oct 2012 10:30

Entity Framework Proxy Authentication with Oracle

Post by Sg1team » Mon 22 Oct 2012 10:38

Hi,

I'm currently looking for a way to enable proxy authentication in my Asp.NET MVC 3-Application with Entity Framework. I use the database first approach to auto-generate my database model from my oracle database. What I want is to allow users to log in with their oracle username/password while preserving all database-relevant rules/restrictions.
How could this be achieved?

Thanks,
Sg1team

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

Re: Entity Framework Proxy Authentication with Oracle

Post by Shalex » Fri 26 Oct 2012 16:05

Currently proxy connection can be established only via conn2.Open(conn1):
http://www.devart.com/dotconnect/oracle ... on%29.html. But the connection, which is proxied in this way, is rejected by the DbContext constructor: http://msdn.microsoft.com/en-us/library ... 03%29.aspx.

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

Sg1team
Posts: 3
Joined: Mon 22 Oct 2012 10:30

Re: Entity Framework Proxy Authentication with Oracle

Post by Sg1team » Tue 30 Oct 2012 07:48

Hi,

thanks for your answer.
Which alternative do I have then to implement proxy authentication in my Asp.NET MVC4 application?

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

Re: Entity Framework Proxy Authentication with Oracle

Post by Shalex » Thu 01 Nov 2012 13:38

Currently there is no way to use a proxy authentication in Entity Framework via dotConnect for Oracle.

Sg1team
Posts: 3
Joined: Mon 22 Oct 2012 10:30

Re: Entity Framework Proxy Authentication with Oracle

Post by Sg1team » Fri 02 Nov 2012 07:53

Hi,

but what about this solution I found searching your docu (using OciSessionPooling for proxy authentication)?

Code: Select all

OracleConnectionStringBuilder oraConBuilder = new OracleConnectionStringBuilder();
oraConBuilder.UserId = "proxy_user";    // Variable username
oraConBuilder.Server = myOracleServer;
oraConBuilder.Home = myOracleClient;
oraConBuilder.PersistSecurityInfo = true;
oraConBuilder.Pooling = true;
oraConBuilder.OciSessionPooling = true;
oraConBuilder.OciSessionPoolUserId = "proxy_conn";    // proxy connection user
oraConBuilder.OciSessionPoolPassword = "proxy_conn_password";    // proxy connection users pw

EntityConnectionStringBuilder con = new EntityConnectionStringBuilder();
con.Name = "TempProxy";

// Metadata needed for Entity Framework
con.ConnectionString = "metadata=res://*/Models.DataModel1.csdl|res://*/Models.DataModel1.ssdl|res://*/Models.DataModel1.msl;provider=Devart.Data.Oracle;";

con.ProviderConnectionString = oraConBuilder.ToString();

MyEntities db = new MyEntities(con.ToString());
This solution works for me with a Entity Framework data model generated from existing database. Hereby proxy_conn only has basic permissions. proxy_user has CONNECT THROUGH proxy_conn and advanced permissions to actually do some database work.

Can I proceed using this solution or are there some drawbacks I did not consider?

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

Re: Entity Framework Proxy Authentication with Oracle

Post by Shalex » Mon 05 Nov 2012 12:43

Shalex wrote:Currently there is no way to use a proxy authentication in Entity Framework via dotConnect for Oracle.
There is no way in the Direct mode.

Code: Select all

oraConBuilder.Pooling = true;
oraConBuilder.OciSessionPooling = true;
Pooling is a pool implementation in dotConnect for Oracle itself.
OciSessionPooling is a pool implementation in Oracle client. If OciSessionPooling is used, the dotConnect for Oracle pool is disabled.
Sg1team wrote:Can I proceed using this solution or are there some drawbacks I did not consider?
The Oci Session Pooling feature is available only in the OCI mode (via Oracle Client).

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

Re: Entity Framework Proxy Authentication with Oracle

Post by Shalex » Mon 21 Apr 2014 15:21

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