Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Question about truncate the audit (aud$)
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 bytesDatabase mounted.
NAME TYPE VALUESQL> -- create the user to own the truncate command SQL> create user truncaud identified by truncaud;
------------------------------------ ----------- -----------------------
-------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
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$
*
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
-- 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
![]() |
![]() |