Question about truncate the audit (aud$)
cschang
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
Daniel Morgan
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.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
C Chang
> 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
> > 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
sybr...@yahoo.com
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
C Chang
Would that be too danger to grant other non-sys DBA user a privilege
of DELETE ANY TABLE which will be involved with SYS? (or I mixed with
the idea of DROP ANY TABLE). I need to the manuals word by word.
Thanks.
Chisoon
C Chang
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
Sybrand Bakker
wrote:
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 Bakker, Senior Oracle DBA
Pete Finnigan
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@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>
hth
kind regards
Pete
--
Pete Finnigan (email:pe...@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.
C Chang
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
C Chang
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.
cschang
insufficient privilege when I run the procedure using the DBMS_job.
Strangely I could use the user to run both the "TRUNCATE TABLE sys.aud$
REUSE STORAGE" directly or the EXEC sysAuditBackup procedure. It seems
the problem was at dbms_job. Do I need to grant the dbms_job the
privilege? grant to who or from who (sys)?
C Chang