Home » RDBMS Server » Security » How to trace activities of all users on the database (HPUX,oracle 9i)
How to trace activities of all users on the database [message #360960] Mon, 24 November 2008 08:08 Go to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
Hi,

I need to trace all the users activities on the database level and need to log those data in a file..
as far as all documents, I have found

SQL> alter system set SQL_TRACE=TRUE scope=spfile;



SQL>shutdown immediate

SQL> startup

When I run above queries,I find the logs but I am not able find which user has performed which action. Can I know the method to trace the username...

Re: How to trace activities of all users on the database [message #360971 is a reply to message #360960] Mon, 24 November 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is in the trace file (maybe as user id and not name in your version) but it is there.

Regards
Michel
Re: How to trace activities of all users on the database [message #361042 is a reply to message #360971] Mon, 24 November 2008 23:19 Go to previous messageGo to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
Sorry, I couldn't get you.. can you please explain clearly..
Re: How to trace activities of all users on the database [message #361090 is a reply to message #361042] Tue, 25 November 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't put it clearer.
See your trace files.

Regards
Michel
Re: How to trace activities of all users on the database [message #361096 is a reply to message #361090] Tue, 25 November 2008 00:49 Go to previous messageGo to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
Herre is my trace file., can you please let me know where is the userid. Please

--------------------------

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /oracle20/app/oracle/product/9.2
System name: HP-UX
Node name: NETMAN3
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: dbtest1
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 14562, image: oracle@server1 (TNS V1-V3)

*** SESSION ID:(7.499) 2008-11-25 06:37:11.184
=====================
PARSING IN CURSOR #1 len=475 dep=1 uid=0 oct=42 lid=0 tim=3586380332394 hv=71214
0972 ad='33853b60'
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY
= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GR
EGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= '
BINARY' TIME_ZONE= '+05:30' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT = 'HH.MI.SSX
FF AM' NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT = 'H
H.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM TZR'
END OF STMT
PARSE #1:c=0,e=420,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3586380332340
EXEC #1:c=0,e=1189,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3586380395440
=====================
PARSING IN CURSOR #1 len=6 dep=0 uid=0 oct=44 lid=0 tim=3586381929648 hv=2425872
81 ad='33b3e330'
commit
END OF STMT
PARSE #1:c=0,e=350,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3586381929587
XCTEND rlbk=0, rd_only=1
EXEC #1:c=0,e=441,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3586382245198
*** 2008-11-25 06:41:07.413
XCTEND rlbk=0, rd_only=1

---------------------
Re: How to trace activities of all users on the database [message #361100 is a reply to message #361096] Tue, 25 November 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
uid=0 => SYS

Regards
Michel
Re: How to trace activities of all users on the database [message #361106 is a reply to message #361100] Tue, 25 November 2008 01:23 Go to previous messageGo to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
=====================
PARSING IN CURSOR #1 len=6 dep=0 uid=21 oct=44 lid=21 tim=3588934980014 hv=24258
7281 ad='57826160'
commit
END OF STMT
PARSE #1:c=0,e=712,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3588934979972
XCTEND rlbk=0, rd_only=0
EXEC #1:c=0,e=654,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=3588934981427
*** 2008-11-25 07:21:14.990
XCTEND rlbk=0, rd_only=1


Now I have the uid=21. where can I found what is the username for this userid ?

Any views...
Please..
Re: How to trace activities of all users on the database [message #361119 is a reply to message #361106] Tue, 25 November 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBA_USERS

Regards
Michel
Re: How to trace activities of all users on the database [message #361131 is a reply to message #361119] Tue, 25 November 2008 02:20 Go to previous messageGo to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
Thanks. its works..
Re: How to trace activities of all users on the database [message #361141 is a reply to message #361131] Tue, 25 November 2008 03:01 Go to previous messageGo to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
Mike,

I need a help from you again...

As of now, the trace files are going to below destination

/oracle20/app/oracle/admin/testdb1/udump

and this path is default path where oracle traces the user and generates the traces..

Once i give sql_trace=true, I need to differentiate the enabled traces and default traces of the oracle..

Is it possible to redirect the trace files that are generated by sql_trace to a new location..
Re: How to trace activities of all users on the database [message #361151 is a reply to message #361141] Tue, 25 November 2008 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't have a distinct value, both are user trace files for Oracle.

Regards
Michel
Re: How to trace activities of all users on the database [message #361155 is a reply to message #361151] Tue, 25 November 2008 03:54 Go to previous messageGo to next message
sanei05
Messages: 103
Registered: September 2008
Senior Member
oh..
I required a distinct value of the udump because, I need to schedule a job for removing the files every 7 days.
I tried auditing too. But I am not able to find the accurate details of the what actions the users are performing...

Re: How to trace activities of all users on the database [message #361180 is a reply to message #361155] Tue, 25 November 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to schedule a job for removing the files every 7 days.

You need to have a smart purge script.

Regards
Michel
Re: How to trace activities of all users on the database [message #361191 is a reply to message #361180] Tue, 25 November 2008 05:42 Go to previous message
sanei05
Messages: 103
Registered: September 2008
Senior Member
yes.. i am trying on that...
also I need to try on auditing too..
Please share your ideas if you have any....

I am just trying auditing now and let me see how this work... The only thing with auditing is that I cannot make the output to be viewed by the non-DBA personnel...

But in sql_trace,, i can make non-dba to view the files...
so i am going for sql_trace instead auditing..
Previous Topic: Problem on Job
Next Topic: Grant privilege to "user b" for object in schema "user a"
Goto Forum:
  


Current Time: Thu Dec 08 14:39:38 CST 2016

Total time taken to generate the page: 0.11178 seconds