Oracle security and SmartGWT processRPCTransaction

[webfrustrated]

I am using version 3.0EE to build an application for a client. The client is using Oracle for the enterprise database. The project requires my team to use a mechanism similar to Oracle's Proxy Authentication.

For each request to the database, we are forced to call a stored procedure in Oracle passing the UserID of the user currently logged into the web application. This stored procedure sets up some security and some role stuff in the database for the user.

I was not sure of the best approach in SmartGWT, but here is what I came up with. We are making use of the ds.xml files, but I did not want to have to go thru a DMI handler for each ds.xml operation. I wanted to the call to the stored procedure to not add any complexity to the ds.xml operations. We already extended the IDACall to make use of the build in role management feature.

I decided to put the call to the stored procedure into our servlet that now extends IDACall. Here is some of the code:

public void processRequest(HttpServletRequest request,
               HttpServletResponse response)
        throws ServletException, IOException
       {
           HttpSession session = request.getSession();

	   Object user = session.getAttribute("SESSION_USER");
  
           if (user.getRoles() != null) {
               try {
                   RequestContext context = RequestContext.instance(this, request, response);   
                   RPCManager rpc = new RPCManager(request, response);
                   rpc.setAuthenticated(true);
                   rpc.setUserRoles((String) roles); 
                   
                   // call processRPCTransaction() to iterate through all RPCRequests and
                   // DSRequests and execute them
                   //processRPCTransaction(rpc, context);
	           setUser(rpc, context, user.getUserID());		
  
               } catch (Throwable e) {
                   handleError(response, e);
               }
           } else {
               super.processRequest(request, response);
           } 
       }

private void setUser(RPCManager rpc, RequestContext, String userID) {
	Connection conn = null;
	CallableStatement cstmt = null;
        ResultSet rs = null;

        try {
            SQLTransaction.startTransaction(rpc);
            conn = SQLTransaction.getConnection(rpc);
            cstmt = conn.prepareCall("{ call xyz.THE_STORED_PROC(?) }");
            cstmt.setString(1, userID);
            cstmt.execute();

	    processRPCTansaction(rpc, context);

            SQLTransaction.commitTransaction(rpc);
        } catch (Exception e) {
            SQLTransaction.rollbackTransaction(rpc);
	    throw e;
	} finally {
	    if (rs != null)
                rs.close();
            if (cstmt != null)
                cstmt.close();
            if (conn != null)
                conn.close();
        }
}

You can see that I have replace the line "processRPCTransaction(rpc, context);" with "setUser(rpc, context, user.getUserID());". This works as intended in terms of successfully calling the stored procedure before each operation defined in a ds.xml file. The only problem is that I get the following error: "java.lang.Exception: No current connection for 'null'". This occurs at the line that that tries to commit the transaction - "SQLTransaction.commitTransaction(rpc)".

My guess is that the call to processRPCTransaction commits the transaction and sets the connection object to null. Can you just verify this for me or suggest a better approach to the one that I have already taken?

[Isomorphic]

Your current code doesn't work because it is trying to introduce a transaction way too early in the processing order. At the point where you're trying to establish a transaction, we haven't even looked at what DataSources are involved or loaded their SQL driver settings.

The right way to do this is to create a Custom DataSource that subclasses SQLDataSource, overrides execute(), and does an additional DSRequest to do the SQL operation you need to do via SQL Templating (specifically, take your stored procedure code and make a <customSQL> code out of it).

Alternatively if you prefer to work with SQLConnection directly, this sample shows the right approach.

[webfrustrated]

We are still having some issues here. We have our own servlet called "SecureIDACall" that extends IDACall. In the processRequest() method, we set up the RPCManager in order to take advantage of the field level security. We basically have the same code in place as shown in the API docs:

HttpSession session = request.getSession();
           Object roles = session == null ? null : session.getAttribute("roles");
  
           if (roles != null) {
               try {
                   RequestContext context = RequestContext.instance(this, request, response);   
                   RPCManager rpc = new RPCManager(request, response);
                   rpc.setAuthenticated(true);
                   rpc.setUserRoles((String) roles); 
                   
                   // call processRPCTransaction() to iterate through all RPCRequests and
                   // DSRequests and execute them
                   processRPCTransaction(rpc, context);
  
               } catch (Throwable e) {
                   handleError(response, e);
               }
           } else {
               super.processRequest(request, response);
           }

