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;
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;
This comment has been removed by the author.
ReplyDeleteDear,
ReplyDeleteReally, the code is very helpful but i need your help to modify something in it. How can i contact you?