I'm using Hibernate 3.2.4 with JBoss 4.2.2 and Oracle 11G.
In my application I connect to the DB with a technical user configured in the data-sources.xml. From a SSO-login a know the real username and I have to tell the DB this username everytime I update data.
I have different ideas but I'm not sure wether they will work.
Any suggestions how this can be done?
Joined: Apr 19, 2011
that's not the point. I don't need any information about the database. Instead, I want to tell the database, which user the update performs (not the technical user of the connection but a logical user).
If you're hoping to connect to the database with the end user's ID and password, that's a problem.
In Enterprise environments such as webapps, efficiency is provided by use of a connection pool. Because it's faster to recycle an existing connection than to open a new one, the pool manager constructs connections as needed, but then keeps them around for later users.
The problem is that the later user may not the the same user. So if you opened a connection using Sally's user credentials, it would be a major problem if you later handed that connection to Roger, and a connection, once opened, cannot change credentials. Thus, the closest approximation you could get using pooling would be to maintain a separate pool for each user. That loses much of the efficiency of pooling, especially in systems with lots of active users.
Because of this, the normal case is to connect with a single userid/password that belongs to the app and has the greatest common denominator of all the privileges of the users of the app. Or more precisely, the GCD of all the privileges needed for the users to use the app.
The onus then falls on the app to ensure that users are constrained to only the functions appropriate for that user. For coarse-grained security, you can get absolute protection by using the J2EE standard container-based security subsystem, which can be used to block unauthorized users at the URL level and prevent violators from even getting near the app. I use this to protect administrator functions, for example. For fine-grained security, you may need to augment this with actual code within the app. There are several mechanisms that can be enlisted to help, such as the use of Spring Security (aecgi), JAAS and Apache Shiro.
Customer surveys are for companies who didn't pay proper attention to begin with.
Joined: Apr 19, 2011
I'm familiar with connection pools, fine grained security, JAAS and all this fancy stuff.
And in pure JDBC i would know how to implement my requirement: get a connection from the pool, call a stored procedure to tell the database who I am (so that subsequent SQL-Calls can use this information inside the database, e.g. for auditing), do some SQL, commit or rollback and close the connection. In a good designed JDBC-Layer, everything but the specific SQL-Calls is implemented in a central place and will be executed automatically.
What I'm searching for is a way to do this with Hibernate.
OK. That ups the ante a bit. Effectively then, it sounds like you have a way to attach additional state information (the user ID) to your transactions.
I can see several ways to approach that.
1. Put a hook on the beginTransaction process that brings in the user ID and applies it
2. Do the same thing using AOP if you're working with a transactional AOP framework such as Spring.
3. You could also potentially hook into the raw "getSession" method used by Hibernate itself if you're not using JPA.
4. Conversely, in JPA, it's possible that you could provide a custom EntityManagerFactory for that purpose, although that may not be a good place to do it, since in my code, at least, the shared EntityManager is pretty much static.
JPA also supports adding pre-commit hooks to Entities, although that might be a bit tedious.
These are just ideas off the top of my head and I'd have to RTFM to determine their viability or for that matter, other possibilities. They're all geared towards the idea that you'd be jamming in an invocation of the identifying stored procedure call as the first operation of a transaction.
Joined: Apr 19, 2011
thanks for your input.
I played around a little with a DeleteEventListener just to test the procedure call. What I've done:
in persistence.xml I set the properties of the persistence-unit:
Implementation of AppDeleteListener:
(I use JBoss Seam. That's the reason for getting the DAO like in line 16)
And the DAO-Method for setting the Userinfo in the Database:
1. With pure JDBC:
2. with Hibernate:
Here you don't need the Session. Could be eliminated from the signature.
So far so good. This works for every Delete.
I will investigate it further regarding every statement (onPersist, onMerge...) and in combination with transactions.
Doing the procedure call every time a Delete, Create or Persist is called was not my original intention. What I want is one call at the beginning of a transaction. Here's what I've done:
In persistence.xml I defined a session-scoped interceptor like this
And in the corresponding Interceptor Class:
Note that the EmptyInterceptor implements all the required Methods of an Interceptor.
The stored procedure has to be called from the same EntityManager as the getTransaction()-call. Otherwise you will get a "java.lang.IllegalStateException: Transaction not active". As I'm working with Seam, it's easy to declare a Seam-Session-Scoped class, which stores the EntityManager and whatever I need. So I store the EntityManager at the beginning of the transaction and get it here, to do the extra call.