How prevent any user from accessing specific schema through specific tools
761559Jun 16 2011 — edited Jul 18 2011Dears , we need to prevent any user from accessing specific schema through specific tools
As example, any user except aaa,bbb,ccc cannot access HR schema through toad or SQl Navigator or sql plus
I created below trigger but it not working well, still other users can connect to HR schema.
CREATE OR REPLACE TRIGGER HR.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
( -- Not allowed Tools
UPPER(rec_session.program) LIKE '%TOAD%'
OR UPPER(rec_session.program) LIKE '%SQL%NAV%'
OR UPPER(rec_session.program) LIKE '%SQL%PLUS%'
)
AND
( -- Only Permitted Users
UPPER(rec_session.OSUSER) NOT LIKE '%AAA%'
OR UPPER(rec_session.OSUSER) NOT LIKE '%BBB%'
OR UPPER(rec_session.OSUSER) NOT LIKE '%CCC%'
)
-- Protected Schema
AND UPPER(rec_session.SCHEMANAME) LIKE 'HR'
THEN
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');
END IF;
CLOSE cur_session;
END;
Please, help