Solved

Oracle Database Transaction History

Posted on 2002-07-15
7
2,819 Views
Last Modified: 2008-03-03
Does oracle have any built in security feature that keeps track of who added/modified/deledted from the DB and where they did it?

0
Comment
Question by:superdesio
7 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 50 total points
ID: 7155324
No.

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).
0
 

Author Comment

by:superdesio
ID: 7155352
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?

0
 
LVL 1

Assisted Solution

by:psvel
psvel earned 50 total points
ID: 7155367
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.
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.

 

Expert Comment

by:bozkirli
ID: 7156602
Check this out:
http://www.csee.umbc.edu/help/oracle8/server.815/a67772/audit.htm

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.  

Sadik
0
 
LVL 2

Expert Comment

by:stmontgo
ID: 7160596
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!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7163329
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.
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9392212
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 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

15 Experts available now in Live!

Get 1:1 Help Now