Trigger to restrict alter session

[Michael Dauria]

We have a third party application that connects to an oracle database using a generic login. This application has a connection dialog that allows you to select a schema to connect to and I want to restrict some people from using some of those schemas. I created the trigger below which fires when someone first logons to the foo schema, it checks a table that I have created and if they are in the table then it alters their session to the one I have specified in the table. I have found that if someone switches schemas, then this doesn't fire. If I logon to fooey schema and then so an "alter session set current_schema = foo" then the trigger is not fired. I assume since I am not loging into a schema but merely switching schema that this is why it doesn't fire. How do I get this to fire when someone switches schemas? Is this the wrong trigger type? I looked at the list of triggers that I could create but didn't see anything that looked correct.

create or replace trigger user_logon
after logon on foo.schema
declare
nRowCount number;
sDef_Schema varchar(64);
sOS_User varchar(64);
BEGIN
SELECT SYS_CONTEXT('US ERENV', 'OS_USER') INTO sOS_User FROM DUAL;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM valid_users where OS_USER = ''' || sOS_User || '''' INTO

nRowCount;
if nRowcount > 0 then
EXECUTE IMMEDIATE 'SELECT DEFAULT_SCHEMA from valid_users where OS_USER = ''' || sOS_User || ''''

INTO sDef_Schema;
EXECUTE IMMEDIATE 'alter session set current_schema = ' || sDef_Schema;
end if;
END;

[amitpatel66]

Instead of handling and performing this validation at database level, why dont to restrict users by controlling the schema list before they login in front end application? The drop down for X user should show only those schema to which it has right to login in to. For user Y, it should should specific list of schema that it can access.

[Michael Dauria]

Unfortunately this is a third party app, so I don't have control to restrict what schemas are show.

Thinking about this more, is there a way for a logon trigger to execute a set role? The app uses a generic login to oracle but in the trigger I can see the OSUSER who is trying to connect. In sqlplus I can alter the roles and restrict what schemas this app sees. I understand that there is a set role that will set the role of the session but from all the info that I am finding it looks like you cannot do this from within a logon trigger. Is this true for 10g?