Auditing

ajaybelde
ajaybelde used Ask the Experts™
on
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$;

/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
How about asking a specific question?

What is wrong? What isn't working as planned?

Author

Commented:
I dont how to schedule the script for moving the audit records sys.aud$ to new schema, how i need to schedule in crontab.can u provide the commands
Top Expert 2009
Commented:
I see nowhere where you purge the log. Depending on your Oracle version, you can use the new DBMS_AUDIT_MGMT to perform some of the audit work, such as purging.

http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm


At the bottom of this page (http://www.oracle-base.com/articles/11g/AuditingEnchancements_11gR2.php) there are some tips for setting up audit archiving and purging. You need to write the script to copy the records you want to archive out of the aud trable, but then you use the API to control the purging.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

johnsoneSenior Oracle DBA

Commented:
Isn't this question the same as

http://www.experts-exchange.com/Database/Oracle/Q_27238911.html

The script looks identical.
Top Expert 2009

Commented:
>>I dont how to schedule the script for moving the audit records sys.aud$ to new schema,

Setup a DBMS_SCHEDULER job to call a PL/SQL procedure. Put your logic inside that procedure.

Docs here:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000363

Use JOB_TYPE of STORED_PROCEDURE or PLSQL_BLOCK

Example here: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schedadmin006.htm#i1009099

Author

Commented:
can u please help me in writing the scripts for auditing in 10gr2,11gr1, and 11gr2. for moving the auditng records from sys.aud$ to info.order aon every day.can u please provide commands using above example script

Author

Commented:
@johnsone yes..nobody is responding over there, thats why i reposted it.
Top Expert 2009

Commented:
Hmm, you should ping the original thread, not post duplicates. We now have to merge the points due to EE guidelines. Personally I don't check duplicates, but johnsone as pointed it out. You need to edit each question and assign half point to each, unfortunately, and this doesn't make experts of the original thread happy, usually; though I know sdstuber and he won't likely mind.

Author

Commented:
@mrjoltcola : i can give you full points if you help me over here.becuase i am looking for exact solution how to write the scripts in all the three versions in 10gr2, 11gr1, and 11gr2
johnsoneSenior Oracle DBA

Commented:
I don't typically check duplicates either.  However, I had seen the original earlier today and knew that script look very familiar.
Top Expert 2009

Commented:
I'll help. I have a meeting with a customer shortly, will have to return to this later on. I've also notified sdstuber that we are working in this thread as well, hopefully he will pop in.

Author

Commented:
Ok thanks, probably at what time you are going to start it, i will be waiting for you.

Author

Commented:
HI,
can anyone help me over here in writing the audit script
Most Valuable Expert 2011
Top Expert 2012
Commented:
can you be more specific as to what you are looking for?


for the scheduling piece...


BEGIN
    DBMS_SCHEDULER.create_job(
        job_name         => 'YOUR_DAILY_JOB_NAME',
        job_type         => 'PLSQL_BLOCK',

        job_action       => 'begin insert into info.ORDER as select * from sys.aud$;
; delete sys.aud$; commit; end; ',   -- expand as needed here...

        start_date       => TRUNC(SYSDATE) + 22 / 24,
        repeat_interval  => 'FREQ=daily;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
        enabled          => TRUE,
        comments         => 'Your daily job.');
END;

Author

Commented:
i need to write a auditing script in all the versions of 10gr2, 11gr1, and 11gr2, so any can one help me here to write the script.
Most Valuable Expert 2011
Top Expert 2012

Commented:
what do you need that you don't already have?

Author

Commented:
the script should select the version and run according to that version,
for example if it is 10.2.0.4 version then the script needs to run according to the 10g version, if it is 11gr1 and 11gr2 it should run according to that version..?
Most Valuable Expert 2011
Top Expert 2012

Commented:
your script looks to me like it should run fine on all 3 versions.  - are you getting errors? if so, what are they?

the dbms_scheduler snippet I posted should be the same on all 3 as well.

is there something specific about each version that you're trying to support?

Author

Commented:
i need a procedure to move the records not a dbms_scheduler for moving the scheduler, because the dbms_scheduler supports in 11gr2, so i need a procedure for moving the audit records from sys.aud$ to info.order in 10gr2, and 11gr1 version.
Top Expert 2009

Commented:
Write one SQL statement that will be compatible with all.

Due to time constraints I cannot write the script for you anytime soon, but I'd be happy to help if you show initiative to start your own work first.

Essentially what you need to do is write SQL to copy records from A to B, then trim A.

Author

Commented:
Begin
insert into info.order select * from sys.aud$;
delete sys.aud$;
commit;
end;
/

write a procedure for this sql statement
Top Expert 2009
Commented:
CREATE OR REPLACE PROCEDURE archive_aud
AS
  ts TIMESTAMP;
BEGIN
  ts := SYSTIMESTAMP;
  insert into info.order select * from sys.aud$ where timestamp# < ts;
  delete sys.aud$ where timestamp# < ts;
  commit;
END;
/

Author

Commented:
what about hash partioning with database name,how i need to keep in procedure..?, does this procedure supports in 10gr2, and 11gr1..?, today is my last day, if it is 10gr2, and 11gr1 the procedure needs to be executed and if it is 11gr2 the dbms_scheduler needs to be executed. so what i have to do to change the script.

Author

Commented:
can u keep the for loops or if loops to make the script look good and the script needs to run in all the versions, today is the last day for this task, i have to submit today.
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>>what about hash partitioning?

what about it?


>>> today is my last day   -- what do you mean by this?


, if it is 10gr2, and 11gr1 the procedure needs to be executed

 and if it is 11gr2 the dbms_scheduler

why?  different methods?  That's not what you asked for, what changed?


you request is very simple,  take the dbms_scheduler block above

put the procedure mrjoltcola wrote inside it (or put the contents inside as an anonymous block)

and run it
done

should work for 10gr2, 11gr1,11gr2 without modification
if it doesn't,  please post the exact error you are getting






Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> can u keep the for loops or if loops to make the script look good


there are no loops in what you need
as for "looking good"  format the code to whatever standard your company uses.

if there is no standard, then format to suit your own whim

if you don't have a preference, then leave as is
Top Expert 2009
Commented:
I'm not sure what you are asking.

If you want partitioning, you should partition the table offline.

You can also migrate and partition the base audit table in Oracle 11, by using the PL/SQL API I mentioned before (or maybe in the other question). But that doesn't need to exist in the procedure.

As for formatting, etc. well, that is up to you. If you need more and cannot format it yourself, consider hiring someone.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial