Deleting Records From SYS.AUD$ When Connected as Another User Than SYS Deletes Less Rows Than Selected (Doc ID 388169.1)

Last updated on NOVEMBER 03, 2015

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.


Symptoms

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:

SQL> connect x/x
Connected.
SQL> select count(*) from sys.aud$;

COUNT(*)
----------
5

When the user connected is SYS, the number of rows deleted is 1200. 

SQL> delete from sys.aud$;
0 rows deleted.

Changes

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
begin
execute immediate 'truncate table aud$';
end;
/

grant execute on truncate_aud$ to scott;

 
This would work because TRUNCATE is considered a DDL and not a DML.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms