Advice needed for identifying Client User when using SQLDatasource against Orcale DB

[Staccato70]

1) Using SmartClient Version: v9.1p_2014-07-31/PowerEdition Deployment (built 2014-07-31)
2) IE 11

Background:
We have several tables in our Database (Oracle) that have auditing performed by Triggers when changes are made to data in these table. These triggers use session/connection information in order to determine who is making the change. Since we are using shared connections, the UserId on the DB Connection is not the actual user of the GUI, so we need to tell Oracle the remote UserId. This is being done through a call to DBMS_APPLICATION_INFO.set_client_info('<UserId>').

The way our authentication works is that we get the UserId of the authenticated user from the HTTPServletRequest Header.

Current Approach:
1) Create a StoredProcedure DataSource to hold the various Stored Procedure Calls as OperationBindings

<DataSource ID="StoredProcedures"
  serverType="sql"
   dbName="Oracle">     
 <operationBindings> 
  <operationBinding operationType ="update" operationId="spSetClientInfo" >
   <customSQL>call DBMS_APPLICATION_INFO.set_client_info($userId)</customSQL>
  </operationBinding>
  <operationBinding operationType ="update" operationId="spClearClientInfo" >
   <customSQL>call DBMS_APPLICATION_INFO.set_client_info('')</customSQL>
  </operationBinding>
 </operationBindings>  
</DataSource>

2) Extend SQLDataSource to execute the stored procedure that sets the Client Info before executing the actual SQL (making sure to clear it after).

import javax.servlet.http.HttpServletRequest;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.sql.SQLDataSource;

public class CUIDSQLDataSource extends SQLDataSource {
 private static final long serialVersionUID = 1L;

 @Override
 public DSResponse execute(DSRequest req) throws java.lang.Exception{
  //If only a fetch, then no need to set Remote USER ID
  if(req.getOperationType().equalsIgnoreCase("fetch")){
   return super.execute(req);
  }else{
   //Get the User Id from the Header, if not found set it to Empty String
   HttpServletRequest servletReq = req.getHttpServletRequest();
    if(servletReq!=null){
    String userId=servletReq.getHeader("remote-user");
    if(userId==null ) {
     userId = "";
    }
    
    //Set the UserId on the RPCManager so the Stored Procedure DS
    //has access to it via the $userId velocity variable
    req.getRPCManager().setUserId(userId);
    //Call Stored Procedure to set Remote User Id
     DSRequest spReq = new DSRequest("StoredProcedures","update");
    spReq.setOperationId("spSetClientInfo");
    //Make it part of the same Transaction by using the same RPCManager
    spReq.setRPCManager(req.getRPCManager());
    DSResponse spResp = spReq.execute();

    if(spResp.getStatus()==DSResponse.STATUS_SUCCESS){
     //Setting of Client User ID succeeded so execute original request
     DSResponse superResp = super.execute(req);
    
     //Call the Stores Procedure to clear the User Id
     DSRequest spClearReq = new DSRequest("StoredProcedures","update");
     spClearReq.setOperationId("spClearClientInfo");
     spClearReq.setRPCManager(req.getRPCManager());
     DSResponse spClearResp = spClearReq.execute();

     //return the response from the original request
     return superResp;
    }else{
     return new DSResponse(){{setFailure("Unable to set Client Info in the Database.");}};
    }
    
   }
   return new DSResponse(){{setFailure("Unable to get Client Info.");}};
  }
 }
}

Questions:
1) Is this the recommended method of doing this or is there an easier way?
2) Is it safe in terms of not allowing multiple users to show the same Remote User Id given that the connection is shared?
3) Any suggestions on improvement?

Thanks!

[Isomorphic]

The danger of your approach is of course accidently returning a privileged SQL connection to the pool, where it somehow gets borrowed by another user, or perhaps by an operation that is not triggered by a user (some kind of housekeeping).

An alternative to this, which might be feasible if there are just a few database userIds, would be to use a different dataSource.dbNames for each distinct database-level userId. You could then use the Dynamic DSGenerator approach shown in this thread. In effect, each set of your users would use a different pool of SQL connections.

If this isn't feasible (too many distinct SQL users), your existing approach is OK, but we'd suggest being very careful about error handling: no matter what the error, you want to make sure you always return your connections to the unprivileged state, or alternatively, that you always set a userId on the connection at the beginning of using it, even if it doesn't require privilege per se.

[Staccato70]

Thank you for the quick response Isomorphic.

Regarding your concern about returning privileged SQL connections to the pool, I thought that setting the RPCManager on the requests that are being code generated to be the same as the Initial Request's RPCManager that these would be treated as one DB transaction, therefore it could be relied upon that they would either all succeed or all fail and that the connection would not be returned to the pool until all of them are completed. Is this incorrect?

[Isomorphic]

That's correct. So the danger is accidentally returning a privileged connection to the pool at the end of the transaction.