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

Last updated on May 12th, 2019 at 03:43 am

Here is the 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
/