Pete Finnigan's Oracle Security Weblog

Today there has been a question posted to comp.oracle.databases.misc (The thread is here) asking about deleting or truncating the audit trail when its stored in the SYS owned table AUD$. The poster wanted to know about creating a procedure to periodically delete or truncate the contents and about creating that procedure as the user SYS.

I answered with quite a log post detailing the issues of privileges and also showing a short example if creating a procedure as a user that has had its privileges removed later so it cannot be logged in with and then calling that procedure from another user to encapsulate the privilege.

You can read my post by clicking "more".

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 /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 the privilege:-

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@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@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>