Search This Blog

Showing posts with label prevent users connect to oracle server. Show all posts
Showing posts with label prevent users connect to oracle server. Show all posts

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;
Related Posts with Thumbnails