OraFAQ Forum: Security » trace or audit user activity

Home » RDBMS Server » Security » trace or audit user activity

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

trace or audit user activity [message #534870]

Fri, 09 December 2011 16:25

patdev
Messages: 71
Registered: August 2008

Member

Hi,

i want to trace user activities, I want to generate the file with SQL statement generated by particular user. I tried to look in to sys.AUD$ but all i get is loging logout and locations but no SQL Text. How can i get that!!

thanks

Report message to a moderator

Re: trace or audit user activity [message #534874 is a reply to message #534870]

Fri, 09 December 2011 17:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

enable SQL_TRACE by below
DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE,TRUE,'ALL_EXECUTIONS');
process trace file with tkprof

Report message to a moderator

Re: trace or audit user activity [message #534888 is a reply to message #534870]

Fri, 09 December 2011 23:52

Michel Cadot
Messages: 68352
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I tried to look in to sys.AUD$ but all i get is loging logout and locations but no SQL Text

Audit trail contains only what you asked Oracle to audit.

Regards
Michel

Report message to a moderator

Re: trace or audit user activity [message #535154 is a reply to message #534874]

Mon, 12 December 2011 07:27

patdev
Messages: 71
Registered: August 2008

Member

Hi,

If i execute by sys dba right, does it include other session too and connecting new session such as usera, userb, and so on? or just sys

please guide.

Thanks

Report message to a moderator

Re: trace or audit user activity [message #535155 is a reply to message #535154]

Mon, 12 December 2011 07:31

Michel Cadot
Messages: 68352
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please reply to the questions.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.

Regards
Michel

Report message to a moderator

Re: trace or audit user activity [message #535159 is a reply to message #535155]

Mon, 12 December 2011 08:11

patdev
Messages: 71
Registered: August 2008

Member

but if i try to execute exec DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE,TRUE,'ALL_EXECUTIONS') it give error for insufficient privileges for the user i want to trace or audit!!

Thanks

Report message to a moderator

Re: trace or audit user activity [message #535161 is a reply to message #535159]

Mon, 12 December 2011 08:19

Michel Cadot
Messages: 68352
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Ask your DBA to give the user the privilege to execute it!

Regards
Michel

[Updated on: Mon, 12 December 2011 08:19]

Report message to a moderator

Re: trace or audit user activity [message #535172 is a reply to message #534870]

Mon, 12 December 2011 09:41

John Watson
Messages: 8795
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I want to generate the file with SQL statement generated by particular user. I

This sounds as though you need to enable extended auditing, which includes the SQL statements. If you want it to go to a file, your only option is
ALTER SYSTEM SET AUDIT_TRAIL=XML,EXTENDED;
and then use an XML viewer. So it is probably better to write the records to the sys.aud$ table with
ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED;
and then enable the auditing with
AUDIT ALL STATEMENTS BY whomever;
Time you read the security guide, http://www.oracle.com/pls/db112/to_toc?pathname=network.112%2Fe16543%2Ftoc.htm&remark=portal+%28Books%29

[Updated on: Mon, 12 December 2011 09:42]

Report message to a moderator

Previous Topic:	audit problem
Next Topic:	Syslog Audit Trail format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 08 01:32:19 CST 2023