Re: Oracle User Security via a middle layer

For the moment, I was exploring the use of SYS_CONTEXT('USERENV','OS_USER') and it returns consistently the username of the client who has logged in via the network, and not the user who is connected to the database. I did this by simply asking a couple of people to login via any client, TOAD, PL/SQL Developer or even sqlplus and then execute the following:

SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL; The above works fine in Oracle 10g 10.2.0.4 as well.

I next plan to modify the audit triggers that are used to update created_by_user, modified_by_user in the application tables to use the SYS_CONTEXT function and ask the developers to do the same with some simple inserts/update and/or deletes.

In essence for the application is not using a connection pool - not yet anyway. Yes, and I agree that if we go the connection pool route, I wonder what the above statement will return. I think in that case, I will have to explore all the other options.

If we go the proxy user route (and I probably will not), the proxy user can also be determined by the SYS_CONTEXT function as well as follows:

SELECT SYS_CONTEXT('USERENV','PROXY_USER') FROM DUAL; I think the above statement is valid only if you connect with the following syntax..

connect <proxy_user>[real user]/<proxy user password>

I do realize that we have to contend with roles etc that will have to be set for the proxy user via the ALTER USER command.

But I know that we have to change the app as the number of user's is increasing and we have to go to a genuine mid-tier based connection pool. And I have decided to read up on the various methods that can be used for identifying/authenticating the user.

Thank you for your response.

z1hou1 Received on Sat Oct 09 2010 - 13:10:41 CDT