I've seen only one other question like this asked and it wasn't answered very well so I'll ask it again...
We need to use our database (in this case Oracle) for authentication and storing of privileges. We want to use the database username/password as the application username password and any privileges as the application privileges (if I don't have permission to insert into a table, the button/menu that accesses the session/entity combination for this is greyed so I can't use it). This means I need to set up a database pool that can accept user names and passwords of individual users and NOT of one "super user"...
Having a single username\password and EJB ACL's (a la BEA)for the pool is not secure enough. We need to ensure that no matter how the user accesses the data (through our client-server EJB app or directly through a sql command line like SQLPlus) there are some things they cannot do because of the role they have been assigned.
One posibility I thought of was having a single user/password user created that only has connect privileges and then issuing a "connect newuser\newpassword" sql command to connect as the user I want...I'm just not sure if one can do this through a JDBC datasource. Has anyone done this?
I found my answer... For those who are interested, my last bit was essentially right.
You can create your pool as "jdbc\mypool" (for example) and use jndi to get a datasource object from it in the usual way. You can create your pool with a default username\password combo for a user with connect privileges only. Then, when you call on the datasource to get the connection, use the line:
This allows you to use the connection with the rpivs of the user you want, not the default user for the connection, allowing you to use your database security, authentication and roles at the application level. I have tested this this morning and it seems to work -
user 'mike' has select privs on a group of tables but not insert. When connected as above, the user can 'findByPrimaryKey()' but cannot 'create()' ejb objects, just as expected (we use BMP...I'm not sure how this works with CMP)
Hope this helps others...