ODP.net connection pooling: ClientID, the client identifier never changes from the first user who logs in

Question

Scenario : we have an application that uses Oracle 10g and the latest version of ODP.net in an ASP.net application. We use the .ClientID WriteOnly property for the OracleConnection object to pass a specific user ID to the database for audit purposes. When the connection pool is disconnected, this works fine.

When it is turned on, the first user who logs in (for example, USER1) updates the record, and MODIFIED_BY is USER1, but when another user goes to the website after, thus capturing the connection in the pool, MODIFIED_BY remains USER1, despite to enter the system. USER2 for ClientID.

The logic of our database is as follows:

We save the class in an ASP.net session that has the logic for connecting to the database. At the first call, this is our constructor:

Public Sub New(ByVal connection As String, Optional ByVal oracleClientID As String = "") MyBase.New() _oracleConnection = New OracleConnection(connection) _clientID = oracleClientID End If End Sub 

Here is the gist of the code to open the connection and close, get rid of:

 Try _OraCmd = New OracleCommand(command, _oracleConnection) With _OraCmd .BindByName = True .Parameters.Clear() .CommandType = CommandType.StoredProcedure _oracleConnection.Open() If _clientID <> "" Then _oracleConnection.ClientId = _clientID Dim OraDadpt As New OracleDataAdapter(_OraCmd) '' Logic to get data OraDadpt.Fill(ds) End With Catch ex As Exception Throw ex Finally ClearParameters() _OraCmd.Dispose() _oracleConnection.Close() End Try 

The point is that since the connection is in the pool, an alleged LOGON trigger is called, which never happens, and the client ID is never set again. However, the ORACLE documentation says that ClientID is used exactly for what we are trying to do.

Does anyone have any thoughts on why SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER') is not given a new USERID, which is passed to ClientID when the connection pool is used in our .NET application with ODP.net? Is it a database setup, a listener setup?

Update

We sent the question to Oracle. At the same time, we had to create a small test application that simulated a problem. At the same time, everything worked fine on my local host using the built-in Cassini web server in Visual Studio. With IIS, a problem arises.

UPDATE

Determined that IIS is not a problem. Package variables were not cleared because the connections that were merged were reused, essentially what the pool should do. We solved this with DBMS_SESSION.MODIFY_PACKAGE_STATE (DBMS_SESSION.REINITIALIZE).

Answer 1

Try using DBMS.Rest_Package before closing the connections.

The problem that I think is related to pooling the ODP is that it maintains the connection in such a way that each user opens and closes the connection to ODP, while the pool stores session package variables in memory; until the connection expires. However, since the time / exit and connection restoration from the database to the pool occurs only after the connection RETURNS to the pool, you work with other session data.

Answer 2

This works great with both pool and shutdown. ClientId and ClientInfo in an Oracle session are not updated until the command is executed.

Could you confirm the validity of the if statement? If _clientID <> "" Then _oracleConnection.ClientId = _clientID. Even when you close the connection, clientId will still remain the same. Not sure where your setting / getting _clientId is when you pass this to your method.

 class Program { static void Main(string[] args) { TestClientId test = new TestClientId(); test.DoSomething("FirstClientId"); test.DoSomething("ChangedClientId"); } } public class TestClientId { /// <summary> /// The connection string. /// </summary> private const string ConnString = "DATA SOURCE=//server:port/service_name;USER ID=user;PASSWORD=pswd;"; /// <summary> /// The oracle connection. /// </summary> private OracleConnection connection; /// <summary> /// The oracle session id. /// </summary> private long sid; /// <summary> /// Initializes a new instance of the <see cref="TestClientId"/> class. /// </summary> public TestClientId() { this.connection = new OracleConnection(ConnString); } /// <summary> /// Changes the client id of the oracle connection. /// </summary> /// <param name="clientId">The client id.</param> public void DoSomething(string clientId) { this.connection.Open(); this.sid = this.GetSessionId(this.connection); if (!string.IsNullOrEmpty(clientId)) { this.connection.ClientInfo = clientId; this.connection.ClientId = clientId; } OracleCommand command = new OracleCommand("select * from dual", this.connection); command.ExecuteNonQuery(); this.connection.Close(); } /// <summary> /// Gets the session id. /// </summary> /// <param name="con">The connection object.</param> /// <returns>The current oracle session id.</returns> public int GetSessionId(OracleConnection con) { OracleCommand cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "select SYS_CONTEXT('USERENV','SID') from dual"; object sid = cmd.ExecuteScalar(); return Convert.ToInt32(sid); } } 

Answer 3

ClientId is reset only when the connection is closed, and if you close it, then its border should be reset.

The connection pool helps the database server timeout the idle session and use the connection to serve the active session. A simple logical session remains open, and the physical connection is automatically restored when the next request comes from that session. So is the connection really closed?

Therefore, it would be nice to set the session ID using DBMS_SESSION.SET_IDENTIFIER

Hope this helps

Answer 4

It is very dangerous for me to maintain a link to the database connection in your session. The idea of ​​the connection pool is that the request takes the connection for the duration of the request. When the request completes, the connection returns to the pool. Then the connection will be reused for more requests from different users.

I suspect all sorts of unpleasant things happen when you keep a connection in your session. You are probably using a connection when it is being used by another request at the same time. Or you can get a closed connection because the connection pool must decide to close it.

Furthmore, the LOGON trigger is probably only executed when the database connection is first created, but not executed again when the connection is reused for another request or another user.

To fix the problem, take a database connection at the beginning of each request and explicitly set the client identifier (and / or execute the code triggered by the LOGON trigger). Then use this connection for the duration of the request. But do not store it anywhere after the request is completed.

Thus, your connection is always correctly initialized by the current user context. And you stick to the rules of the connection pool.

Answer 5

When the connection pool is enabled, which is a good and, of course, way of switching to an ASP.NET script (and in most scripts actually), you should not store any db connection. You must open and close connections when you need it.

Here is a link to SQL Server, but it is the same with Oracle that explains it: SqlConnection Class

So the code you need to use when calling Oracle should be something like this anywhere in your application when you need it:

 Using connection As New OracleConnection(connectionString) connection.Open() ' Do work here; connection closed on following line. End Using 

The fact is that you cannot enable the connection pool with specific user information in the connection string. Therefore, I suggest you implement your audit code without using a connection string.