Deleting from sys.aud$

[GaryA]

Hoping someone can explain why I am running into the issue seen
below. I do a select count(*) from sys.aud$ and get one number, but
when I do a delete using the same where condition I get a
substantially different number of records to be deleted.

*******************************
SQL> select count(*) from sys.aud$ where userid = 'MIKE1';

COUNT(*)
----------
33570

SQL> delete from sys.aud$ where userid = 'MIKE1';

2 rows deleted.

SQL> rollback work;

Rollback complete.

SQL> select count(*) from sys.aud$;

COUNT(*)
----------
147042

SQL> delete from sys.aud$;

12154 rows deleted.

SQL> rollback work;

Rollback complete.
*******************************

Can anyone offer a reason as to why I am seeing such differing
results?

Thank you.
Gary

[GaryA]

Forgot to mention that this is Oracle 11g on Windows 2003 Server.

The statements worked as expected in an Oracle8i environment.

-Gary

[Steve Howard]

Hi Gary,

No clue. I would suggest:

1) Enable 10046 trace on your session and then issuing each.
2) Opening an SR if what is above doesn't show what is different.

Thanks,

Steve

[Mark D Powell]

Who are you connected as? Do you have sys auditing turned on? Do you
have extended auditing turned on?

Did you query the dba_audit_trail to see if new audit rows were
inserted because of your action to see what information these rows may
show?

Post your audit rules.

HTH -- Mark D Powell --

[GaryA]

On Dec 23, 3:49 pm, Mark D Powell <Mark.Powe...@hp.com> wrote:
> On Dec 23, 11:13 am, GaryA <garyi...@yahoo.com> wrote:
>
>
>
> > Hoping someone can explain why I am running into the issue seen
> > below.  I do a select count(*) from sys.aud$ and get one number, but
> > when I do a delete using the same where condition I get a
> > substantially different number of records to be deleted.
>
> > *******************************
> > SQL> select count(*) from sys.aud$ where userid = 'MIKE1';
>
> >   COUNT(*)
> > ----------
> >      33570
>
> > SQL> delete from sys.aud$ where userid = 'MIKE1';
>
> > 2 rows deleted.
>

<snip>

>
> > Thank you.
> > Gary
>
> Who are you connected as?  Do you have sys auditing turned on?  Do you
> have extended auditing turned on?
>

I have tried this connected as SYS, SYSTEM, and a user account with
SELECT and DELETE privileges on SYS.AUD$.
Sys auditing is false. Extended auditing is also off.

audit_sys_operations=FALSE
audit_trail=DB

> Did you query the dba_audit_trail to see if new audit rows were
> inserted because of your action to see what information these rows may
> show?
>
> Post your audit rules.
>
> HTH -- Mark D Powell --

I've discovered that if I connect "AS SYSDBA", I can delete the rows
as expected. However, when I connect simply as "SYS", I get the
results shown in the initial post. I'm now making an assumption that
perhaps something has changed in regards to security on the AUD$ table
between Oracle8 (where "AS SYSDBA" didn't seem to be required to
delete the rows) and Oracle11.

-Gary

[GaryA]

I have found the cause of the discrepancies in the records counts
after deleting records from sys.aud$, but I'm still at a loss as to
the rationale behind it.

When I connect "AS SYSDBA", I can delete all the records that meet the
section criteria.

When I do not connect "AS SYSDBA", the delete command deletes all the
sys.aud$ records meeting the criteria EXCEPT those with action# = 7,
which are audits records of other previous delete commands.

At least I now know what I have to do to resolve this issue and delete
the records I need to delete.

-Gary

[Randolf Geist]

I think this is (more or less) documented behaviour, see e.g.:

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1011521

The point is that audit entries for auditing SYS.AUD$ can not be
deleted by "non-SYSDBA" users.

Quote from the link above: "DELETE, INSERT, UPDATE, and MERGE
operations on SYS.AUD$ table are always audited, and such audit
records are not allowed to be deleted."

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684

[hpuxrac]

On Dec 24, 11:29 am, GaryA <garyi...@yahoo.com> wrote:

snip

> I have found the cause of the discrepancies in the records counts
> after deleting records from sys.aud$, but I'm still at a loss as to
> the rationale behind it.
>
> When I connect "AS SYSDBA", I can delete all the records that meet the
> section criteria.

That's pretty much a basic concept ... not surprised that people did
not understand you were not doing it already.

In posts like this it always helps if one supplies complete examples
including all the code in a script or testcase so any such assumptions
get flushed out as quickly as possible.

> When I do not connect "AS SYSDBA", the delete command deletes all the
> sys.aud$ records meeting the criteria EXCEPT those with action# = 7,
> which are audits records of other previous delete commands.
>
> At least I now know what I have to do to resolve this issue and delete
> the records I need to delete.

Probably looking at the oracle documentation is also a good idea.

[Mark D Powell]

>  -Gary- Hide quoted text -
>
> - Show quoted text -

Gad you figured it out.