Home » RDBMS Server » Security » trace or audit user activity
trace or audit user activity [message #534870] Fri, 09 December 2011 16:25 Go to next message
patdev
Messages: 73
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
Re: trace or audit user activity [message #534874 is a reply to message #534870] Fri, 09 December 2011 17:13 Go to previous messageGo to next message
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
Re: trace or audit user activity [message #534888 is a reply to message #534870] Fri, 09 December 2011 23:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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
Re: trace or audit user activity [message #535154 is a reply to message #534874] Mon, 12 December 2011 07:27 Go to previous messageGo to next message
patdev
Messages: 73
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
Re: trace or audit user activity [message #535155 is a reply to message #535154] Mon, 12 December 2011 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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
Re: trace or audit user activity [message #535159 is a reply to message #535155] Mon, 12 December 2011 08:11 Go to previous messageGo to next message
patdev
Messages: 73
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
Re: trace or audit user activity [message #535161 is a reply to message #535159] Mon, 12 December 2011 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 Go to previous message
John Watson
Messages: 8922
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:
  


Current Time: Thu Mar 28 05:38:20 CDT 2024