Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Database Transaction History

Posted on 2002-07-15
7
Medium Priority
?
3,262 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 200 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 200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

916 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