Solved

How to trace the activity on a single table in oracle

Posted on 2010-11-17
11
2,043 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)
ID: 34157793
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
ID: 34157843
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)
ID: 34157857
>>You would not be able to audit select with a trigger.

Excellent point!
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34157879
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
ID: 34157913
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
ID: 34158003
@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
ID: 34158045
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)
ID: 34158128
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
ID: 34158146
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
ID: 34158342
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
ID: 34158690
@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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

22 Experts available now in Live!

Get 1:1 Help Now