Home » RDBMS Server » Security » Query Regarding AUDIT_TRAIL (Oracle 9.2.0.1.0, RHAS 2.1)
Query Regarding AUDIT_TRAIL [message #496291] Fri, 25 February 2011 22:59 Go to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Hi Friends,

I would like get select,insert,update commands in particular text file for my one of the schema. if i am enabling audit_trail=os in init.ora and issue
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY SCOTT;

Should i get above statements in text file.

Actually I would like to get all the select,insert,update statements for my particular schema. is there any way to get it from instance or log. is it possible without audit_trail? if you know another way please let me know.

Thanks in Advance,
Chintan

[Updated on: Sat, 26 February 2011 00:54]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496298 is a reply to message #496291] Sat, 26 February 2011 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What happenned when you tried?

Regards
Michel

[Updated on: Sat, 26 February 2011 00:53]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496299 is a reply to message #496298] Sat, 26 February 2011 01:01 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
I tried it by both the ways by audit_trail DB and OS. but i cant find any sql statements.

chintan
Re: Query Regarding AUDIT_TRAIL [message #496300 is a reply to message #496299] Sat, 26 February 2011 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works for me:
MICHEL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY SCOTT;

Audit succeeded.

MICHEL> connect scott/tiger
Connected.
SCOTT> select count(*) from emp;
  COUNT(*)
----------
        14

1 row selected.

SCOTT> connect michel/michel
Connected.

MICHEL> select extended_timestamp from dba_audit_trail where username='SCOTT' and obj_name='EMP';
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
26/02/2011 08:14:05.946 +01:00

Regards
Michel

[Updated on: Sat, 26 February 2011 01:18]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496301 is a reply to message #496300] Sat, 26 February 2011 01:25 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks friend

but my requirement is whole statement.
select count(*) from emp;


Chintan
Re: Query Regarding AUDIT_TRAIL [message #496302 is a reply to message #496301] Sat, 26 February 2011 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your audit_trail parameter must be either db or xml and contain the "extended" option.

Regards
Michel

[Updated on: Sat, 26 February 2011 01:33]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496304 is a reply to message #496302] Sat, 26 February 2011 01:36 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Dear Michel

It will work on 9.2.0.1.0

Chintan
Re: Query Regarding AUDIT_TRAIL [message #496306 is a reply to message #496304] Sat, 26 February 2011 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use FGA, not possible in other in this version (or activate trace for all SCOTT sessions).

Regards
Michel

[Updated on: Sat, 26 February 2011 01:50]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496311 is a reply to message #496306] Sat, 26 February 2011 04:47 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Michel

As you say i am enable sql_trace=true in my init.ora, now the problem is that its generates so many unnecessary queries by system. its also creates so many .trc files. it is possible to creates only on trace file and bypass all unnecessary queries, herewith i paste the command.

tkprof ora_16600.trc temp.txt SYS=NO INSERT=temp.sql AGGREGATE=NO


Chintan
Re: Query Regarding AUDIT_TRAIL [message #496312 is a reply to message #496311] Sat, 26 February 2011 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Do not enable trace at instance level for ALL sessions, only for the user you want using a logon trigger
2/ You have a trace file per session
3/ You cannot trace only some statements

Regards
Michel
Re: Query Regarding AUDIT_TRAIL [message #496313 is a reply to message #496312] Sat, 26 February 2011 05:34 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks

Actually my plan is to to enable sql_trace=true run web based application, collect all select,insert,update,delete statements, and run all that statements on another server. so i have to get only those statements which are run by application only.

Chintan
Re: Query Regarding AUDIT_TRAIL [message #496314 is a reply to message #496313] Sat, 26 February 2011 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you then put a trace inside the application? Isn't it the correct place to get all statements executed by the application?

Regards
Michel
Re: Query Regarding AUDIT_TRAIL [message #496431 is a reply to message #496314] Mon, 28 February 2011 04:14 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks michel

i am finding the way from sql_trace. by the use of tkprof i am collecting all the statements into to one table and then i differentiate all the statements with use of user_id. now i would like to know another thing that, which criteria used by oracle to create .trc files because its generate so many .trc and i have to tkprof unnecessary files which is not containing related schema statements.

Regards
Chintan
Re: Query Regarding AUDIT_TRAIL [message #496432 is a reply to message #496431] Mon, 28 February 2011 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1 file per session being traced.
Re: Query Regarding AUDIT_TRAIL [message #496435 is a reply to message #496432] Mon, 28 February 2011 04:25 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks michel

Actually i would like to get the sequence of statement execution.
can i get from .trc files?


Regards
Chintan
Re: Query Regarding AUDIT_TRAIL [message #496436 is a reply to message #496431] Mon, 28 February 2011 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2/ You have a trace file per session


Process id is in the file trace name.
You can set "tracefile_identifier" session parameter if you want something more explicit for you but this must be done BEFORE setting the trace on, so this means you have to do it in a logon trigger not at instance level.

Regards
Michel

[Updated on: Mon, 28 February 2011 04:26]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496443 is a reply to message #496436] Mon, 28 February 2011 04:50 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Michel

Can i use sid_ora_pid.trc as a ascending sequence of statements which it contains?

Regards
Chintan
Re: Query Regarding AUDIT_TRAIL [message #496449 is a reply to message #496443] Mon, 28 February 2011 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With sid as instance id and not session id, and for 9i and Linux, you can rely on this name, I think.
But, from Oracle point of view, you cannot rely on an order in pid; this is a Linux question.

Regards
Michel
Re: Query Regarding AUDIT_TRAIL [message #496452 is a reply to message #496449] Mon, 28 February 2011 05:22 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Michel,

Do you have any other idea regarding this, i just want to get statements in ascending sequence of execution from .trc file. how can it get it?

Regards
Chintan
Re: Query Regarding AUDIT_TRAIL [message #496457 is a reply to message #496452] Mon, 28 February 2011 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Get them in ascending order of creation date which of course give no indication of statements execution order.
For this you have to read the file and statement operations timestamps.

Regards
Michel

[Updated on: Mon, 28 February 2011 05:41]

Report message to a moderator

Re: Query Regarding AUDIT_TRAIL [message #496458 is a reply to message #496457] Mon, 28 February 2011 05:42 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Are you sure and confident? it its works. my problem will be solved.

Thanks for supporting
Chintan
Re: Query Regarding AUDIT_TRAIL [message #496460 is a reply to message #496458] Mon, 28 February 2011 05:46 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Dear michel

i will try as creation order and then let you know for future reference.

Chintan
Re: Query Regarding AUDIT_TRAIL [message #496461 is a reply to message #496458] Mon, 28 February 2011 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The file creation time is sure as it gives the time when the session started (if you enable the trace from instance parameter or logon trigger).
If you can't rely on system file timestamp, you can order them by the second line of the file which gives when Oracle creates the trace file.

Regards
Michel
Re: Query Regarding AUDIT_TRAIL [message #496466 is a reply to message #496461] Mon, 28 February 2011 05:59 Go to previous message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Dear michel

As you say i am sending snap of my .trc file listing with the command of "ll -ltrh".
[oracle@Test udump]$ ll -ltrh
-rw-r-----    1 oracle1  oinstall     4.4k Feb 28 12:46 test_ora_3800.trc
[b]
-rw-r-----    1 oracle1  oinstall     4.2k Feb 28 12:46 test_ora_3806.trc
-rw-r-----    1 oracle1  oinstall     9.7k Feb 28 12:46 test_ora_3804.trc
-rw-r-----    1 oracle1  oinstall     285k Feb 28 12:46 test_ora_3802.trc
[/b]
-rw-r-----    1 oracle1  oinstall     117k Feb 28 12:46 test_ora_3779.trc
-rw-r-----    1 oracle1  oinstall     100k Feb 28 12:47 test_ora_3813.trc
-rw-r-----    1 oracle1  oinstall      712 Feb 28 12:48 test_ora_3892.trc
[oracle1@Test udump]$


As i observe some times older process id with newest timestamp. in this case you are right, i have to depend on creation time stamp instead of process id sequence.

Regards
Chintan
Previous Topic: how to hide password in script
Next Topic: How To Know Which Session Causing A TX Lock
Goto Forum:
  


Current Time: Thu Mar 28 13:32:36 CDT 2024