Re: Question about truncate the audit (aud$)

Pete Finnigan <plsql_at_petefinnigan.com> wrote in message news:<Ix2+WJBfLWUBRx0r_at_peterfinnigan.demon.co.uk>...
> Hi,
>
> If you want to allow another user to be able to delete the records from
> the SYS.AUD$ table then you can either directly grant delete on SYS.AUD$
> to the relevant user or grant the DELETE_CATALOG_ROLE to the user. The
> following report shows the privileges this role has:
>
> find_all_privs: Release 1.0.6.0.0 - Production on Wed Sep 22 08:35:22
> 2004
> Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
>
> NAME OF USER TO CHECK [ORCL]: DELETE_CATALOG_ROLE
> OUTPUT METHOD Screen/File [S]: S
> FILE NAME FOR OUTPUT [priv.lst]:
> OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
>
> User => DELETE_CATALOG_ROLE has been granted the following privileges
> ====================================================================
> TABLE PRIV => DELETE object => SYS.AUD$ grantable => NO
> TABLE PRIV => DELETE object => SYS.FGA_LOG$ grantable => NO
>
> PL/SQL procedure successfully completed.
>
> For updates please visit http://www.petefinnigan.com/tools.htm
>
> SQL>
>
> Do not grant DELETE ANY TABLE to your user. This is a security risk as
> it would allow the user with the privilege to delete any table. In
> 8.1.7.4 the default setting for the parameter
> o7_dictionary_accessibility is TRUE which potentially means the user can
> even delete dictionary data and cause the database to be damaged or
> unusable. Generally granting any privilege with the keyword ANY is a
> security risk.
>
> If you want to allow the user to be able to truncate a table owned by
> another user (SYS.AUD$ in this case) then you have to break this rule of
> granting privileges with the keyword ANY as you need to grant DROP ANY
> TABLE to the relevant user. Truncating is much faster than deleting but
> you should consider the risk of the privilege needed as well. In this
> case you can grant the DROP ANY TABLE privilege to a canned user that
> has no other privileges including CREATE SESSION and has an impossible
> password and / or locked . Then create your procedure as this user and
> grant execute on it to the user that will truncate the SYS.AUD$ table.
> That way you hide the privilege from the user needing to do the delete.
>
> Here is a simple example session creating a procedure that will truncate
> the SYS.AUD$ table from another user using a canned user to own the
> procedure and be granted the privilege - There are some comments in the
> code:-
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 22 11:46:29 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Personal Oracle9i Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> connect sys/a_at_sans as sysdba
> Connected.
> SQL> select count(*) from sys.aud$;
>
> COUNT(*)
> ----------
> 130032
>
> SQL> sho user
> USER is "SYS"
> SQL> -- set o7_dictionary_accessibility to true to simulate 8.1.7.4
> SQL> -- the second option is to grant SELECT ANY DICTIONARY otherwise
> SQL> -- the procedure will give a 942 error.
> SQL> alter system set o7_dictionary_accessibility=true scope=spfile;
>
> System altered.
>
> SQL> startup force;
> ORACLE instance started.
>
> Total System Global Area 135338868 bytes
> Fixed Size 453492 bytes
> Variable Size 109051904 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 667648 bytes
> Database mounted.
> Database opened.
> SQL> sho parameter o7
>
> NAME TYPE VALUE
> ------------------------------------ ----------- -----------------------
> -------
> O7_DICTIONARY_ACCESSIBILITY boolean TRUE
> SQL> -- create the user to own the truncate command
> SQL> create user truncaud identified by truncaud;
>
> User created.
>
> SQL> -- grant relevent privs
> SQL> grant create session,create procedure,drop any table to truncaud;
>
> Grant succeeded.
>
> SQL> -- connect and create a truncate procedure
> SQL> connect truncaud/truncaud
> Connected.
> SQL> create procedure trunc
> 2 as
> 3 begin
> 4 execute immediate 'truncate table sys.aud$';
> 5 end;
> 6 /
>
> Procedure created.
>
> SQL> -- let another user run the procedure
> SQL> grant execute on trunc to oscan;
>
> Grant succeeded.
>
> SQL> -- make the proecure owner safer.
> SQL> connect sys/a_at_sans as sysdba
> Connected.
> SQL> revoke create session,create procedure from truncaud;
>
> Revoke succeeded.
>
> SQL> alter user truncaud identified by values 'impossible';
>
> User altered.
>
> SQL> -- finally connect and test it.
> SQL> connect oscan/oscan
> Connected.
> SQL> -- check this user has no rights to truncate the table
> SQL> truncate table sys.aud$;
> truncate table sys.aud$
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
>
> SQL> -- now use the new procedure
> SQL> execute truncaud.trunc;
>
> PL/SQL procedure successfully completed.
>
> SQL> select count(*) from sys.aud$;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
>
>
> hth
>
> kind regards
>
> Pete

It works even with using the dbms_job. Thanks a lot.

C Chang
ps. I am going to read more about the security from your site. Our new project need a lot of such topics. Received on Thu Sep 23 2004 - 10:21:51 CDT