Recently while helping a customer troubleshoot a problem, all operations on a configuration table needed to be audited because it was suspected that the application had made changes to this table.
Originally, Oracle's auditing of a table is very convenient, a command can be achieved, and do not need to bother to customize the audit table.
-- Enable auditing of insert, update, and delete operations on table DEPT
AUDIT INSERT, UPDATE, DELETE ON DEPT BY ACCESS.
-- View all audit records for the DEPT table.
SELECT * FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME = 'DEPT';
-- Disable auditing of table DEPT
NOAUDIT INSERT, UPDATE, DELETE ON DEPT; -- Disable auditing on table DEPT.
But unfortunately, because the client's current environment has audit_trail turned off by default. (On by default, the default value is DB, off is NONE)
The author has verified this in an experimental setting:
- 1. audit_trail must be on for an audit of the table to be logged;
- 2. audit_trail is a static parameter, if you modify it, you need to restart the database to take effect.
Pay particular attention to check the setting of this parameter, because one of the pitfalls here is that the above audit command, even if you turn off this parameter, the execution of the command to turn on table auditing does not report an error, it just can't actually be recorded.
I believe we all know that the restart request process in the production environment is very troublesome, so we can only monitor the DML operations of this table in a manual way, such as custom triggers.
Luckily, because this configuration table is normally not modified very often, using triggers doesn't cause any performance problems.
Let's move on to the hands-on session, where we simulate the needs of this customer, assuming dept this table:.
-- Create the example table DEPT
CREATE TABLE dept (
deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(14), loc VARCHAR2(13), dname
loc VARCHAR2(13)
).
-- Insert the example data into the DEPT table
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); -- Insert sample data into DEPT table.
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');; -- Insert example data into DEPT table.
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO')
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON'); INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON').
VALUES (40, 'OPERATIONS', 'BOSTON'); commit;
Create a trigger against the dept table as follows:
-- CREATE TABLE
CREATE TABLE dml_audit(
modiy_time DATE,
table_name VARCHAR2(30),
deptno NUMBER(2),
modiy_type VARCHAR2(14),
dname VARCHAR2(14),
loc VARCHAR2(13)
);
CREATE OR REPLACE TRIGGER dept_trg
BEFORE INSERT OR DELETE OR UPDATE ON dept
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc)
VALUES (sysdate, 'dept', :, 'insert', :, :);
END IF;
IF deleting THEN
INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc)
VALUES (sysdate, 'dept', :, 'delete', :, :);
END IF;
IF updating THEN
INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc)
VALUES (sysdate, 'dept', :, 'update_before', :, :);
INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc)
VALUES (sysdate, 'dept', :, 'update_after', :, :);
END IF;
END;
/
Test it and confirm that delete, insert, and update can be monitored:
08:17:58 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> select * from dml_audit;
no rows selected
Elapsed: 00:00:00.02
08:18:05 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> delete from dept where deptno=10;
1 row deleted.
Elapsed: 00:00:00.22
08:18:41 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
Elapsed: 00:00:00.00
08:18:56 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> update dept set LOC='China' where deptno=40;
1 row updated.
Elapsed: 00:00:00.04
08:19:25 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> select * from dml_audit;
MODIY_TIME TABLE_NAME DEPTNO MODIY_TYPE DNAME LOC
------------------ ------------------------------ ---------- -------------- -------------- -------------
07-AUG-24 dept 10 delete ACCOUNTING NEW YORK
07-AUG-24 dept 10 insert ACCOUNTING NEW YORK
07-AUG-24 dept 40 update_before OPERATIONS BOSTON
07-AUG-24 dept 40 update_after OPERATIONS China
Elapsed: 00:00:00.01
Is everything all right?
In fact, no, because the customer problem is to suspect that the table has been altered, and changes are not actually limited to DML operations.
Let's assume an extreme scenario, if an operation is to drop this table and rebuild it and then insert the same data.
Such a scenario cannot be documented by the above trigger alone. Because when the table is dropped, the corresponding trigger is also deleted.
Because triggers are objects that depend on the existence of a table, when the table is deleted, the trigger also loses its role as an object.
For example, there's this init script that directly drops the configuration table and then recreates the initialization data:
@init
So what do you have to do to monitor this extreme situation?
It's not hard to create another trigger for DDL, and if there is a ddl operation on the dept table it is also logged to the specified log table.
-- Creating an Audit Table
CREATE TABLE ddl_audit (
event_time TIMESTAMP,
username VARCHAR2(30),
userhost VARCHAR2(30),
object_type VARCHAR2(30),
object_name VARCHAR2(30),
action VARCHAR2(30)
);
-- establish DDL flip-flop (electronics)
CREATE OR REPLACE TRIGGER ddl_trigger
AFTER CREATE OR DROP ON SCHEMA
DECLARE
v_username VARCHAR2(30);
v_userhost VARCHAR2(30);
BEGIN
SELECT USER, SYS_CONTEXT('USERENV', 'HOST') INTO v_username, v_userhost FROM DUAL;
IF ORA_DICT_OBJ_NAME = 'DEPT' THEN
INSERT INTO ddl_audit (
event_time, username, userhost, object_type, object_name, action
) VALUES (
SYSTIMESTAMP, v_username, v_userhost, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_SYSEVENT
);
END IF;
END;
/
In this way, if there is really such a DDL operation, it can also be audited, but it should be noted that the impact of the trigger of this DDL is relatively larger, non-essential cases do not build first. After locating the problem, delete it in a timely manner.
08:26:49 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> select * from ddl_audit;
EVENT_TIME USERNAME USERHOST OBJECT_TYPE OBJECT_NAME ACTION
------------------------------ --------------- --------------- ------------------------------ --------------- ------------------------------
07-AUG-24 08.25.11.885875 AM JINGYU demo TABLE DEPT DROP
07-AUG-24 08.25.12.167304 AM JINGYU demo TABLE DEPT CREATE
07-AUG-24 08.25.59.989356 AM JINGYU demo TABLE DEPT DROP
07-AUG-24 08.26.00.061629 AM JINGYU demo TABLE DEPT CREATE
Elapsed: 00:00:00.01
As you can see, this trigger way, although more bulky than the audit, but also can be achieved on the table either DDL or DML track record, with these traces of the operation, naturally, it will be convenient for customers to go to further troubleshooting problems.