If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: protecting dbms_session.set_identifier

We have a client application using a connection pool that is unable to use proxy authentication. For the purposes of auditing we would like to record the actual username (or other identifying data) for the session in the client_identifier with dbms_session.set_identifier (they are all connecting to the same database userid).

Seeing as this is for auditing purposes we need a way to ensure that any connected user can't just execute dbms_session.set_identifier with any username he wants. Are there any foreseen problems revoking all access to dbms_session and only granting it through a separate wrapper package that we control? What steps should we take to ensure it's trusted?

Why do application users have Oracle client s/w plus actual schema names & passwords that would allow them to actually log directly into the database?

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

They don't. It's a COTS app with a JDBC pool that we can't do much to modify (like adding proxy auth), that's connecting to a single application database user/schema.

But forget all that for now. Just say I want to have a logon trigger to set the client_identifier to a person's OS_USER, but to have it secure so I can't set my client_identifier to your OS_USER and pretend to be you. How do you lock that down correctly?