CODEWITHSUNDEEP
databaseoracle-databasestored-proceduresplsqloracle11g
Krishnendu
Krishnendu

Reputation: 125

is there any way to audit any action performed on the database ?

I want to audit any modification to any db objects and the information will be stored in a table. The audit information must hold the IP/ log in user name (desktop)/scheduler from which the data/object was modified.

I tried Google on this but i got few DB objects which can help in this like v$session,dba_audit_objects.

Any elaborate approach on this from any one ?

This will be used in the team in order to track the source where from data is modified.

Upvotes: 1

Views: 69

Answers (3)

Vinish Kapoor
Vinish Kapoor

Reputation: 679

You can write the Database and Schema level triggers on DDL statements with SYS_Context('USERENV', 'PARAM') function to audit the database changes. The following are the DDL trigger options:

BEFORE / AFTER ALTER
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND

Upvotes: 1

ErikL
ErikL

Reputation: 2041

avk's answer is a good option if you only want to use Oracle standard functionality.

Another option is using the build in audit features, have a look at http://oracle-base.com/articles/10g/auditing-10gr2.php for some examples.

Upvotes: 2

avk
avk

Reputation: 871

One way is by creating audit tables for each and every table in your system. Then write triggers on all columns in each table and have these triggers store old and new value on a row in the audittable. (delete, update, insert).

Upvotes: 1

Related Questions