Useful queries on Oracle Logon Triggers with examples

Table of Contents

What is Oracle Logon Triggers

A trigger is called Oracle logon trigger if it is associated with logon action on triggering event. This is system triggers

how to check logon triggers in oracle

select * from dba_triggers where triggering_event like '%LOGON%';

Example

SQL> Set lines 200
SQL> column owner format a10
SQL> column TRIGGER_NAME format a15
SQL> column TRIGGERING_EVENT format a15
SQL> column TRIGGER_TYPE format a15
SQL> column STATUS format a15
SQL>select owner, TRIGGER_NAME,TRIGGERING_EVENT,TRIGGER_TYPE,STATUS from dba_triggers where triggering_event like '%LOGON%';

Oracle logon trigger which can be used to stop login from particular program and user

CREATE OR REPLACE TRIGGER program_restrict
AFTER LOGON ON DATABASE
BEGIN
FOR x IN (SELECT username, program
FROM SYS.v_$session
WHERE audsid = USERENV (‘sessionid’))
LOOP
IF LTRIM (RTRIM (x.username)) = ‘TEST’
AND LTRIM (RTRIM (x.program)) IN (‘sqlplusw.exe’,‘TOAD.exe’)
THEN
raise_application_error
(-20999,‘Not authorized to use in the Production
environment!’);
END IF;
END LOOP;
END program_restrict
/

Oracle logon trigger which can be used to stop login from particular hostname and OS user

CREATE OR REPLACE TRIGGER SYSTEM.LOGON_DENY
AFTER LOGON
ON DATABASE
declare
OSUSER varchar2 (200);
HOSTNAME varchar2 (200);
begin
select sys_context ('USERENV', 'OS_USER') into OSUSER from dual;
select sys_context ('USERENV', 'HOST') into HOSTNAME from dual;
if sys_context('USERENV','SESSION_USER')in ('HR','SCOTT','TECH')
and sys_context ('USERENV', 'HOST') in ('TECH_USER1','TECH_USER2')
then
raise_application_error(-20001,'Denied! You are not allowed to logon from host '||HOSTNAME|| ' using '|| OSUSER);
end if;
end;
/

Oracle logon trigger which can be used to trace the activity of a specific user

CREATE OR REPLACE TRIGGER set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USE')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

How to disable the oracle logon triggers or system triggers

When _SYSTEM_TRIG_ENABLED is set to TRUE (the default) then system triggers are enabled. So if set this parameter to false and bounce the database then logon triggers or system trigger will not fire

alter system set "_SYSTEM_TRIG_ENABLED" = false scope=spfile;
shutdown immediate
startup

Hope you like these useful queries on Oracle logon triggers. Please do provide the feedback

Related Articles
How to check Trigger status in Oracle : Check out How to check Trigger status in Oracle, how to enable/disable trigger, how to find the trigger definition, how to find all the triggers
SQL trace : SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
oracle dba scripts : Top oracle dba scripts for Oracle Database for monitoring purpose ,how to see the waits events of the sessions and check the last analyzed
Create User in Oracle : This page contain lot of useful information on Oracle Object Privileges ,Create user,system Privileges, How to show all privileges from a user
https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm#TDDDG52600