Script of Logon Trigger for restrict the access of application with Oracle User.
AS an example, following script show the restrict of SQLPLUS.EXE application to access for normal user. I left the DBA user , DBA user can use it. But other than dba user get error while using by help of LOGON TRIGGER.
CREATE OR REPLACE TRIGGER stop_app_on_logon
AFTER LOGON ON DATABASE
Declare
var_sid number;
var_program varchar2(50);
var_isdba varchar2(20);
begin
--Check user is sysdba or normal
select sys_context('userenv','ISDBA') into var_isdba from dual;
--Get the current SID
execute immediate 'select distinct sid from sys.v_$mystat' into var_sid;
--With help of SID get the program name
execute immediate
'select program from sys.v_$session where sid = :b1'
into var_program using var_sid;
--Restrict the program you want to in example i used SQLPLUS.EXE
if upper(var_program) = upper('SQLPLUS.exe') and var_isdba = 'FALSE' then
raise_application_error (-20001,'Access for non DBA users restricted',TRUE);
end if;
END;
/