how to restrict alter session
We have an application that connects to an Oracle 10g database. The application is a third party app that connects to the database using one standard logon. The problem is that anyone connecting with this application can see all the same schemas as the next person. I started experimenting with logon triggers on the database and schema. My goal is to take the OSUSER name and based on the user allow them to connect/restrict them from certain schemas. I wrote the trigger below which seems to work fine when the user first connects but if a user logs onto a schema and then alters his current schema using "alter session set current_schema" then the trigger is not called and the user can freely switch his schema. Is there a way to make the trigger fire whenever someone tries to logon or switch the current schema?
create or replace trigger user_logon
after logon on mdauria.schema
SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO sOS_User FROM DUAL;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM valid_users where OS_USER = ''' || sOS_User || '''' INTO
if nRowcount > 0 then
EXECUTE IMMEDIATE 'SELECT DEFAULT_SCHEMA from valid_users where OS_USER = ''' || sOS_User || ''''
EXECUTE IMMEDIATE 'alter session set current_schema = ' || sDef_Schema;