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
  • 4
  • 3610

Showing Answers 1 - 4 of 4 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 ?

  Was this answer useful?  Yes

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?

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions