Re: Converting from single user w/pool to multiple users

Guy Rouillier wrote:

>Do you really want to try to administer
>1000s of database user accounts in addition to whereever you maintain
>these same accounts for non-DB authentication. This assumes, of course,
>that if you are interested in accountability at all that you
>authenticate somewhere. And I certainly wouldn't want to sacrifice
>memory that could be put to good use processing database requests to
>holding several thousand idle database connections
>
>
Actually, we dont think that the management of the database accounts
will be an issue. Our user administration system will create the users
in postgres at the same time it creates the appropriate 'profile' in our
application databases. Any changes (such as disabling the user) is also
propogated to both places via stored procedure(function) that modifies
both as appropriate.

>Instead, again assuming you authenticate users, you can propogate that
>security context to JBoss. Then you can secure the EJB or MBean method
>that obtains the database connection from the pool (and presumably does
>something useful) to respect the security context. If you want to
>audit, you have the security context information, so you can extract the
>authentication credentials from there to write to an audit table (or
>even include in every row you write to the database.)
>
>
The issue is really propogating the authenticaion credentials to the
database itself.....it's our ON INSERT/ON UPDATE/ON DELETE triggers that
are doing the auditing and they need the user ID to accurately log
changes. In lieu of any other per-connection persistant data option,
this seems like the best bet.