Re: Question about truncate the audit (aud$)

From: Pete Finnigan <plsql_at_petefinnigan.com>
Date: Wed, 22 Sep 2004 12:32:47 +0100
Message-ID: <Ix2+WJBfLWUBRx0r@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

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(*)

SQL> hth

kind regards

Pete

-- 
Pete Finnigan (email:pete_at_petefinnigan.com)
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Wed Sep 22 2004 - 06:32:47 CDT