Deleting Records From SYS.AUD$ When Connected as Another User Than SYS Deletes Less Rows Than Selected
(Doc ID 388169.1)
Last updated on FEBRUARY 23, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.2 and laterOracle 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.
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:
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$ :
This would work because TRUNCATE is considered a DDL and not a DML.
Cause
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
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |