Solved

How to trace the activity on a single table in oracle

Posted on 2010-11-17
11
1,939 Views
Last Modified: 2012-05-10
I would like to trace all the activity (Selects, updates,inserts, deletes) on a single table in oracle. Is it possible if so How do you configuure it.  The trace needs to be on a single table. It can not be a large scale trace as the server has a high disk and CPU load now.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
 
0
Comment
Question by:fpkeegan
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
For a single table I might go with a trigger.

The other way is oracle auditing:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/audit.htm
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
You would not be able to audit select with a trigger.  If you need to audit select, then you need to take the hit of Oracle auditing.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>You would not be able to audit select with a trigger.

Excellent point!
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Just wanted to point that out.  If select is not an issue, then a trigger is the way to go.  Especially with one table.
0
 
LVL 11

Expert Comment

by:Akenathon
Comment Utility
Apart from AUDIT you should take a look at FGA (Fine Grained Auditing): http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_fga.htm#i1002216

It can audit SELECTS and everything else. The big plus over plain AUDIT is that you can have much more control over who gets audited, what exact SQLs get audited, etc. You can even choose to audit operations only when they affect the specific columns you care about.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Accepted Solution

by:
Akenathon earned 500 total points
Comment Utility
@johnsone: AUDIT and triggers are very different. Even if we do not consider the fact that triggers don't work with SELECTs, AUDIT is faster because it's hardcoded into the Oracle executable.

Triggers need someone to code and mantain them, and could be disabled (which can only be detected by using AUDIT anyway), so they are not as "secure". On the other hand, they provide much better control over what gets traced at the expense of PL/SQL execution. Note that even if you code a trigger which does nothing, you still have the overhead of switching between SQL and PL/SQL, which will be noticeable in a bulk operation if the trigger is FOR EACH ROW. And that's another difference: AUDIT won't let you do anything FOR EACH ROW. You only get to choose BY ACCESS (much the same as a trigger which is not FOR EACH ROW) or BY SESSION (an option the triggers don't have, which can further improve AUDIT performance over triggers if you are OK with session granularity).
0
 

Author Comment

by:fpkeegan
Comment Utility
What is the set up for fine grain auditing on a single table. All I want to look at is the statements used agains the table Select, update, inserts, delete.  Does is work on oracle 9.2. The link is for 10.2.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
FGA was introduced in 9i:

http://www.oracle.com/technetwork/database/security/index-083815.html

That link also has an Oracle-By-Example link (although for 10g but should be similar if not the same).

The only thing I cannot locate is if FGA is part of Advanced Security or free to use with a standard license.
0
 
LVL 11

Expert Comment

by:Akenathon
Comment Utility
Yes, it does work for 9i: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_fga2.htm#1002217

Here are some examples to get you going:

http://www.oracle-base.com/articles/9i/SecurityEnhancements9i.php#FineGrainedAuditing

http://www.hawkeyetechnology.com/DATABASE%20AUDITING.pdf (look around slide 35)

And... if you need to capture the SQL statement, FGA is your only option. AUDIT won't do it. The trigger won't get fired for queries, so you NEED to understand and use FGA :-)
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Understood, but what are they trying to accomplish with the audit records?  Can even fine grained auditing tell me who changed a specific record, when they changed it and what the history of changes to a record are?

Auditing and fine grained auditing have their own overhead associated with them.  When I have auditing turned on, Oracle has to decide whether the action that just occurred requires an audit record to be written.  That is not free.  On the other hand a trigger knows when it is supposed to do its job.  When you are talking about 1 table out of possibly thousands, is it worth the overhead of auditing for just one table?  Maybe yes, maybe no.

Triggers do require someone to write them, however these type of auditing triggers are fairly simple and if done correctly require almost no maintenance.  Yes, it could be disabled, however your orgainzation should have the controls in place to prevent that type of thing from happening.  One can also argue that auditing records could be deleted and that presents just as much of a security hole as disabling a trigger.

0
 
LVL 11

Expert Comment

by:Akenathon
Comment Utility
@johnsone: Yes, FGA can tell you who did what and when, and what was the SQL. If the author had asked for a history, still I would have to recommend FGA because what he did say is that he needed SELECTs audited (FGA can run a PL/SQL block of your choice just like a trigger, so whatever a trigger does, FGA also can do).

AUDIT is not an overhead if the AUDITed operations are never executed. You are probably already paying for the negligible overhead of having audit_trail != NONE (if your data is any worth and you have audit_trail=NONE you are not even auditing connections, so you have much bigger problems than the "IF" overhead).

I obviously agree that you can cover your tracks if you have enough privileges... AUDIT and FGA are designed with security in mind so they are a bit harder to circumvent, but of course it can be done.

Please note: I'm not saying this to argue that one solution is intrinsecally "better" than the other... I do use triggers for auditing purposes, I just see that they are not even an option for the author's problem at hand (because of the queries), but all these alternatives and some others not even mentioned have their place in Oracle's security/forensics.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle 10G 5 45
Stored proc to create user table via file import 9 52
Pl/SQL Query 31 61
File generation using utl_file 4 28
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 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

6 Experts available now in Live!

Get 1:1 Help Now