Sybrand Bakker wrote:
> 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
I went to check the alert.log file today, it said that the problem was
insufficient privilege when I run the procedure using the DBMS_job.
Strangely I could use the user to run both the "TRUNCATE TABLE sys.aud$
REUSE STORAGE" directly or the EXEC sysAuditBackup procedure. It seems
the problem was at dbms_job. Do I need to grant the dbms_job the
privilege? grant to who or from who (sys)?
C Chang
Received on Fri Sep 24 2004 - 21:10:37 CDT
