• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » How to use Oracle logon trigger to stop login from Particular program

How to use Oracle logon trigger to stop login from Particular program

September 25, 2020 by techgoeasy Leave a Comment


Table of Contents

  • What is Oracle Logon Triggers
  • how to check logon triggers in oracle
  • Oracle logon trigger  which can be used to stop login from particular program and user
  • Oracle logon trigger  which can be used to stop login from particular hostname and OS user
  • Oracle logon trigger  which can be used to trace the activity of a specific user
  • How to disable the oracle logon triggers  or system triggers

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


Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1
  • Step by step upgrade process for R12.2 Upgrade Part -4(Applying 12.2.x Release Update Pack)

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us