rem -----------------------------------------------------------------------
rem Filename: logintrig.sql
rem Purpose: Audit user logins and enable SQL tracing for selected users
rem Date: 09-Jul-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
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;
rem -----------------------------------------------------------------------
rem Filename: logintrig.sql
rem Purpose: Audit user logins and enable SQL tracing for selected users
rem Date: 09-Jul-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
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;