Re: Question about truncate the audit (aud$)

On 21 Sep 2004 06:07:54 -0700, chi-soon_x_chang_at_raytheon.com (C Chang) wrote:

>sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0409200718.31d1ec16_at_posting.google.com>...
>> chi-soon_x_chang_at_raytheon.com (C Chang) wrote in message news:<88c62e86.0409200157.4a2c2ebd_at_posting.google.com>...
>> > Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1095648966.158220_at_yasure>...
>> > > cschang wrote:
>> > > > I activated the audit under the sys and altered its tablespace out of
>> > > > system and put it into a user schema tablespace. However, according to
>> > > > the principle, I should periodically store the content to another place
>> > > > (out of system ). I believe I can write a procedure under the sys to
>> > > > copy the content to other user?s table and TRUNCATE out the content.
>> > > > Although many previous newsgroup articles suggest to grant the execute
>> > > > privilege to another user to run the procedure. My question is: is that
>> > > > still a bad practice to create a procedure (an object) under the sys
>> > > > which is from the Oracle to define the system and is not supposed to be
>> > > > modified? ( I remembered one of the previous newsgroup articles
>> > > > mentioned about such principle). My system is 8.1.7 on NT 4. Thanks.
>> > > >
>> > > > C Chang
>> > >
>> > > Never, ever, for any reason write anything as SYS. There is no reason
>> > > to and you shouldn't. It has always been a bad practice and remains so.
>> >
>> > So then, how can I clean out the aud$ table periodically without
>> > creating a procedure under SYS? Thanks.
>> >
>> > C Chang
>>
>>
>> You need privilege to that table. *Any* user with the DBA role (or
>> delete any table privilege) has that privilege.
>>
>> Sybrand Bakker
>> Senior Oracle DBA
>
>Will that be too dnager to grant the DELETE ANY TABLE from SYS to a
>DBA user? (someone in the newsgroup mentioned this before) or I mixed
>with the idea of DROP ANY TABLE. I need to read manual word by word.
>
>I have just tried to grant the DELETE TABLE aud$ to my schema DBA user
>and rewrote the procedure under the DBA user, tested it; I found out
>that the 'TRUNCATE TABLE sys.aud$ REUSE STORAGE" in the procedure
>still not work.
>
>C Chang

Add
authid current_user
before the is/as
or grant direct privilege to the affected user (current privilege is via role)
BTW: you should have posted the error message, and also this is a FAQ

--
Sybrand Bakker, Senior Oracle DBA