CoderZone.org

Category: >> Oracle PL/SQL >> Audit User Logins (User Login Trigger) Bookmark and Share

<< lastnext >>

Snippet Name: Audit User Logins (User Login Trigger)

Description: Audit user logins and enable SQL tracing for selected users.

Audit User Logins and start traces for selected users (User Login Trigger)

Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» Database Links: CURRENT_USER
» Instant Test Database with DCBA
» Show info on current context
» Lookup Oracle error messages
» Display and release DBMS_LOCK locks
» Display locks and latches
» Show rollback segment stats
» Show active transactions
» List supported INIT.ORA parameters
» Display database SGA statistics
» Measure the Buffer Cache Hit Ratio
» List security related profile info...
» Find users with deadly privileges
» Block TOAD and other tools
» Kill Session
» Extents
» DBA Users
» DBA Tablespaces
» DBA triggers
» DBA Sessions
» DBA Roles
» DBA Objects
» DBA Links
» DBA Jobs
» Job Queue
» DBA Free Space
» Data Files
» DBA Extents

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

CONNECT / AS SYSDBA
 
DROP TABLE user_login_audit;
 
CREATE TABLE user_login_audit (
     login_time     DATE,
     username        VARCHAR2(30),
     machine          VARCHAR2(30),
        command          VARCHAR2(128)
);
 
CREATE OR REPLACE TRIGGER user_login_trig
     AFTER LOGON ON scott.SCHEMA
DECLARE
  v_username sys.v_$session.username%TYPE;
  v_machine  sys.v_$session.machine%TYPE;
  v_command  sys.v_$session.command%TYPE;
BEGIN
  SELECT username, machine, command 
    INTO v_username, v_machine, v_command
    FROM sys.v_$session 
   WHERE audsid = USERENV('SESSIONID')
     AND audsid != 0  -- Don't Check SYS Connections
     AND ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  INSERT INTO sys.user_login_audit 
     VALUES (SYSDATE, v_username, v_machine, v_command);
 
  IF UPPER(v_machine) LIKE '%PC1%' THEN          -- Start SQL trace for users from PC1
     DBMS_SESSION.set_sql_trace(TRUE);
  END IF;
END;
/
SHOW ERRORS
 
CONNECT SCOTT/TIGER
 
CONNECT / AS SYSDBA
 
SELECT * FROM sys.user_login_audit;
 
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 8