How can you prevent the users logging in through�tools like TOAD or SQL Navigator or PLSQL Developer..?

Connect as sydba
Write a LOGON trigger like this

� � �CREATE OR REPLACE TRIGGER RESTRICT_TOOL
�AFTER LOGON ON DATABASE
DECLARE
� �L_TOOL �V$SESSION.PROGRAM%TYPE;

BEGIN
�SELECT PROGRAM
�INTO L_TOOL
�FROM V$SESSION
�WHERE �AUDSID = USERENV('SESSIONID')
� �AND �AUDSID != (SELECT AUDSID
� � � � � � � � � �FROM V$SESSION
� � � � � � � � � �WHERE USERNAME='SYS');

�IF UPPER(L_TOOL) LIKE '%TOAD%'
�OR UPPER(L_TOOL) LIKE '%SQLNAV%'
�OR UPPER(L_TOOL) LIKE '%PLSQLDEV%' THEN
� � RAISE_APPLICATION_ERROR(-20008, 'You are not authorized to access !');
�END IF;
END;

Interview Candidate
Aug 6th, 2005
3
3821

Showing Answers 1 - 3 of 3 Answers

Dinesh G

Aug 6th, 2005

Above shown trigger won't work if user system. Same will happen if any use opts the as sysdba option.

Is there any way to close these loop holes ?

Laxmi Kant Sharma

Aug 30th, 2007

This solution is not working properly.. because it is not raised after logon. so there is no use of raise_application_error after login. Is there any other method for that?