trace the user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: trace the user

  1. #1
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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


  2. #2
    Join Date
    Nov 2000
    Posts
    344
    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;
    /

  3. #3
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    Read this, it will answer your questions, I think.

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

    -John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width