Home » RDBMS Server » Security » Oracle Audit Design (Oracle 9.2, R2, AIX)
Oracle Audit Design [message #509501] Sat, 28 May 2011 16:38 Go to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
Let us say I want to audit data updates, deletes on existing table EMP_TAB that
has a few hundred thousands of records.

I created a shadow table Emp_tab_audit and added few audit columns

Emp_tab (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);


CREATE TABLE Emp_tab_audit (
seq number
operation varchar2(3),
user varchar2(20),
Timestamp date,
ip_address varchar2(25),
Terminal varchar2(10,
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);

I am mostly interested in UPDATES and DELETES but I decided to add INSERTS to have full history for
each eomplyee in one table (audit schema) instead of querying two tables all the time (production
table and audit table) to see the changes.

I created this AFTER INSERT, UPDATE, DELETE trigger.

I decided to copy the :NEW values for INSERT and UPDATE and :OLD values for DELETE.
see attached.

so when insert happens, the first audit row is created in EMP_TAB_AUDIT.
when update happens, the 2nd new row is created in EMP_TAB_AUDIT.

The problem I am facing is the old records that curently exist. If someone updates an old row I am
copying the :NEW values so I won't have a copy of the :OLD values unless I create 2 ROWS (one for
the old and one for the new).

Do you think I should copy all the hundreds of thousands of records to the AUDIT tables for this to
work. I am hesitant to do that.

ANy better ideas. I am applying this solution to several tables (not just one).
This is also in 9i and i dont flexibility other than using a trigger to track data changes.




*******************************************************************
CREATE OR REPLACE TRIGGER TRG_EMP_AUDIT
 AFTER INSERT OR DELETE OR UPDATE ON EMP_TAB
 FOR EACH ROW DECLARE
 
 v_operation   VARCHAR2(10) := NULL;
 v_user        VARCHAR2(20);
 v_timestamp   Date;
 v_ip_address  VARCHAR2(25),
 v_terminal    VARCHAR2(10);

BEGIN
 
 v_user := USERENV(user);
 v_timestamp := SYSDATE;
 v_ip_address := USERENV(ip_address);
 v_terminal := USERENV(terminal);
 
 IF INSERTING THEN
   v_operation := 'INS';
  ELSIF UPDATING THEN
   v_operation := 'UPD';
  ELSE
   v_operation := 'DEL';
  END IF; 
  
    
  
  IF INSERTING OR UPDATING THEN
   INSERT INTO EMP_TAB_AUDIT (
    seq,
    operation,
    user
    timestamp,
    ip_address,
    terminal,
    empno,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno )
   VALUES (
     audit_seq.nextval,
     v_operation,
     v_user,
     v_timestamp,
     v_ip_address,
     v_terminal,
    :new.empno,
    :new.job,
    :new.mgr,
    :new.hiredate,
    :new.sal,
    :new.comm,
    :new.deptno);

ELSIF DELETING THEN
   INSERT INTO EMP_TAB_AUDIT (
      seq,
      aud_action,
      user
      timestamp,
      ip_address,
      terminal,
      empno,
      job,
      mgr,
      hiredate,
      sal,
      comm,
      deptno )
     VALUES (
       audit_seq.nextval,
       v_operation,
       v_user,
       v_timestamp,
       v_ip_address,
       v_terminal,
      :old.empno,
      :old.job,
      :old.mgr,
      :old.hiredate,
      :old.sal,
      :old.comm,
      :old.deptno);
END IF; 

END;
/
*******************************************************************************
Re: Oracle Audit Design [message #509503 is a reply to message #509501] Sat, 28 May 2011 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>I created this AFTER INSERT, UPDATE, DELETE trigger.
WHY AFTER INSERT?
Row remains unchanged in base table unless & until UPDATE or DELETE occurs.
I contend the Audit table should only contain "OLD" data; since NEW data always exists in base table
Re: Oracle Audit Design [message #509504 is a reply to message #509503] Sat, 28 May 2011 19:07 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
I thought it would be easier to have FULL HISTORY in one table instead of JOINING AUDIT table and MAIN table to see the whole set of changes. But i am considering to not audit INSERT if that is a best practice in auditing.

I can store OLD values in the audit table, but then if user "mike" changed "APPLE" to "ORANGE" in col1, you are showing "APPLE" in the audit ROW which is kind of strange since he really changed it to "ORANGE".

Do you have any links that show some example for audit designs and you can show the FULL HISTORY or updates for users (i.e mike changed "APPLE" to "ORANGE" on 5/28/2011".
Re: Oracle Audit Design [message #509505 is a reply to message #509504] Sat, 28 May 2011 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>I thought it would be easier to have FULL HISTORY in one table
Such an implementation means the base table is 100% redundant; which violates Third Normal Form.

You are free to dig any hole you want.
Re: Oracle Audit Design [message #509507 is a reply to message #509505] Sat, 28 May 2011 19:32 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
yes, but this is a log table more like desining a reporting database or data warehouse where you do not follow normal forms. Your goal is performace for read only transactions.

as i said i can take the INSERT but can you show me an example of this method to compute reports of what was updated, who did the update ,or looking at the record full history.
Re: Oracle Audit Design [message #509512 is a reply to message #509507] Sun, 29 May 2011 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you can use Fine-Grained Auditing

Regards
Michel

Re: Oracle Audit Design [message #509539 is a reply to message #509512] Sun, 29 May 2011 09:30 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
I looked in to that. FGA in 9i (and i think 11g) will not give you the old and new column values of a row after an update occurs.
Re: Oracle Audit Design [message #509541 is a reply to message #509539] Sun, 29 May 2011 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what you actually need.
If it is audit then this is not the value you need but the statement, who does it and when.
If it is history, this is an entirely different problem (and in this case you should upgrade to a newer version which can give you far more options).

Regards
Michel
Re: Oracle Audit Design [message #509544 is a reply to message #509541] Sun, 29 May 2011 12:57 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
I explained what i need. I need to know the dat avalues that a user updated. You can call it history if you wish but it is considered auditing too.

for example, I need to know that "John updated T1 and changed the COL1 value from "Apple" to "Orange" on 5/20/2011".

it is not under my control to upgrade. It is not that easy to go through red tape for this.
Re: Oracle Audit Design [message #509547 is a reply to message #509544] Sun, 29 May 2011 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you say "I need" who is "I"? what is his/her role/job in the entreprise?
What is the complete requirements, not the way you want to implement it the functional need.

Regards
Michel
Re: Oracle Audit Design [message #509548 is a reply to message #509547] Sun, 29 May 2011 14:09 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
I=customer

i am not sure i can explain it better than this.

Customer wants to know all updates/deletes to an employee data. he wants to see full history since record inception.



Re: Oracle Audit Design [message #509551 is a reply to message #509548] Sun, 29 May 2011 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who "customer"? end user? Is this part of business need?

Regards
Michel

[Updated on: Sun, 29 May 2011 14:53]

Report message to a moderator

Re: Oracle Audit Design [message #509556 is a reply to message #509551] Sun, 29 May 2011 20:16 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
please provide details about User Interface & how application operates against the DB.
via what software (Java, .NET, PHP, etc.) does end user interact with the V9 Oracle DB.
Does every end user have their own Oracle username?
Re: Oracle Audit Design [message #509624 is a reply to message #509556] Mon, 30 May 2011 11:25 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
The application is all written in pl/sql stored procedures.

it uses application accounts (no database accounts). we only have one db account that everyone uses.
Re: Oracle Audit Design [message #509625 is a reply to message #509624] Mon, 30 May 2011 11:27 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
I forgot to mention users use mod_plsql to invoke the pl/sql SPs via browsers.
Re: Oracle Audit Design [message #509626 is a reply to message #509625] Mon, 30 May 2011 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>we only have one db account that everyone uses.
so how will you determine which end user made any specific change to the EMP table?
SQL> @a.sql
SQL> SELECT USERENV(user) from dual;
SELECT USERENV(user) from dual
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter


SQL> SELECT USERENV(ip_address) from dual;
SELECT USERENV(ip_address) from dual
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter


SQL> SELECT USERENV(terminal) from dual;
SELECT USERENV(terminal) from dual
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter
Re: Oracle Audit Design [message #509627 is a reply to message #509626] Mon, 30 May 2011 11:41 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
that is not a problem. do not worry about it.

i capture the environment variables OS USER ,terminal, application, the application user id, etc.

We have users that login directly via TOAD and SQl*plus. We do have client app that uses power builder.

but my whole question is more of a trigger question that will work with any client regardless of teh language used.

trigger auditing works for all clients.
Re: Oracle Audit Design [message #509628 is a reply to message #509627] Mon, 30 May 2011 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>trigger auditing works for all clients.
Congratulations!
Re: Oracle Audit Design [message #509629 is a reply to message #509627] Mon, 30 May 2011 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the end you only want your trigger, so use you trigger.

Regards
Michel
Re: Oracle Audit Design [message #509652 is a reply to message #509629] Mon, 30 May 2011 17:50 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
wow, this turns to be the most USELESS forum i visited.

sorry guys, but you are totally WORTHLESS.

I recommend you take yourselves off this site immediately as I do not think you read my question.

what a total waste of time!

Re: Oracle Audit Design [message #509657 is a reply to message #509652] Tue, 31 May 2011 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you didn't want to explain all the points related to your issue.
We took (and wasted) time to try to get a complete description of what you have to provide the more appropriate answer but you kept things by your side.
Nothing in your code shows that you have user in 3-tier, it implies you only work in 2-tier otherwise the code is not correct. Now you add this point and when we make you notice the code does not work, just say you are able to handle that and your trigger works for all cases. This is not what we see, so if you don't post correct information we can't give you appropriate answer.
You have to learn how to correctly post in forum and in first place you should not insult those that have spent time to try to help you.

Regards
Michel
Re: Oracle Audit Design [message #509709 is a reply to message #509657] Tue, 31 May 2011 06:33 Go to previous messageGo to next message
sam10
Messages: 13
Registered: May 2011
Location: Los Angeles
Junior Member
oh, geez, 3 tier versus 2 tier. What does it matter when you have a database trigger that fires after SQL is inserted/update or deleted. I guess i have been very lucky so far that it works the same way with both application right now.

As i said you both showed a high level of ignorance with your posts and you have not really understood my english question.

If you want to interpret this as insult, instead of listening to someone telling the truth, then you should be fired from posting on this thread and stop wasting people's time as you did to me.
Re: Oracle Audit Design [message #509712 is a reply to message #509709] Tue, 31 May 2011 06:46 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
3 tier versus 2 tier. What does it matter when you have a database trigger that fires after SQL is inserted/update or deleted

2tier: USERENV context contains user information
3tier: USERENV context contains application server information and does not allow you to get information about end user.

Quote:
As i said you both showed a high level of ignorance with your posts and you have not really understood my english question.

I think your level of knowledge (see above) does not allow you to understand the purpose of our questions.

Quote:
stop wasting people's time as you did to me.

I think YOU wasted our time.

Regards
Michel

Previous Topic: To find out whic row is locked by the particular user (2 Merged)
Next Topic: password of users in 11g
Goto Forum:
  


Current Time: Sun Aug 31 00:00:57 CDT 2014

Total time taken to generate the page: 0.08555 seconds