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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.