Restrict the access of using any application in Oracle

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;
/

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle