Solved

audit_useriid

Posted on 2009-05-14
20
861 Views
Last Modified: 2013-12-18


I a have an after update or delete trigger for auditing several tables.

Audit data is saved into this table.

audit_tbl
(    timestamp    date,
    userid            varchar2(30),
    ip_address   varcahr2(30),
    tname        varchar2(30),
    cname        varchar2(30),
    old            varchar2(2000),
    new            varchar2(2000)
)

Tables are get audited by web application (oracle mod_plsq), client server PB application and TOAD, winsql, etc.
applications are using one DB account and users log in using application accounts.

The question is how to best capture the "userid" and "ip address" of the user doing the update or delete.

Some table do have "last_modified_by" and some do not. Trigger can only access the OLD and NEW values of the ROW.
If the column is not getting updated or table does not have it i cant get it. Also on deletes the "OLD.USERID" would not have the userid that deleted the record.

For ip address, it seems there are different environment variables for WEB and client/server connection. When i checked for the "owa_util.get_cgi_env" in stored procedure, the sql*plus session resutled in error on update.

What would be the best way to accomplish this for web and client server environment?

would setting a context after logging work for both. any code samples.
0
Comment
Question by:sam15
  • 9
  • 9
  • 2
20 Comments
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 24396127
If  the users log in using application accounts ,u can set dbms_application_info.set_client_info(:user_id); immediately after user successfully logs into the application.  The audit trial trigger can capture the application user id by reading the client info by  dbms_application_info.read_client_info procedure.
For getting the IP address in cleint server applications/ tools use

select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
 
 
 
0
 

Author Comment

by:sam15
ID: 24396555
let me check something

user A logs in over the web useing "mike/tiger". after validation you set the client info to"mike". Ok let us say that Mike updated EMP table. Would the trigger have "read_client_info" and get "mike".

What about if user B logs in over the web using "john/secret" and updated EMP table. is the trigger going to see "john" now in the client info.

I have to check that because the WEB is stateless. I do nto know how it can link each call to a procedure with the application user unless you pass that userid into the procedure.
0
 
LVL 11

Accepted Solution

by:
Andytw earned 500 total points
ID: 24397212
Sam15:
You can reference owa_util.get_cgi_env, but you need to check that the the environment has been initialised (i.e. the procedure is being invoked by MOD_PLSQL).  
The following procedure works via the web AND client/server envirnoment - no errors in SQL*Plus.  Obviously replace the HTP.P and DBMS_OUTPUT calls with inserts into your auditing table:
CREATE OR REPLACE PROCEDURE test IS

   lvIPAddress VARCHAR2(32);

   lvUser      VARCHAR2(30);

BEGIN
 

   IF owa.num_cgi_vars > 0 THEN

      lvIPAddress := owa_util.get_cgi_env(param_name => 'REMOTE_ADDR');

   ELSE

      lvIPAddress := SYS_CONTEXT('USERENV',

                                 'IP_ADDRESS');

   END IF;

   lvUser := SYS_CONTEXT('USERENV', 'SESSION_USER');
 

   IF owa.num_cgi_vars > 0 THEN

      htp.p(lvIPAddress);

      htp.p(lvUser);

   ELSE

      dbms_output.put_line(lvIPAddress);

      dbms_output.put_line(lvUser);

   END IF;

END;

/

Open in new window

0
 

Author Comment

by:sam15
ID: 24397824
Great, that solves 1/2 of the issue which is capturing the correct ip address for the client connecting.
How do you get the userid which is the application login id.

If you connect from web and you enter "mike/tiger", and you run the procedure that has "UPDATE  EMP" I want to get "mike" into the audit table.
If you connect using TOAD and run UPDATE EMP, it would be the database account since everyone uses that. I capture the terminal id and osuser and ip address to identify the user.
If you connect using the PB client/server application, it would be "john/secret" because everyone use s application accounts.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24398064
Sam15:  SYS_CONTEXT('USERENV', 'SESSION_USER') will always return the USERID.  In your example, it will either get:
a). MIKE if run the procedure from the web, or
b). JOHN is you run the procedure from TOAD

Is that what you want?  I'd always capture the terminal id, osuser etc. regardless of web/client-server.  Then you'll get the info to identify the people who log in via TOAD,  but you'll know to ignore this for web users (since those fields will always be the same e.g. terminal="", osuser = "SYSTEM").


