Monday, March 9, 2015

Trigger to detect failed logins in oracle database

DROP TRIGGER SYS.HATA_TRIG_PREVENT_SQL_TOOLS;

CREATE OR REPLACE TRIGGER SYS.HATA_TRIG_Prevent_SQL_TOOLS
AFTER LOGON ON DATABASE
DISABLE
declare
v_user varchar2(30):=user;
v_program varchar2(100);
v_exists number(1) := 0;
 begin
select upper(b.program), upper(osuser) into v_program,v_user
from sys.v_$session b
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections;
AND rownum = 1;

begin
select 1 into v_exists
from hataopsadm.ACCESS_TO_SQL_TOOL
where OS_ID = v_user;
exception
when no_data_found then
  v_exists := 0;
end;

   if  v_exists = 0 and (UPPER(v_program) LIKE '%TOAD%' OR UPPER(v_program) LIKE '%T.O.A.D%' OR -- Toad
    UPPER(v_program) LIKE '%SQL DEVELOPER%' OR     -- SQL Developer
UPPER(v_program) LIKE '%SQLNAV%' OR     -- SQL Navigator
UPPER(v_program) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_program) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_program) LIKE '%EXCEL%') -- MS-Excel plug-in
THEN
        raise_application_error(-20000, 'SQL tool with specified access is not allowed !!!');
   end if;
end;

No comments:

Post a Comment

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...