Connecting to Oracle with Proxy Authentication

In This Topic

This topic is applicable only for full .NET Framework.

This topic describes how to connect to Oracle server using proxy authentication.

Proxy authentication allows middle-tier applications to control the security by preserving database user identities and privileges. Oracle allows creating a proxy database user, connecting and authenticating against the database on behalf of database users.

Here is an example of proxy user creation and modifying a standard Scott user to allow Scott use proxy connection (note that you need to execute script as a user that has the CREATE USER privilege):

     CREATE USER my_proxy_user IDENTIFIED BY my_proxy_user_password;
     GRANT connect, resource TO my_proxy_user;
     ALTER USER scott GRANT connect through my_proxy_user;

Proxy authentication is not supported in Direct mode.

With dotConnect for Oracle you can connect to Oracle using proxy authentication in two ways: either using two connection instances and the Open method overload, accepting an OracleConnection, and using a single OracleConnection instance with the Proxy User Id and Proxy Password connection string parameters.

Here is an example, using two connections:

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

Visual Basic

Using proxyConnection As New OracleConnection("Data Source=ORA; User Id=my_proxy_user; Password=my_proxy_user_password;")
	proxyConnection.Open()

	Dim scottConnection As New OracleConnection("User Id=scott; Pooling=false;")
	scottConnection.Open(proxyConnection)

	Dim cmd As OracleCommand = scottConnection.CreateCommand()
	cmd.CommandText = "SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual"
	Using reader As OracleDataReader = 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'
	End Using
End Using

And the next example uses the Proxy User Id and Proxy Password connection string parameters:

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

Visual Basic

Using proxyConnection As New OracleConnection("Data Source=ORA; Proxy User Id=my_proxy_user; Proxy Password=my_proxy_user_password; User Id=scott;")
	proxyConnection.Open()

	Dim cmd As OracleCommand = proxyConnection.CreateCommand()
	cmd.CommandText = "SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual"
	Using reader As OracleDataReader = 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'
	End Using
End Using

See Also