Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2894
  • Last Modified:

How to trace the activity on a single table in oracle

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
fpkeegan
Asked:
fpkeegan
  • 4
  • 3
  • 3
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
>>You would not be able to audit select with a trigger.

Excellent point!
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
johnsoneSenior Oracle DBACommented:
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
 
AkenathonCommented:
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
 
AkenathonCommented:
@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
 
fpkeeganAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
AkenathonCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
AkenathonCommented:
@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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now