DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2001

    production security

    I suspect ordinary users know the production password from a user with DBA privilege, bad application design I know but it is historical before I arrived on site.

    Therefore what is the best way I can identify who is logging in as this user! machine name e.g obviously v$session but I want to know if anyone has implemented anything similar. I can of course change the password but I want to catch the sneaky buggers red handed!

    Thanks in advance..

  2. #2
    Join Date
    May 2002
    Logon trigger
    Send email on logon

  3. #3
    Join Date
    Jun 2001
    Dublin, Ireland
    Do you have TOAD on your site?
    If you do you can use the kill/trace session facility to see who is logged on, which terminal they are logged on at, their o/s userid, and even what sql statements they are executing.

  4. #4
    Join Date
    Dec 2001
    Keene, NH
    This is what I use to protect my application schema from invalid logons - however if the culprit is sneaky enough to change his O/S logon on his PC to hack, I suppose he/she could - but he/she does not know enough to do so and may have problems with our local network if they change their o/s name on their pc.

    Also, even if someone hacks, it is audited in the table I create called system.xaud table.

    Hope this helps.

    CREATE OR REPLACE TRIGGER system.wmsowner_logon_trigger
    AFTER LOGON ON wms_owner.schema
    v_user VARCHAR2(240);
    CURSOR c1 IS
    SELECT osuser , sid, serial#, program as tool
    FROM sys.v_$session
    WHERE audsid=userenv('SESSIONID');
    c1_rec c1%rowtype;
    v_cnt number;
    OPEN c1;
    FETCH c1 INTO c1_rec;
    CLOSE c1;
    SELECT user INTO v_user FROM dual;
    IF lower(c1_rec.osuser) NOT IN ('oracle','oracle_app','javaapp') THEN
    INSERT INTO system.xaud(a_date, osuser, db_user, tool, SID, notes)
    VALUES (sysdate, c1_rec.osuser, v_user, c1_rec.tool, c1_rec.sid, 'LOGON Denied - Session Killed');
    RAISE_APPLICATION_ERROR(-20000,'Illegal Logon recorded in audit trail!');
    END IF;

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.