Deleting Records From SYS.AUD$ When Connected as Another User Than SYS Deletes Less Rows Than Selected
Last updated on NOVEMBER 27, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.
Checked for relevance on 22-Apr-2013.
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$ :
create or replace procedure truncate_aud$ as
execute immediate 'truncate table aud$';
grant execute on truncate_aud$ to scott;
This would work because TRUNCATE is considered a DDL and not a DML.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms