OraFAQ Forum: Security » AUDIT in oracle 10g

Home » RDBMS Server » Security » AUDIT in oracle 10g (10.2.0.1.0 windows 2003r2 server)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

AUDIT in oracle 10g [message #386949]

Tue, 17 February 2009 06:10

ramoradba
Messages: 2457
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

Report message to a moderator

Re: AUDIT in oracle 10g [message #386952 is a reply to message #386949]

Tue, 17 February 2009 06:32

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It all depends on your final audit requirements.

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387090 is a reply to message #386952]

Tue, 17 February 2009 21:46

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Thank you michel
i need to know one specific user actions only.thats why i am enabling auditing.

Report message to a moderator

Re: AUDIT in oracle 10g [message #387106 is a reply to message #387090]

Tue, 17 February 2009 23:33

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

i need to know one specific user actions only.

So activate AUDIT

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387113 is a reply to message #387106]

Tue, 17 February 2009 23:54

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

THANK YOU MICHEL IKNOW HOW TO ENABLE AUDIT. BUT MY QUESTION IS SHALL I WRITE ANY TRIGGER WHICH WILL INSERT ALL THE CONTENT INTO MY ANOTHER USER AND THEN TRUNCATE THE SYS.AUD$.SO IS IT MEANINGFUL?SHALL I PROCEED LIKE THAT OR ANY SUGGES...

Report message to a moderator

Re: AUDIT in oracle 10g [message #387116 is a reply to message #387113]

Wed, 18 February 2009 00:03

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

sorry for the capital letters

Report message to a moderator

Re: AUDIT in oracle 10g [message #387139 is a reply to message #387113]

Wed, 18 February 2009 00:46

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The question is why do you think you should? How do you come to this point?

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387185 is a reply to message #387139]

Wed, 18 February 2009 03:41

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

i didn`t get you Mr.michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387203 is a reply to message #387185]

Wed, 18 February 2009 04:30

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't get you either.
You said:

Quote:

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 ?

OK, Now I answered:

Quote:

It all depends on your final audit requirements.

It is meaningful if your audit requirements need it, otherwise it is not.

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387378 is a reply to message #387203]

Thu, 19 February 2009 01:03

ramoradba
Messages: 2457
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

ind> ho user

ind>

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 #387381 is a reply to message #387378]

Thu, 19 February 2009 01:10

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No background process involved, it is your SQL*Plus.
HO is short version of HOST command.

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387410 is a reply to message #387381]

Thu, 19 February 2009 02:31

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

thank you michel.

[Updated on: Thu, 19 February 2009 23:43]

Report message to a moderator

Re: AUDIT in oracle 10g [message #387593 is a reply to message #387410]

Fri, 20 February 2009 00:02

ramoradba
Messages: 2457
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;

noaudit succeded

SQL>noaudit insert table by appowner;

noaudit succeded

.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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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 #387615 is a reply to message #387609]

Fri, 20 February 2009 01:15

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Thank you michel.but here is, i did same as you suggested ,what ever the audit option are displayed by the above query are executed by me with noaudit command.no audit record is generated through sqlplusw.exe.but with our application which will use tomcat the log is generated.the application is using user appowner.when i query the results from dba_common_audit_trail only db_user is populated no all the columns.any suggess?

regards
sriram

Report message to a moderator

Re: AUDIT in oracle 10g [message #387616 is a reply to message #387615]

Fri, 20 February 2009 01:20

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Please find the attachment.

regards
sriram

Attachment: sp.txt
(Size: 7.58KB, Downloaded 2490 times)

Report message to a moderator

Re: AUDIT in oracle 10g [message #387623 is a reply to message #387616]

Fri, 20 February 2009 01:38

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because these records are not related to a sql statement.

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387628 is a reply to message #387623]

Fri, 20 February 2009 01:51

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

so how can i made that to be not generated in the audit records? please

SYS@IND>noaudit session by appowner;

Noaudit succeeded.

SYS@IND>noaudit create session by appowner;

Noaudit succeeded.

SYS@IND>noaudit SELECT TABLE by appowner;

Noaudit succeeded.

SYS@IND>noaudit INSERT TABLE by appowner;

Noaudit succeeded.

SYS@IND>noaudit EXECUTE PROCEDURE by appowner;

Noaudit succeeded.

SYS@IND>noaudit DELETE TABLE by appowner;

Noaudit succeeded.

SYS@IND>noaudit DELETE ANY TABLE by appowner;

Noaudit succeeded.

SYS@IND>select count(*) from aud$;

  COUNT(*)
----------
        57

1 row selected.

SYS@IND>truncate table aud$;

Table truncated.

SYS@IND>sho user
USER is "SYS"
SYS@IND>

here i am truncating the sys.aud$ table.does it effect my database at any where.please suggest me....for to overcome this audit problem...shall i write any kind of object for this? i mean any trigger if the db_user!=raghav then it should delete those records from aud$.please suggest me ...

regards
sriram

Report message to a moderator

Re: AUDIT in oracle 10g [message #387635 is a reply to message #387628]

Fri, 20 February 2009 02:46

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You haven't to write anything, you just have to execute the correct audit statements.
First remove ALL audit. Check there is none. Then execute the correct audit statements and check this is this the one you really want.

Regards
Michel

Report message to a moderator

Re: AUDIT in oracle 10g [message #387681 is a reply to message #387635]

Fri, 20 February 2009 05:04

ramoradba
Messages: 2457
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

Report message to a moderator

Re: AUDIT in oracle 10g [message #387702 is a reply to message #387681]

Fri, 20 February 2009 05:53

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

yeah..i got it i realised my mistake.
thank you

regards
SRIRAM

Report message to a moderator

Previous Topic:	how to provide grant permission to other user for user defined datatype?
Next Topic:	Privileges (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 21:20:06 CST 2025