Can an Oracle database trigger know what user is actually logged into the system?

I hope I understand this enough to pose an intelligent question.

- I'm working on a web-based application written in J2EE which interfaces to an Oracle database.
- Users log into the system using their network password.
- A table in the database contains a "created by" column which is supposed to contain the user name of the person who created the row.
- When a form writes to the table, a trigger is fired which automatically includes the created_by and created_time columns using the system variable USER.

The system variable USER contains the user name of the account used to connect to the database (which is the same for everybody) and NOT the name of the user who is actually logged into the application.

I realize that J2EE can pass the username of the person actually logged into the system, which the trigger can then use to insert this information into the database, but is there another way for the (in this case) Oracle database trigger  to obtain the user name of the person actually logged in?

(If the username is not part of the data used to perform the insert, can my trigger somehow obtain it?)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
Good question -- my solution would be to subshell out to the host and capture the username into a variable.  Someone else may have a more elegant appoach, but I hope that helps.
caldernetAuthor Commented:
Not that it really changes my question but the paragraph beginning with "I realize that J2EE can pass the username..."

Should read:

I realize that J2EE can write the username of the person actually logged into the system into the table so the trigger doesn't have to worry about it, but is there another way for the (in this case) Oracle database trigger  to obtain the user name of the person actually logged in?

(Thanks DVZ....I'm not a J2EE programmer, so I personally don't understand your answer - But I intend on discussing all possible solutions with others.)
Also try looking at v$session os_user

or pull it with sys_context

select sys_context('userenv','os_user') from dual
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

The way I look at it is this:
- your app knows the usernames A1, A2, ... An and connects to Oracle via a general username O1.
- Oracle only "sees" O1 and has no automatic knowledge of A(i).

I would suggest setting a variable with the app usernames. Perhaps even a contextual value?
while sys_context is easier, if you do want to pull osuser from v$session, here's an easy way to do it for the current session.  Note, the trigger owner might not have permission to query v$session, so, another argument for the sys_context method.

select osuser from v$session s, v$mystat m
where m.sid=s.sid
and rownum=1
Instead of Oracle trigger your application has to call an Oracle stored procedure that as IN parameters will get the name of the application user.
The procedure will have the same functionality like the trigger you are looking for, except the fact it will store the application user and details in the same table.
Something like this:

CREATE OR REPLACE PROCEDURE  register_record (p_apl_user IN VARCHAR2)
   INSERT INTO reg_table (user, when) VALUES (p_apl_user, SYSDATE);

The application has to call it so:

:1 := 'user27964';
execute register_record (:1);
That's essentially what the asker stated in 22894877, except wrapping the insert inside a stored procedure.

Not that it's wrong in anyway,  but I think the askwer was looking for someway that oracle could detect the user on its own with having to require the app to send it with additional handshaking.

caldernetAuthor Commented:
11.06.2008 at 09:23AM EST, ID: 22895180 by sdstuber does not work unfortunately.
select osuser from v$session s, v$mystat m
where m.sid=s.sid
and rownum=1

While this pulls up the user name in an SQL Developer window all nice and pretty, when running on the server in a trigger, I get a "table not found" message. I assume this is because when I'm executing it directly in my windows environment, it knows who I am, but once on the server, there's no longer a connection to my Windows Login. (Too bad, this would have been cool.)

I'll keep going through the responses. Thank you for all of them.
"table not found"  is a privileges or synonym problem like I mentioned,  so use the sys_context method then.  It's smaller code and no special permissions required
Also keep in mind that the application username and the os username may be different.
yes, but if the os user is what he is looking for (and it seems like maybe it is)

"Users log into the system using their network password."

My suggestion still might not work if the os user is also a system account on the web server then it won't be any help.  But,  the asker seems to already be aware of how to keep track of the application user manually, so that left exploring if Oracle has any built in ways to detect the user and v$session/sys_context are, I belive, the only options left in that respect

caldernetAuthor Commented:
Yes, I believe SDSTUBER understands correctly.
Please log into Windows with a username and password.
When they run the application, they are prompted to enter the same username and password.
If the password they use to log into their workstation changes, then the application knows about this as well and will require the new password.

I'm still testing...thanks again.
caldernetAuthor Commented:
11.06.2008 at 09:11AM EST, ID: 22895052 by sdstuber
select sys_context('userenv','os_user') from dual

I'm afraid this doesn't work for me either. There's no error but NULL is returned by the select statement in the query. A value of "WAS NULL" was put into my column by the trigger below.

I think what I want to do may be impossible, although it may be possible for least the sys_context solution to work if the environment was set up differently.

I'm afraid I don't really understand schwertner's "register record" solution (22895718). It seems that the user would need to be stored in a table by the J2EE application. This data would then need to be referenced by my trigger. If this is true, then my trigger still wouldn't know what to fetch.

_____ Trigger I just tried to use___________________________________________

  user_logged_in test_table.test_created_by%TYPE;


    select sys_context('userenv','os_user') into user_logged_in from dual;
    :new.TEST_CREATED_BY := user_logged_in;

  IF user_logged_in IS NULL THEN

    :new.TEST_CREATED_BY := 'ERROR';

sorry, your app will have to do it then.  If you like the idea of contexts, you can create your own context namespace then reference it

    :new.TEST_CREATED_BY := sys_context('your_namespace_here','your_app_user');
    :new.TEST_CREATED_BY := user_logged_in;

or you could simply put it in a package variable and either reference the variable directly or via a function (I would go for the latter)

     :new.TEST_CREATED_BY := your_package.get_your_user;
    :new.TEST_CREATED_BY := user_logged_in;

note, both of these assume your session is constant.  If you have connection pooling and you won't know which session you'll be in from one call to the next then these might not work, at least not without some handshaking.

writing to a table does require some handshaking too as you already noted to keep track of which row you need to be querying
caldernetAuthor Commented:
Oooo. I think maybe for now I'll try to avoid this problem then, and if need be, we'll force the Java programmers to pass the current username when writing to the table.

So - do I still give out points?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
up to you.  

if none of the above apply you and not helpful at all you can request a delete.

or, if you feel the question is worth something then accept your own answer.

If something was helpful even if not directly applicable right now then you can still assign points, including doing a split with your own answer for a partial refund.

I think this was a worthwhile discussion so I would recommend against the delete, how you divide up the points is your choice though.
>>select sys_context('userenv','os_user') from dual

I'm afraid this doesn't work for me either. There's no error but NULL is returned by the select statement in the query. A value of "WAS NULL" was put into my column by the trigger below.


This is strange. How are you executing this test?
caldernetAuthor Commented:
You can see what I did at the comment above at

caldernetAuthor Commented:
....The trigger was added to the table and then the J2EE application was run. It creates the row...the trigger is fired and I don't have the windows user name of who made the change to put into the database automatically.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.