I have posed this at the "jguru ejb forum" with not much milage so far. I am hoping that you will be able to throw some more light.
I am trying to migrate a standard cleint-server (financial) app to a J2EE app (JBoss 3.2).
The existing app has an elaborate security model built into the database and various grant privileges to various roles.
For instance, when a trader books a trade it is held somewhere to be checked (counterparty matching, limits, risk etc). When this trade is booked the user's id is logged into the DB's trade transaction table.
There is a DB trigger that:
* checks to see if the user name provided by via the sql statement matches the user name in the connection object [con.getMetaData().getUserName()]
* also the user's role (held in a table elsewhere) is checked for the correct role via the trigger
On moving to a J2EE model, the container seems to hold a pool of connection objects to the database and not an a per user basis (as it is currently with a 2-tier client-server system).
What would be a work around to this?
Of course, some would arge that business logic should be in the middle layer etc.
But the fact remains that in the "real" world (made of rouge traders) some would prefer the checks to be (always) done at the DB level so that one cannot point a database tool and make changes via the back door.
I have seen these kinds of issues before. Your options are:
1. Rewrite your security model to be more J2EE.
2. Don't use J2EE connection pooling. Instead, write a custom connection pool, that maintains exactly one connection per user. Something like this:
Connection con = ConnectionPool.getConnection(user);
Bear in mind that databases have a limit on the total number of connections, so "one connection per user" limits on your application's scalability. If your database does not allow more than 256 connections, you can never have more than 256 concurrent users.
Well the very fact that the container is providing you with a connection pool, restricts you to use 1 user(used by the connection pool) to connect to the database. So a typical suggestion is to make a user/users having privileges equivalent to the various users or roles in the database. So you could now have a connection pool per role, or have a single database user with all the privileges, and one connection pool.
Finally to keep up with the security aspect, check for authorization in the app server. Here too you could use two approaches. You can use the simple model of defining users and roles in the deployment descriptor and define their privileges. Else the worst case scenario would be to build a JAAS module and use it for your deployment. This way you could do all the checks you need in your implementation of JAAS.
Hope this helps.