As the TOAD usuage increases , There are issues with performance of the database . We have seen that TOAD users are one of the reasons for causing performance issue. Users , I mean developers, Login to Production database using TOAD and fire N number of Queries . If the Query uses Parallelism , Then Parallel processes are linked to the users . Thus when a process requires a parallel process , Oracle is not able to allocate the necessary parallel processes as requested .. thus performance issue . Also we have seen TOAD users can use locking issue which may impact the overnight batch issues
Thus , I was asked to see how to restrict the TOAD access to the Production databases . There are two ways to do this :
- Not to allow TOAD session
- To kill the TOAD session after some interval
I think first option is the best option ....
To do this , We have to write a simple trigger ....
CREATE OR REPLACE TRIGGER block_toad
AFTER LOGON
ON DATABASE
DECLARE
--Declare a cursor to find out the program
--the user is connecting with.
CURSOR user_prog IS
SELECT program FROM v$session
WHERE audsid=sys_context('USERENV','SESSIONID');
--Assign the cursor to a PL/SQL record.
user_rec user_prog%ROWTYPE;
BEGIN
OPEN user_prog;
FETCH user_prog INTO user_rec;
IF user_rec.program IN ('TOAD.exe')
THEN
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login with TOAD');
END IF;
CLOSE user_prog;
END;
/
Similar process can be used to block other tools ...
Hope this helps DBA to manage the TOAD access to Production databases ;)
4 comments:
of course all you have to do to bypass your trigger is to rename the TOAD.exe into "TOADz.exe" or any other name... I've found this post because my team lead is on a crusade to kill all TOAD users, I'm trying to argue that we need to control user at the source by setting resources limits and consumer groups instead of trying to fight the software itself. His argument is that TOAD is for development and therefore has nothing to do on a prod database... and what happens to the trigger if the developer just moves to another piece of software...?
if you dont use DHCP, you can block the IP address ..
CREATE OR REPLACE TRIGGER block_toad
AFTER LOGON
ON DATABASE
DECLARE
--Declare a cursor to find out the program
--the user is connecting with.
ip varchar2(100) ;
CURSOR user_prog IS
SELECT program FROM v$session
WHERE audsid=sys_context('USERENV','SESSIONID');
--Assign the cursor to a PL/SQL record.
user_rec user_prog%ROWTYPE;
BEGIN
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip FROM dual;
if ip in ('192.168.2.161','192.168.2.204', '192.168.2.164') then
RAISE_APPLICATION_ERROR(-20001, 'NO ESTÁ PERMITIDO EL ACCESO AL SERVIDOR DE PRODUCCION DESDE DESARROLLO');
end if ;
OPEN user_prog;
FETCH user_prog INTO user_rec;
IF upper(user_rec.program) IN ('TOAD.EXE','TOAD')
THEN
RAISE_APPLICATION_ERROR(-20001, 'NO ESTÁ PERMITIDO EL ACCESO CON ESTA HERRAMIENTA');
END IF;
CLOSE user_prog;
END;
/
We can block by tracing the username as well, we do not bother about the application or IP address.
Oracle identity manager online training
Oracle identity manager training
Oracle performance tuning online training
Oracle performance tuning training
Oracle rac online training
Oracle rac training
Oracle SCM online training
Oracle SCM training
Oracle SOA online training
Oracle SOA training
Oracle sql plsql online training
Oracle sql plsql training
Oracle Web logic online training
Post a Comment