Solved

Oracle Database Transaction History

Posted on 2002-07-15
7
2,978 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 35

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 35

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.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

828 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