• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

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?)
0
caldernet
Asked:
caldernet
  • 8
  • 7
  • 3
  • +2
6 Solutions
 
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.
0
 
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.)
0
 
sdstuberCommented:
Also try looking at v$session os_user

or pull it with sys_context


select sys_context('userenv','os_user') from dual
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
gatorvipCommented:
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?
0
 
sdstuberCommented:
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
0
 
schwertnerCommented:
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.
0
 
schwertnerCommented:
Something like this:

CREATE OR REPLACE PROCEDURE  register_record (p_apl_user IN VARCHAR2)
IS
BEGIN
   INSERT INTO reg_table (user, when) VALUES (p_apl_user, SYSDATE);
END:
/

The application has to call it so:

:1 := 'user27964';
execute register_record (:1);
0
 
sdstuberCommented:
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.

0
 
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.
0
 
sdstuberCommented:
"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
0
 
gatorvipCommented:
Also keep in mind that the application username and the os username may be different.
0
 
sdstuberCommented:
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

0
 
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.
0
 
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___________________________________________
CREATE OR REPLACE TRIGGER "DB"."PREINS_TEST_TABLE"
BEFORE INSERT ON DB.TEST_TABLE
FOR EACH ROW

DECLARE
  user_logged_in test_table.test_created_by%TYPE;

BEGIN

  IF :new.TEST_CREATED_BY IS NULL THEN
    select sys_context('userenv','os_user') into user_logged_in from dual;
    :new.TEST_CREATED_BY := user_logged_in;
  END IF;

  IF user_logged_in IS NULL THEN
    :new.TEST_CREATED_BY := 'WAS NULL';
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    :new.TEST_CREATED_BY := 'ERROR';

END;
0
 
sdstuberCommented:
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


 IF :new.TEST_CREATED_BY IS NULL THEN
    :new.TEST_CREATED_BY := sys_context('your_namespace_here','your_app_user');
    :new.TEST_CREATED_BY := user_logged_in;
  END IF;

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)

 IF :new.TEST_CREATED_BY IS NULL THEN
     :new.TEST_CREATED_BY := your_package.get_your_user;
    :new.TEST_CREATED_BY := user_logged_in;
  END IF;

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.



0
 
sdstuberCommented:
writing to a table does require some handshaking too as you already noted to keep track of which row you need to be querying
0
 
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?
0
 
sdstuberCommented:
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.
0
 
gatorvipCommented:
>>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?
0
 
caldernetAuthor Commented:
You can see what I did at the comment above at
     http://www.experts-exchange.com/Database/Oracle/10.x/Q_23881393.html#a22896878

Thanks.
0
 
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now