Clever Geek Handbook

Oracle delete audit: how to check what has been deleted?

Firstly, I am completely new to the history of oracle tracking. So, I have to check the actions of a specific user.

My problem is when I check dba_fga_audit_trail, I see in the sql_text column that the user has executed the delete statement, but I don't know what he deleted. This is what is written in a cell from the sql_text column:

DELETE FROM USR WHERE USR_ID = :B

Could you please help me find a way to keep an audit trail which row was deleted from a particular table?

+3


source to share


2 answers


You need to create a table for storing deleted data that will contain one row to delete. Then create a trigger on the USR table that will fire after the drop. This trigger will select the data you want to audit from the 'dummy': OLD table and insert into the audit table.



0


source


My problem is when I check dba_fga_audit_trail, I see in the sql_text column that the user has executed the delete statement, but I don't know what he deleted.

You need to set audit_trail

in DBMS_FGA.DB + DBMS_FGA.EXTENDED

by sending an audit trail to a table SYS.FGA_LOG$

in the database and enabling SQL Text and SQL Bind.

For example,



SELECT * FROM emp WHERE empno = 9998;
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);
UPDATE emp SET sal = 10 WHERE empno = 9998;
DELETE emp WHERE empno = 9998;
ROLLBACK;

-- Check the audit trail.

CONN sys/password AS SYSDBA
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998

4 rows selected.

You can also watch a demo by Tim Hall.

0


source






More articles:


All Articles