Entity Framework Proxy Authentication with Oracle

[Sg1team]

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]

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]

Hi,

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

[Shalex]

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

[Sg1team]

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]

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.

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]

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