My first question is, what will the TransactionPolicy bet set to at this point - NOT_SET OR NONE? In our case, it is 0 which looks to be NOT_SET.

Can you confirm that the TransactionPolicy must be set here? It seems that if you try to set it later in the SecureIDACall servlet, SmartGWT will throw an error saying something that the "transaction policy can not be set once it has been created...".

We have also created a SQLDataSource that all of our ds.xml (DataSources) inherit from. In this DataSource we override the execute() method with our own implementation. In this override, we would like to be able to do a few things.

1. Manually control the transaction management
2. For each passed in DSRequest, use a single or the same database connection since we have to call an Oracle stored procedure before each CRUD operation to mimic Oracle's Proxy Authentication.

We have the following:

public DSResponse execute(DSRequest dsRequest) throws Exception {
Connection conn = null;

String operationType = dsRequest.getOperationType();

if (!operationType.matches(DataSource.OP_FETCH)) {
conn = (Connection)((SQLDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest);
} 

SQLTransaction.startTransaction(rpc);

if (conn == null) {
conn = SQLTransaction.getConnection(rpc);
}

JDBCOperations jdbcOps = new JDBCOperations();

try {
// call to our Oracle stored procedure to set security in DB for the user
jdbcOps.setUser(conn, userId);

List<DSRequest> requests = rpc.getRequests();

for (DSRequest req : requests) {
dsResponse = super.execute(req);
}

SQLTransaction.commitTransaction(rpc);
} catch (SQLException sqle) {
SQLTransaction.rollbackTransaction(rpc);
} finally {
SQLTransaction.endTransaction(rpc);
}

return response;
}

I initially tried to wrap the call to the stored procedure into a new DSRequest.

Map values = DataTools.buildMap("UserID", "JohnDoe");
DataSource ds = DataSourceManager.get("UserDataSource");
DSRequest spRequest = new DSRequest();
spRequest.setDataSource(ds);
spRequest.setOperationType(DataSource.OP_CUSTOM);
spRequest.setOperationId("spSetUser");
spRequest.setValues(values);
spRequest.setPRCManager(rpc);
spRequest.addSubRequest(dsRequest);

ds.xml file

<operationBinding operationType="custom" operationId="spSetUser">
<customSQL>call XX_XX.SP_SET_USER($values.UserID)</customSQL>
</operationBinding>

In the log, I can see it being called correctly, but a SQL exception is always thrown: "Cannot perform fetch on a PLSQL statement: next". This will work fine if I place this in a method w/in our JDBCOperations class using a CallableStatement;

public void setUser(Connection conn, String userID)...
CallableStatement cstmt = null;

try {
cstmt = conn.prepareCall("{ call XX_XX.SP_SET_USER(?) }");
csmt.setString(1, userID);
cstmt.execute();
} catch (SQLException sqle) {
throw sqle;
} finally {
cstm.close;
}

Calling the stored procedure like so works fine w/out SQL error. When I run the code, for fetches I can see the call to the SP to set the user and the select in the same transaction and it seems like the same database connection. The problem is when I try to execute an "add" or an "update". In the log everything looks fine, but it fails when the built in SELECT stmt. for the updated record or the newly inserted record is executed after the update or the insert.

We get the following: No current connection for 'null'. I can see the update statement issued in the log, and I can confirm the update in the database. My understanding is that SmartGWT will then select the newly added or updated record to be returned in the DSResponse - correct? I suspect that since we are manually ending the transaction that this might be the cause.

[Isomorphic]

The transaction setting is potentially influenced by settings on DataSources and when you are in IDACall before processRPCTransaction(), the DataSources that will process the requests have not been loaded yet - it's too soon to check the transaction policy, but there would be no need to do so anyway given that this is not where you should be initiating extra DSRequests.

We can't make any sense of this code:

if (!operationType.matches(DataSource.OP_FETCH)) {
conn = (Connection)((SQLDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest);
} 

SQLTransaction.startTransaction(rpc);

if (conn == null) {
conn = SQLTransaction.getConnection(rpc);
}

It appears to try to get an existing transaction but then unconditionally start a new transaction .. ? We don't know of a reason why you would make any calls to SQLTransaction at all for your use case - seems like you would use getTransactionObject() only. Your unnecessary code is probably causing the issue you report with re-selecting data.

As far as how you're invoking the stored procedure, that looks fine, and we'd probably need to see the PLSQL code to tell you why it results in the weird error "Cannot perform fetch on a PLSQL statement: next". But you don't need to use a DSRequest to execute this procedure anyway, your code from JDBCOperations is fine too.

[webfrustrated]

The unclear code is a bit of a hack. It seems that for operations not placed within RPCManager.startQueue() and RPCManager.sendQueue() like ComboBoxItem.fetchData() in a DynamicForm will throw a NullPointer when getTransactionObject() is called. This sort of makes sense since the call to getTransactionObject() will always return a Connection after casting when the following is executed on the client.

RPCManager.startQueue();
form1.saveData()
form2.saveData()
RPCManager.sendQueue();

Ideally you don't usually execute a fetch/select within a transaction, but our goal here is to execute the stored procedure to set the user in Oracle and on the same connection execute a fetch, insert, update or delete.

[Isomorphic]

Right, singular operations don't use transactions by default. You can change this so transactions are used unconditionally, however, it would never make sense to obtain the current Connection and then call startTransaction(), which is what your code is doing.

Separately, how did you get an NPE? Can you show a stack trace?

[webfrustrated]

Can you point me to an example or lead me in the direction as to how to ensure that transactions are used unconditionally? I am just curious as to how this might be done.

I will post the stack trace in the morning when back at my office.

[Isomorphic]

It's not exactly worthy of a sample, it's just a one-line setting in server.properties - see setTransactionPolicy().

[webfrustrated]

So if I understand correctly, in the server.properties you can set autoJoinTransactions: ALL? If correct, will this be lost when the RPCManager is instantiated in the SecureIDACall servlet?

if (roles != null) {
               try {
                   RequestContext context = RequestContext.instance(this, request, response);   
                   RPCManager rpc = new RPCManager(request, response);
                   rpc.setAuthenticated(true);
                   rpc.setUserRoles((String) roles); 
                   
                   // call processRPCTransaction() to iterate through all RPCRequests and
                   // DSRequests and execute them
                   processRPCTransaction(rpc, context);
  
               } catch (Throwable e) {
                   handleError(response, e);
               }
           } else {
               super.processRequest(request, response);
           }

Also by setting the TransactionPolicy in the server.properties, are commits and rollbacks automatic now or can they still be manually issued?

[Isomorphic]

Commits and rollbacks are always automatically issued whenever autoJoinTransactions is used for a request. The setting autoJoinTransactions: ALL just means that transactions will be used even for single-request RPCs.

This is why you need to delete all code your manual attempts to manage transactions as we previously indicated - its unnecessary, and you'll interfere with the automatic mechanism.

[webfrustrated]

In regards to the NullPointer - we have a ComboBoxItem that has static values assigned to it. Once a user selects an option from this ComboBox, a second ComboBoxItem is populated with dynamic values after querying the database.

Dynamic ComboBoxItem

DataSource ds = DataSource.get("DYNAMIC_SEARCH");

Criterion c = new Criterion("ID", OperatorId.IEQUALS, "dynamic_id_value");

ComboBoxItem dynamicResults = new ComboBoxItem("FIELD_NAME");
dynamicResults.setOptionDataSource(ds);
dynamicResults.setOptionOperationId("fetchResultsBySomeId");
dynamicResults.setPickListCriteria(c);

from the log:

...SecureIDACall: Entering processRequest
...RPCManager - Processing 1 requests.
...PRCManager - Request #1 (DSRequest) payload: {
criteria: {
dataSource:"DYNAMIC_SEARCH",
operationType:"fetch",
textMatchStyle:"startsWith"
},
componentId:"isc_PickListMenu_1",
appID:"builtinApplication",
operation:"search",
oldValues:null
}
...SecureIDACall: Calling processRPCTransaction()...
...AdminDataSource: Entering: execute
...AdminDataSource: DataSource operation: search
...AdminDataSource: Entering: setUser
...AdminDataSource: Connection is null.
...JDBCOperations: Entering: setUser
...JDBCOperations: calling SP...(?) for user X
WARN RequestContext - dsRequest.execute() failed:
java.lang.NullPointerException
at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:212)
com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
...SecureIDACall.processRequest(SecureIDACall.java:77)
com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)

