Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How prevent any user from accessing specific schema through specific tools

761559Jun 16 2011 — edited Jul 18 2011
Dears , 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2011
Added on Jun 16 2011
34 comments
1,367 views