0
 

Author Comment

by:sam15
ID: 24398369
NO, try it. SESSION_USER is always the DATABASE user account. We only have one. Users use application accounts which we create and store in a custom table (userid,password).

Ther is CLIENT_IDENTIFIER parameter that i have to set I think.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24398431
Ahhh ... right.  If they are application accounts, SESSION_USER won't work - I agree.
Yes, as suggested by shajukg , you can use dbms_application_info which will work.
0
 

Author Comment

by:sam15
ID: 24399056
dbms_application_info stores and reads the values from V$session.

For client server where you have one session id for all transactions it will work.

For web everytime you run a page it is a new session. You have to run it in every page. It will not work if you only set it in the login page because when that same user runs update it is a different session.
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 24401588
I'm not  familiar with much with web development, but i don't think it is essential to open new sessions in database whenever u open a new page .
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24402090
shajukg: since the web is stateless, sessions can *not* be maintained across web pages, so a session has to be opened for each page.  If you're using connection pooling in MOD_PLSQL, the webserver keeps a pool of sessions that can be re-used.  but, as far as the application is concerned it will be a new session.
Sam15:  I've never encountered this issue, since in my environment each application user has a corresponding Oracle user.  I'm assuming that your application must know which application user is logged on? Can you not use that same mechanism to set the application user in the auditing trigger?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:sam15
ID: 24403300
we run in a stateless mode in the web - even though you can run in stateful but that will kill your performance.

Each page will have a different database session id. The web is supposed to do its own session management by passing a custom 'session id" to each page so you know who that user is and whether the session is valid or not. If we have that then we can set a client identifier using dbms_sesssion.set client_info in memory for that sesssion id and that user. So every page that receives the session id can read back the username. I am not sure how would the trigger get the session id since it can only read ROW values.

the issue is that this web application uses "USERID" instead of session id for session management. Is there a way to get/push that  to the "after update or delete trigger" or not if it is not getting saved to the table row?

Is not your environment a nighmare to maintain? you have account management at the database level and application level to keep them in sync. Usually i think it is either DB accounts or Application acounts.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24403612
We run in stateless mode too - I've also heard bad things about resource usage in stateful mode.

>>Each page will have a different database session id. The web is supposed to do its own session >>management by passing a custom 'session id" to each page so you know who that user is and >>whether the session is valid or not
Yes, that's pretty much standard way of handling sessions in a web environment.

>>the issue is that this web application uses "USERID" instead of session id for session management.
I don't think this is an issue, whether you use USERID or a session ID the result is the same .. you can use this to set dbms_session.set_client_info (Personally I would set a context) .  

>>Is there a way to get/push that  to the "after update or delete trigger" or not if it is not getting saved to >>the table row?
The the trigger can read this either using dbms_session.get_client_info, or SYS_CONTEXT(<name_space>, <variable>).

>>Is not your environment a nighmare to maintain?
No, it actually makes it easier to maintain.  Our application can use the Oracle's security, user, permissions, profiles, resource-groups, logging etc. and other management features that are built-in to the database.  The app code is in an application schema, and end users connect to the Oracle users.  Sure, our application stores extra information about *it's* users, so as you say these users have to be kept in sync with the Oracle uesrs.  However, it's a small price to pay for what you get back

0
 

Author Comment

by:sam15
ID: 24404054
you are right i can set acontext using the userid instead of the sessionid but it is kind of useless.

First you have to pass the userid to the trigger code so that you can look it up from the context in memory. so if the trigger know the userid why lookitup from memory if that is what i need to store in the audit table for the user who updated or deleted the record.

My "update" procedure has userid as input parameter but you cant pass variables or parameters to triggers. However i found this article but i have not tried it yet as one way to pass a variable from procedure to the trigger. The other way of course would be to store the "update userid" in the main table and use that in the trigger using "NEW.UPDATE_USERID but I can't see how a delete would get the person userid deleting the record because there is no "NEW.UPDATE_USERID for deletes.

This is the article that show how to pass variable to trigger.

http://birijan.com.np/?q=oracle+plsql+passing+variable+to+trigger
0
 

Author Comment

by:sam15
ID: 24404099
another comment on your environment. You do not seem to be using application accounts for authentication. it is more for tracking addditional information on your database user accounts.
I assume you are using mod_plsql and user keys in the the database userid/password.

