Unexpected entries into the SYS.AUD$

While working with a customer this week, they were testing some audit functionality on the database.  The database version being tested was Oracle 11g (11.2.0.3.5).  When the user created a table, the entry in the SYS.AUD$ is registered as a CREATE TABLE.  When the user issues a DROP TABLE, the entry for DROP TABLE is inserted into the SYS.AUD$ table. So, what is the problem? 

In reviewing the SYS.AUD$ table, they noticed that additional entries added.  These entries are related to Oracle Spatial, which the customer does not use.  So the question is, why are these spatial entries created in the SYS.AUD$ table on a DROP TABLE command? 

Looking into this issue, I was able to find a document in My Oracle Support that references this exact issue.  The note number is: 1338587.1

Cause:

Oracle needs to know when someone creates a table that has a sdo_georaster column.   The only way to identify this is to fire a trigger that determines if the table being created is a spatial table or not.  In short, this means that every table that is created is checked wither or not it is using a spatial column or not.

Solution:

The solution to this issue is to disable the triggers in the MDSYS schema that are being fired when the table is being dropped.  The triggers are listed below; they can also be found in the MOS note 1338587.1.

Disable these triggers from the MDSYS schema.

conn / as sysdba
alter session set current_schema=mdsys;
alter trigger sdo_geor_addl_trigger disable;
alter trigger sdo_geor_bddl_trigger disable;
alter trigger sdo_geor_err_trigger disable;
alter trigger sdo_topo_drop_ftbl disable;

After these triggers (above) have been disabled, rerun any tests for creating and dropping tables.  Then check the SYS.AUD$ table and verify that the spatial entries are no longer there.

Side Note: This issue is related to Bug: 12716670 – SPATIAL AUDIT ENTRIES ON AUD$ DOING GENERIC DDL/DML NOT SPATIAL RELATED

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,556 other followers

%d bloggers like this: