How to block an application for example TOAD in oracle

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;

/

Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post