Find who and when changed a specific value in the database

Question

We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake.

we want to find out who and when has done this?

could someone give some tips?

thanks in advanced.

Answer 1

If you didn't enable auditing in your database, you usually cannot spot who and when queried and/or modified the database, at least as easily as with auditing enabled.

You can use various types of auditing for these tasks:

1. Standard auditing
2. Fine-Grained Auditing (FGA)
3. Triggers

With standard auditing, you can audit object and system privileges, such as SELECT * FROM HR.EMLOYEES and CREATE ROLE. Audit records can be written in database (the default) or in the files outside the database. You enable auditing with AUDIT statement, for example, AUDIT SELECT ON HR.EMPLOYEES, and disable it with NOAUDIT statement, for example, NOAUDIT CREATE ROLE. Standard auditing allows you to capture the user name which invokes the statement, the timestamp, the text of the executed SQL, and bind variables. The standard auditing records can be queried from the DBA_AUDIT_TRAIL view if auditing is configured to store them in database (which is default).

With FGA, you can audit specific columns and rows of the table based on audit condition, you can audit actions in specific time intervals (say, from 5 PM to 8 AM), you can even audit actions from specific IP addresses. You implement FGA with DBMS_FGA PL/SQL package. For example, you can audit queries and updates on the column SALARY of the table HR.EMPLOYEES with this FGA policy:

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'chk_hr_employees_salary',
   audit_column       => 'SALARY', 
   enable             =>  TRUE,
   statement_types    => 'SELECT, UPDATE',
   audit_trail        =>  DBMS_FGA.DB);
END;
/

The FGA audit records can then be queried from the DBA_FGA_AUDIT_TRAIL view if policy is configured to store them in database (audit_trail parameter in the code above).

With triggers, you can capture the previous and new values for the interesting tables. However auditing implemented with triggers is superseded by FGA. The audit records are inserted in the tables specifically created by you. As far as table auditing is concerned, you can use BEFORE STATEMENT and AFTER STATEMENT triggers to audit statements executing on the tables, or BEFORE ROW and AFTER ROW to audit the previous and new values for the columns.

The other facility I would give a shot is LogMiner. It allows you to analyze the online and archived redo logs and reconstruct the exact DML and DDL that were executed in the database. In Database Control (the web interface to control the database), take to the Availability tab, then click View and Manage Transactions). Here, you can specify the time range and objects you're interested in, and LogMiner will provide you the transactions list based on the parameters you specified. If you cannot determine the actual user, I would try to correlate the time of transaction with AUDIT SESSION records which are usually collected as part of mandatory auditing and can be retrieved from DBA_AUDIT_TRAIL view.