trace the user

[dragon99]

Hi, all
I frequently encounter such trace file as the following:

Dump file /hubdb/app/oracle/8.1.5//admin/hubdb/udump/hubdb_ora_2383.trc
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
ORACLE_HOME = /hubdb/app/oracle/8.1.5
System name: SunOS
Node name: xenon
Release: 5.6
Version: Generic_105181-22
Machine: sun4u
Instance name: hubdb
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 2383, image: oracle@xenon (TNS V1-V3)

*** 2001.05.17.08.39.24.000
*** SESSION ID:(10.61170) 2001.05.17.08.39.24.000
Error on rollback: ORA-01031: insufficient privileges

The oracle error message 1031:
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.

I am afraid some users were trying to perform some actions they supposed not to. My question is that
Is it possible to find out the user who performed this action just based on the info recorded on trace file like the one above? Thanks for help.

Dragon

[jdorlon]

Run the script below. It will create a table on your database
and anytime someone gets an oracle error, it will be trapped
in the table. I tried to find a way to capture the SQL being
issued by the offending user but without much luck.

create table ERROR_TABLE
(
ERRORDATE DATE DEFAULT sysdate,
SID NUMBER ,
USERID VARCHAR2(30) ,
OSUSER VARCHAR2(30) ,
ERRORNO NUMBER(10, 0) ,
OBJECT_OWNER VARCHAR2(30) ,
OBJECT_NAME VARCHAR2(30) )
/

CREATE OR REPLACE TRIGGER ERRORS
AFTER SERVERERROR ON DATABASE
begin
insert into error_table
select sysdate, SID, username, osuser,
ora_server_error(1), ora_dict_obj_name,
ora_dict_obj_owner
from v$session s
where s.AUDSID= userenv('SESSIONID');
end;
/

[dragon99]

Thanks very much, Jdorlon
I tried to run the script you provided. Could you please tell me where I get the following values from:
ora_server_error(1), ora_dict_obj_name,
ora_dict_obj_owner

Thanks.

Dragon

[jdorlon]

Read this, it will answer your questions, I think.

http://www.oradoc.com/ora817/appdev....evt.htm#998000

-John