Audit trail set by user -- oracle 9.2.0.6
Baqir Hussain, November 16, 2005 - 7:01 pm UTC
We have 2-node RAC system on Linux and one username_1/password is being used by the application. There is adhoc requests come from the sales/finance to update/delete/insert records into the production. We have been using the same application username_1/password to do it.
Now Sabanes_Oxley requirement is to create another username_2/password to do DML activity on production and these DML activities by this user must be audited (recorded).
set the following in QA to test and verify
audit_trail=OS and audit_file_dest='/test_dir/audit'
and bounce the QA RAC system.
Now there are two users in the QA system
1.user_1/password_1 --> use for application
2.user_2/password_2 --> create for audit trail
I would like to set audit trail ONLY by this user_2/password_2 to record audit information not by the user_1/passwprd_1.
I tried the following option on user_2/passwordd_2:
audit session by user_2.
It work fine for this user. But when I login as user_1/password_1 it does record audit trail for this user (this what I do not want).
1. Please advise on this issue?
Audit trail records written to an operating system audit trail contains encoded information
SESSIONID: "8644153" ENTRYID: "8" STATEMENT: "8" USERID: "BDR" USERHOST: "1" TERMINAL: "pts/0" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "BDR" OBJ$NAME: "MESSAGE" OS$USERID: "oracle"
2. Is there any tool available to extract this information? OR you have some function/procedure written in this regard ?
Thanks in advance for your help.
November 16, 2005 - 7:42 pm UTC
I doubt SOX says "you cannot use this obscure username, but must use another obscure username to update data". What has this accomplished? nothing - you still don't know WHO did the update (that is what SOX wants..)
1) provide test case, step by step, create user1, create user2, issue audit commands and show me what you mean.
2) in 10gr2, the audit trail can be written to the OS in XML (meaning almost anything can parse it). until then, you are on your own to mine this information.
audit_trail=OS -- 9206
Baqir Hussain, November 17, 2005 - 11:25 am UTC
I will be the ONLY one having this exclusive username_2/password to do adhoc DML.
create user username_2 identified by password_1
default tablespace users
temporary tablespace temp;
grant dba to username_2;
without passing out any command (audit session by unsername_2) oracle still generate audit log for even login.
I do not know what I am missing.
Please advise
Thanks
audit_trail=OS -- 9206
Baqir Hussain, November 17, 2005 - 12:13 pm UTC
I figured out the problem.
I did execute commands in the following sequence
1. audit select table, insert table, delete table, update table by access;
2. noaudit all; --> should take care of the above command
3. audit session by username_1;
Actually "noaudit all;" did not clear out the records shown by the following commands:
select * from dba_stmt_audit_opts;
noaudit select table, insert table, delete table, update table; --> did clear out the old records and the things working great as expected.
Could you please help me out what is the best way to mine out records from the OS audit trail file. Thanks
November 18, 2005 - 9:36 am UTC
you'll have to write something that parses the OS files and makes sense of them.
Oracle and SOX
Tony, May 16, 2006 - 11:10 am UTC
Tom, thanks a lot for your valueable comments,
My question is regarding SOX requirements,
In our schema we have about 30 packages and how can we set up the packages to pass a client identifier to the database with all SQL calls.
Regards
Tony
May 16, 2006 - 11:21 am UTC
eh? Not sure what you mean.
Your application would need to make a single call to dbms_session to set the client id, then auditing will record that value.
you don't "set up packages" to do this, something the application does.