How to block an application for example TOAD in oracle
---DIRECTORY CREATION
CREATE OR REPLACE DIRECTORY ADMIN as '/apps/app/oracle/product/9201/network/log/' ;
-in the above command you need to replace the path with your listener.log location
---EXTERNAL TABLE CREATION
create table application_check
( logon_date varchar2(100),
program varchar2(400),
protocol varchar2(100),
host_name varchar2(15))
organization external
(type oracle_loader
default directory admin
access parameters
(records delimited by newline
badfile 'test.bad'
discardfile 'test.dis'
logfile 'test.log'
fields terminated by "*"
(
LOGON_DATE CHAR,
PROGRAM CHAR,
PROTOCOL CHAR,
HOST_NAME CHAR)
)
LOCATION ('listener.log')
)
REJECT LIMIT UNLIMITED;
---VIEW CREATION
create or replace view application_view AS
select substr(program,44,65) program ,
substr(protocol,31,15) ipaddress,
TO_DATE(SUBSTR(LOGON_DATE,1,19),'DD-MON-YYYY HH24:MI:SS') LOGON_DATE
FROM application_check
where program is not null
and LOGON_DATE not like '%TIMESTAMP%';
--
create or replace trigger application_login_check
after logon on database
declare
x number;
begin
select count(1) into x
from dotsoft_application_view a
where (upper(a.program) like '%TOAD%'
or upper(a.program) like '%IFBLD%'
or upper(a.program) like '%IFBLD%'
or upper(a.program) like '%RWBLD%'
or upper(a.program) like '%DLLHOST%')
and (sysdate-a.logon_date)*24*60*60 < 60;
if x>0 then
raise_application_error(-20001,'You are not authorized');
end if;
end;
/
Tags:
Oracle