So the debug line, "Connection is null.", occurs when trying to execute the following line of code:

conn = (Connection)((SQLDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest);

However, I can get the connection within in our custom SQLDataSource class by using the following:

conn = this.getTransactionalConnection(dsRequest);

[webfrustrated]

Update: So set sql.Oracle.autoJoinTransactions: ALL in my server.properties file. In my custom SQLDataSource, I see that the TransactionPolicy is 0, meaning NOT_SET.

To get around this, in the SecureIDACall servlet I set the transaction policy like so:

RPCManager rpc = new RPCManager(request, response);
rpc.setTransactionPolicy(TransactionPolicy.ALL);
rpc.setAuthenticated(true);
rpc.setUserRoles((String) roles);

I now see the TransactionPolicy set to 4 in the execute() method of my SQLDataSource. I have set up a test case to place a remove and in update in queue on the client.

RPCManager.startQueue();
ds.removeData(recordToDelete);
ds.update(recordToUpdate);
PRCManager.sendQueue();

The remove operation is defined in my ds.xml file as follows:

<operationBinding operationType="remove">
<customSQL>call SP_DELETE_ENTITY($values.ENTITY_ID)</customSQL>
</operationBinding>

So in the log, I observe the following:
...AdminDataSource: transaction policy: 3
...PoolableSQLConnectionFactory... Returning pooled Connection
SQLTransaction... Started new Oracle transaction "7559841"
...JDBCOperations: calling SP_SET_USER(?) for user: JOHNDOE
...SQLDataSource... Performing remove operation with criteria: {ENTITY_ID:"1234"} values:{ENTITY_ID:"1234"]
...SQLDriver... Executing SQL update on 'Oracle': call SP_DELETE_ENTITY('1234')
...SQLDataSource... remove operation affected no rows
... AdminDataSource: DataSource operation: ENTITY_TABLE_update
...JDBCOperations: calling SP_SET_USER(?) for user: JOHNDOE
...SQLDataSource... Performing update operation with criteria: {ENTITY_ID:"16660"} values:{ENTITY_ID:"16660", ENTITY_COMMENTS:"testing..."]
...SQLDriver... Executing SQL update on 'Oracle': UPDATE ENTITY_TABLE SET ENTITY_COMMENTS='testing...' WHERE (ENTITY_ID='16660')
RequestContext - dsRequest.execute() failed:
java.sql.SQLException: ORA-040...
...SQLTransaction - Rolling back Oracle transaction "7559841"
...SQLTransaction - Ending Oracle transaction "7559841"

Looking at the log, I would think that the record that I was attempting to delete should still be in the database, but this is not the case. It looks like the delete record was committed and not rolled back which leads to some confusion.

The stored procedure is as follows:
[CODE]
create or replace procedure "SP_DELETE_ENTITY" (
old_entity_id in number)
begin
delete from entity_child_table where entity_id = old_entity_id;
delete from entity_table where entity_id = old_entity_id;
end;

[Isomorphic]

Again, checking transactionPolicy in a servlet is too early because it potentially depends on settings in DataSources, which have not yet been loaded at that time. Check it from DMI code and you'll see the expected values.

When you are in a transaction, if you delete a row and check if it's deleted, it will appear to be deleted. This doesn't mean there has been an auto-commit - to other users the row is still there. This is how transactions work in general.

[webfrustrated]

Sure I understand what you are getting at and I understand transactions, but you would expect the deleted record to still be in the database after opening a client tool like Toad or SQLDeveloper and querying for the record. The record is no longer in the database table after observing the rollback issued in the log.

[Isomorphic]

That does sound like an autocommit, but from your logs, it's not SmartGWT doing it. It could be something about your stored procedure, or code you've added to try to explicitly set transactionPolicy or manually commit transactions, if that's still around.