Link to home
Start Free TrialLog in
Avatar of ajaybelde
ajaybeldeFlag for United States of America

asked on

Auditing

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$;

/
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

How about asking a specific question?

What is wrong? What isn't working as planned?
Avatar of ajaybelde

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of johnsone
Isn't this question the same as

https://www.experts-exchange.com/questions/27238911/Auditing-Script.html

The script looks identical.
>>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
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
@johnsone yes..nobody is responding over there, thats why i reposted it.
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.
@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
I don't typically check duplicates either.  However, I had seen the original earlier today and knew that script look very familiar.
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.
Ok thanks, probably at what time you are going to start it, i will be waiting for you.
HI,
can anyone help me over here in writing the audit script
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Sean Stuber
Sean Stuber

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

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..?
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?
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.
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.
Begin
insert into info.order select * from sys.aud$;
delete sys.aud$;
commit;
end;
/

write a procedure for this sql statement
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
>>>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






>>> 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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial