Deleting Records From SYS.AUD$ When Connected as Another User Than SYS Deletes Less Rows Than Selected
(Doc ID 388169.1)
Last updated on JANUARY 31, 2022
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
This problem can occur on any platform.
A user X granted SELECT and DELETE on SYS.AUD$ table by SYS user selects a number of rows from SYS.AUD$ table but cannot delete any rows or only some of them, for example:
When the user connected is SYS, the number of rows deleted is 1200.
In RDBMS version 10.2 Oracle Development implemented a security enhancement that mandates that DML on the audit trail (SYS.AUD$) must always be audited and that those records cannot be deleted other than by user SYS.
Note that this rule is also enforced even if SYS creates a stored procedure that deletes the rows from SYS.AUD$ with definer's rights and grants execute on this procedure to another user, this will not work.
The only thing that would work is to create a procedure in the SYS schema which would truncate table AUD$ :
This would work because TRUNCATE is considered a DDL and not a DML.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document