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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3319
  • Last Modified:

Oracle Database Transaction History

Does oracle have any built in security feature that keeps track of who added/modified/deledted from the DB and where they did it?

2 Solutions
Mark GeerlingsDatabase AdministratorCommented:

But, that probably isn't the answer you were hoping for.  There are ways to capture this information with an Oracle database but you need to do some work.

One option to capture part of this information is the "auditing" option that Oracle supports.  You can turn auditing on but that only captures part of what you are looking for.  That captures the username, timestamp, table name and type of transaction (insert/update/delete) but not the actual data values or number of records affected.  Auditing also captures lots of additional information that you may not be interested in, but you need to manage, since it can fill up disk in a hurry.

Another option is to write one or more triggers for each table in the database.  They can capture any data you like when a transaction occurs: username, timestamp, table name, type of transaction, "before" values, "after" values, etc. and record this in audit or log tables you set up, or in ASCII log files (if you use utl_file).
superdesioAuthor Commented:
actully the oracale auditing sounds like what i am looking for. Do you know of a good resource I can read more information on this?  Also does it capture what record in the table was updated/modified/added?

As mentioned by markgeer, Oracle's default auditing feature does not captures the data images in a table and it just logs the transaction details and operations which modifies the metadata.

If you need to audit the data values in a table you need to write a trigger on each table , which can be automated . I have created audit package which contains 3 tables and set of procedures which accompolish the data auditing. If you need it let me know.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Check this out:

This is for oracle 8.1.5

You can find documentation for all versions of Oracle if you do a search on www.google.com or otn.oracle.com.  

This documentation is also included in the documentation that comes with your software.  

logminer, are you in archive log mode?

logminer can let you peek into the arch logs as far back as you have em and you can see who did what.

8i lets you see dml
9i has included support to see ddl as well

be warned, you might be wading through a tonne of data but it's all there!
Mark GeerlingsDatabase AdministratorCommented:
Yes, LogMiner has the DML statements that would need to be executed to repeat (and/or undo) any insert, update or delete statement.  But it is not fast or troublefree, at least in Oracle8i.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
split between markgeer and psvel
Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now