production security
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: production security

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    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
    Posts
    2,645
    Auditing
    Logon trigger
    Send email on logon

  3. #3
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    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
    Location
    Keene, NH
    Posts
    510
    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
    DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
    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;
    BEGIN
    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');
    COMMIT;
    RAISE_APPLICATION_ERROR(-20000,'Illegal Logon recorded in audit trail!');
    END IF;
    END;

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