Search This Blog

Tuesday, August 3, 2010

How to prevent users using TOAD connecting to Oracle server from their client

This is a discussion on How to prevent users using TOAD connecting to Oracle server from their client; Because of your database security, you don't want some client login to Oracle server using TOAD(or others software). This show how to create a system trigger to prevent the connection as you need:

CREATE OR REPLACE TRIGGER prevent_users_login AFTER LOGON ON DATABASE
DECLARE
    CURSOR cur_session IS
        SELECT PROGRAM, USERNAME, SCHEMANAME, OSUSER FROM v$session
        WHERE audsid=sys_context('USERENV','SESSIONID');
    rec_session cur_session%ROWTYPE;
BEGIN
    OPEN cur_session;
    FETCH cur_session INTO rec_session;
    IF SUBSTR(rec_session.program,1,4) = 'TOAD'
        AND rec_session.OSUSER IN ('user_a', 'user_b', 'user_c')
        AND rec_session.SCHEMANAME LIKE 'XYZ%'
    THEN
       
        RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');
    END IF;
    CLOSE cur_session;
END;

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Dear,

    Really, the code is very helpful but i need your help to modify something in it. How can i contact you?

    ReplyDelete

leave your message if you need help ...

Related Posts with Thumbnails