Re: Question about truncate the audit (aud$)

From: C Chang <chi-soon_x_chang_at_raytheon.com>
Date: 23 Sep 2004 06:55:41 -0700
Message-ID: <88c62e86.0409230555.37835be5@posting.google.com>

Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<ovo0l0d59pmf43jccg38h18po3ijsdu6ug_at_4ax.com>...
> 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:
I have tried both ways as u suggested as

1. login SYS
2. GRANT DELETE on AUD$ TO usercore;
3. login as usercore
4. create procedure sysAuditBackup as following:

CREATE OR REPLACE PROCEDURE sysAuditBackup AUTHID CURRENT_USER AS
   cursor_nr       NUMBER;
   sql_text        VARCHAR2 (200) := 'TRUNCATE TABLE sys.aud$ REUSE

STORAGE';
rows NUMBER;

BEGIN

EXECUTE IMMEDIATE sql_text;

   cursor_nr := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (cursor_nr, sql_text, DBMS_SQL.native);    rows := DBMS_SQL.execute (cursor_nr);    DBMS_SQL.close_cursor (cursor_nr);
   EXCEPTION

      WHEN OTHERS THEN
         DBMS_SQL.close_cursor (cursor_nr);
      RAISE;

END sysAuditBackup;
/

Procedure created.

SQL>   var jon number
SQL> 
SQL> begin
  2     dbms_job.submit(:jon, 
  3                     'sysAuditBackup;');

6 end;
7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

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

COUNT(*)

183

SQL> begin
  2 dbms_job.run(45);
  3 end;
  4 /
begin
*
ERROR at line 1:

ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 405
ORA-06512: at "SYS.DBMS_JOB", line 267
ORA-06512: at line 2

but if tun this

SQL> exec sysAuditBackup;

PL/SQL procedure successfully completed. SQL> select count(*) from sys.aud$;

COUNT(*)

0
Why dbms_job does not allow userCore own procedure to run? I had tried the the 'EXECUTE IMMEDIATE ..' in the procdure also, same result. Do I miss somthing here? My system is 8.1.7 Thanks. I am going to try Pete's way.

C Chang Received on Thu Sep 23 2004 - 08:55:41 CDT