Hi,
I need to control the applications and tools like Toad for Oracle connecting to the database. So i created an AFTER LOGON ON DATABASE trigger.
when i login first time through Toad it will allow me to login to the database but when i tried to login again by keeping the first one without logging out it will not allow , I need to completely block Toad connecting to database for selected systems. Can any one please help me to solve this challenge.
Below is the trigger.
CREATE OR REPLACE TRIGGER SYS.BLOCK_TOOLS
AFTER LOGON ON DATABASE
DECLARE
V_PROG SYS.V_$SESSION.PROGRAM%TYPE;
MY_FORCED_EXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);
CNT NUMBER:=0;
BEGIN
SELECT NVL(COUNT(*),0) INTO CNT FROM(
SELECT DISTINCT USERNAME,TERMINAL,MODULE,PROGRAM,SERVICE_NAME,LOCKYN,
UPPER(MACHINE_NAME),MACHINE_IP
FROM SYS.V_$SESSION,USER_LOCK
WHERE USERNAME IS NOT NULL
AND TERMINAL = SYS_CONTEXT('USERENV','TERMINAL')
AND MACHINE_IP = UTL_INADDR.get_host_address(TERMINAL)
AND LOCKYN = 'Y'
AND 'TOAD' = SUBSTR(MODULE,0,4));
IF CNT > 0 THEN
RAISE my_forced_exception;
END IF;
EXCEPTION
WHEN MY_FORCED_EXCEPTION THEN
RAISE_APPLICATION_ERROR(-20101, 'Action not allowed. Please contact your DBA to help you!');
WHEN OTHERS THEN
NULL;
END;
/
------------------------------------------------
USER_LOCK is the table created by me to keep track of the systems which needs to block and script for the table is
CREATE TABLE SYS.USER_LOCK
(
MACHINE_NAME VARCHAR2(25 BYTE),
MACHINE_IP VARCHAR2(20 BYTE),
LOCKYN CHAR(1 BYTE)
)
where MACHINE_NAME is the name of the machine and MACHINE_IP is the IP address of the same system and LOCKYN is the status of the system whether it is locked or not ( Y or N);