troubleshooting Question

Auditing

Avatar of ajaybelde
ajaybeldeFlag for United States of America asked on
Oracle Database
27 Comments4 Solutions539 ViewsLast Modified:
HI,
I have to write the auditing script, so guys please help me where i was wrong, all the auditing is to be done by sys.aud$, and the records needs to move to new schema info, i have to schedule every day to move the audit records from sys.aud$ to info.order,
below is the script which i wrote , can u make some changes and tell me where i was wrong

spool audit.log

--"Auditing Initialisation Parameters: check initialization parameter"

select name || '=' || value from v$parameter where name like '%audit%'
/

---"if auditing is disabled then issue this command and bounce"

alter system set audit_trail=db,extended scope = spfile
/

shutdown immediate

startup

select name || '=' || value from v$parameter where name like '%audit%'
/


create tablespace ORDER_DATA datafile '+DDATA' size 50m;

create user INFO identified by INFO;

Grant connect,resource to INFO;

Alter user INFO quota unlimited on ORDER_DATA;

connect INFO/INFO

create table ORDER
 partition by range (Timestamp#)
 subpartition by hash(dbid)
  subpartition template
  (subpartition sp1 tablespace users,
 subpartition sp2 tablespace users)(
  partition p1 values less than (TO_DATE('07/29/2010','MM/DD/YYYY')),
   partition p2 values less than (TO_DATE('07/29/2011','MM/DD/YYYY')),
 partition p3 values less than (MAXVALUE)) tablespace AUDIT_DATA as select * from sys.aud$



-----AUDIT ON CREATE SESSION,EXECUTE PROCEDURE, UPDATE TABLE, ALTER TABLE WHEN EVER IT IS FAILURE----------
AUDIT CREATE SESSION, EXECUTE PROCEDURE, UPDATE TABLE, ALTER TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;



---- "Statement Audits Enabled on this Database"
column user_name format a10
column audit_option format a40
select *
from   sys.dba_stmt_audit_opts
/

----"Check Privilege Audits Enabled on this Database"
select * from dba_priv_audit_opts
/

----"Check Objects, Audits Enabled on this Database"
select (owner ||'.'|| object_name) object_name,
       alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from   dba_obj_audit_opts
where  alt != '-/-' or aud != '-/-'
   or  com != '-/-' or del != '-/-'
   or  gra != '-/-' or ind != '-/-'
   or  ins != '-/-' or loc != '-/-'
   or  ren != '-/-' or sel != '-/-'
   or  upd != '-/-' or ref != '-/-'
   or  exe != '-/-'
/


spool off;

exit

insert into info.ORDER as select * from sys.aud$;

/
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 4 Answers and 27 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 27 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros