AUDIT in oracle 10g [message #386949] |
Tue, 17 February 2009 06:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136607.jpg) |
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Hi every one i had a small doubt.because of to reduce the burden on system table space,
i am planing to write one object which will truncate the sys.aud$
before that, it should write the content into another user`s object. is it meaningful ? i mean shall i proceed in that way or any other alternate method.I read Natalka Roshak`s blog on AUDITING.but thats on different version
please suggest me
regards
sriram
|
|
|
|
|
|
|
|
|
|
|
Re: AUDIT in oracle 10g [message #387378 is a reply to message #387203] |
Thu, 19 February 2009 01:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136607.jpg) |
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Thank you Michel.and one more ...the sql plus command sho user displays the current connected user name,but by mistake while typing sho user one of my user(who can connect as sys) typed like "ho user"
the out put is like
so what`s the background process done here.As i dont know this command i am asking here ofcourse i googled for this and it shows that its one kind of shell script.
Please explain me...or give me any URL
regards
sriram
[Updated on: Thu, 19 February 2009 01:05] Report message to a moderator
|
|
|
|
|
Re: AUDIT in oracle 10g [message #387593 is a reply to message #387410] |
Fri, 20 February 2009 00:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136607.jpg) |
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
SQL>select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
RAGHAV SEQUENCE BY ACCESS BY ACCESS
RAGHAV SYNONYM BY ACCESS BY ACCESS
RAGHAV SYSDBA BY ACCESS BY ACCESS
RAGHAV SYSOPER BY ACCESS BY ACCESS
RAGHAV SYSTEM AUDIT BY ACCESS BY ACCESS
RAGHAV SYSTEM GRANT BY ACCESS BY ACCESS
RAGHAV TABLE BY ACCESS BY ACCESS
RAGHAV TABLESPACE BY ACCESS BY ACCESS
RAGHAV TRIGGER BY ACCESS BY ACCESS
RAGHAV TYPE BY ACCESS BY ACCESS
RAGHAV UNDER ANY TABLE BY ACCESS BY ACCESS
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
RAGHAV UNDER ANY TYPE BY ACCESS BY ACCESS
RAGHAV UNDER ANY VIEW BY ACCESS BY ACCESS
RAGHAV UNLIMITED TABLESPACE BY ACCESS BY ACCESS
RAGHAV UPDATE ANY TABLE BY ACCESS BY ACCESS
RAGHAV UPDATE TABLE BY ACCESS BY ACCESS
RAGHAV USER BY ACCESS BY ACCESS
RAGHAV VIEW BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
DELETE ANY TABLE BY ACCESS BY ACCESS
DELETE TABLE NOT SET BY ACCESS
EXECUTE PROCEDURE NOT SET BY ACCESS
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
INSERT TABLE NOT SET BY ACCESS
SELECT TABLE NOT SET BY ACCESS
HERE RAGHAV is my user.which should be audited.but the other audit options i didn`t understand.but by checking the dba_common_audit_trail table i found that my anothe user is also participating i mean...the table records the actions of that user also.for that i did the following...SQL>noaudit all by appowner; SQL>noaudit insert table by appowner; .in the sameway done for all the AUDIT OPTIONS SPECIFIED BY OTHER THAN THE USER RAGHAV.
but still i am getting the audit records of APPOWNER.
any suggestion. AS I WANT ONLY RAGHAV`s records,i am truncating the aud$ table , for each 500 rows in aud$ table.does it effect my database? the record are populating when ever the the user is connect through the tomcat i mean our application.
[Updated on: Fri, 20 February 2009 00:07] Report message to a moderator
|
|
|
Re: AUDIT in oracle 10g [message #387609 is a reply to message #387593] |
Fri, 20 February 2009 00:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68647 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
NOAUDIT deactivate ONLY audit the exactly same AUDIT command. It has no effect (and is ignored) on different AUDIT commands.
If you want audit only one user, you have to NOAUDIT all commands you made and reexecute AUDIT commands for this specific user.
Please when a column contains no (meaningful) value remove it from the output you post to limit the width of the lines. Here PROXY_NAME is useless and prevent me from reading the last column without scrolling.
Regards
Michel
[Updated on: Fri, 20 February 2009 00:52] Report message to a moderator
|
|
|
|
|
|
|
|
Re: AUDIT in oracle 10g [message #387681 is a reply to message #387635] |
Fri, 20 February 2009 05:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136607.jpg) |
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Michel please have a look at this.As you suggested done all the commands successfully.but
SYS@IND>select USER_NAME,PRIVILEGE,SUCCESS,FAILURE from dba_priv_audit_opts;
USER_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
DELETE ANY TABLE BY ACCESS BY ACCESS
2 rows selected.
SYS@IND>select USER_NAME,AUDIT_OPTION,SUCCESS,FAILURE from dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
DELETE ANY TABLE BY ACCESS BY ACCESS
SELECT TABLE NOT SET BY ACCESS
INSERT TABLE NOT SET BY ACCESS
DELETE TABLE NOT SET BY ACCESS
EXECUTE PROCEDURE NOT SET BY ACCESS
6 rows selected.
AS you suggested to noaudit all,stil these are there how can i done with all these without knowing the USERNAME
SYS@IND>select distinct action from dba_audit_trail;
ACTION
----------
100
102
101
3 rows selected. that is only logon,logoff,logoffbyclean with the privilege create session.....how can i no audit all this without knowing the proxy or username
|
|
|
|