I was thinking once of create a db account for every web user but we might end up with thousands of oracle accounts/schemas which is not that easy to manage. also, i do not want to go through DBA for adding/dropping a user so we have more felxibilty with application accounts. I liek the one db account per user more for client./server environments for security.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24404530
I'm sure that the context approach will work for you here.  Or even easier to just use CLIENT_IDENTIFIER.  You set it on every page using DBMS_SESSION.SET_IDENTIFIER(...)

I've done a quick example to demonstrate (the script attached sets up relevant tables, triggers etc. ...
The result is that the trigger can see your application user, via SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER').


SQL> exec web_test(1234567);
 

PL/SQL procedure successfully completed.
 

SQL> select * from audit_tbl;
 

TIMESTAMP USERID                         IP_ADDRESS

--------- ------------------------------ ------------------------------

17-MAY-09 TOMMY
 

SQL>

Open in new window

audit-userid.txt
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24404551
re: environment...
That's correct, we don't use application accounts for authentication; we use database authentication provided by MOD_PLSQL.And yes, it's just to track additional (app-specific) information about our users in the DB.

We do have hundreds of accounts, but it's not a problem.  They have minimal privileges - just enough to run the application, and it's all controlled with roles - so managing additional users is not a problem.  The application is able to drop/create user accounts - clearly you're in trouble if your DBA objected to this.  In which case, creation of all new user accounts would have to go through them - which isn't very flexible!
0
 

Author Comment

by:sam15
ID: 24404638
yes thanks for the example.

I do not have session ids in the page so i will set userid instead. But if 100 users are adding records at same time would oracle keep 100 values in memory. It seems to me the client identifier is one value that gets overwritten by each user.

I think for the web application problem is resolved. DO you think for the client/server I can do change at the oracle side and not powerbuilder code. I want to capture the same application userid.

So would you define an after logon trigger or something that sets a client identifier and clear it after logoff. I am wondering if this screw sometihng up for the web part though.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24405675
CLIENT_IDENTIFIER is a USERENV namespace attribute, so you don't need to worry hat setting it will cause memory issues.  The CLIENT_IDENTIFIER is set by session, so they won't interfere, and get overwritten.  You can see by looking at V$SESSION, Try it:
select username, program, client_identifier
from v$session
where type = 'USER'

For more information on application contexts, client_identifier have a look at the Oracle docs (Oracle Database Security Guide).

I think you should try and do the change at the Oracle side. Yes I would create an AFTER LOGON trigger
 which sets CLIENT_IDENTIFIER if it's invoked by MOD_PLSQL.  Then your audit trigger would be something like:


CREATE OR REPLACE TRIGGER test_tbl 

AFTER UPDATE OR DELETE OR INSERT ON test_tbl

FOR EACH ROW

BEGIN

  IF owa.num_cgi_vars > 0 THEN

      lvIPAddress := owa_util.get_cgi_env(param_name => 'REMOTE_ADDR');

      lvUser := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');

  ELSE

      lvIPAddress := SYS_CONTEXT('USERENV',

                                 'IP_ADDRESS');

      lvUser := SYS_CONTEXT('USERENV', 'SESSION_USER');

  END IF;

  

  -- insert info into your audit table

END; 

Open in new window

0
 

Author Comment

by:sam15
ID: 24407334
i think the SESSION_USER will always be the DBA account name. It wont tell me the application userid for the client/server connection. I think i need CLIENT_IDENTIFIER for both cases: web and client server but the web one will be linked to the web session id(not oracle) and the client server will be tied to oracle session id

do you agree?
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24407693
SESSION_USER will always be the Oracle account name.  I thought that your users for client/server connection connect as different Oracle users, not the same user?

>> I think i need CLIENT_IDENTIFIER for both cases: web and client server but the web one will be linked >>to the web session id(not oracle) and the client server will be tied to oracle session id
Yes I agree with this - if you want to audit something other than the Oracle user account  in the client/server case then set the CLIENT_IDENTIFIER, so the trigger will set it for both cases.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Hello All, In previous article we used Hortonworks sandbox to work with Hadoop. Now, lets think to create own single node Hadoop on Linux. Here we Install and Configure Apache Hadoop on UI based Oracle Linux. I assume, you have VMware installe…
Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now