Auditing Ddl Changes On Database Receives Extra Event Records For Dropping Table (Doc ID 788638.1)

Goal

You have a trigger created to audit DDL changes within the production database. The trigger uses the AFTER DDL ON DATABASE clause. SYS creates both the trigger and a table ddl_events to store data about the DDL event.

The trigger inserts the data into the ddl_events table with data from built-in functions such as ora_dict_obj_owner, ora_sysevent, ora_dict_obj_type, and ora_dict_obj_name.

You have a problem to audit dropping a table from a schema user. If SYS creates a table that contains n columns with NOT NULL constraint, then drop the table, the trigger inserts one row with sysevent “Drop” into the ddl_events table. This is what we want.

However if any schema user creates the same table that contains n columns with NOT NULL constraint, then drop the table. The trigger inserts n rows with sysevent “Alter” to rename the constraints to “BIN$xxx” and plus one row with sysevent “Alter” to rename the table to “BIN$xxx” and plus one row with sysevent “Drop”. So we see there are n+2 rows in the audit table and may receive n+2 emails (if we set up email notification for the DDL auditing) for one DDL event to drop a table.
These event logs and emails are really confusing. Since auditing DDL changes is critical in your production databases, you need only one clear row of drop event recorded in the table and one email sent out for auditing a schema user to drop a table.

So, the questions here are:

1. Why does the trigger get different event records by SYS and a schema user as described above.
2. How can we exclude these Alter events from the trigger, at least from the email, for dropping a table by a schema user?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.