Re: Simple Q re schemas and users

Pete, Ed, Daniel, and Chris,

thank you all for your advice. I just wanted to let you know how I decided to solve my problem:

I set the current schema for each user to the MASTER schema whenever they logon. This overshadows any data in the user's own schema, but that is no problem in my case, as all data resides in the MASTER schema.

The logon trigger is created like this (directly after creating the user named username):

EXECUTE IMMEDIATE 'CREATE TRIGGER ' || username ||

                        '.use_schema_master AFTER LOGON ON ' || 
                        username || '.SCHEMA ' ||
                        ' BEGIN ALTER SESSION SET CURRENT_SCHEMA=master; END';

To do this, the user performing the above code must have the privilege CREATE ANY TRIGGER (required to create triggers in other people's schemas). An alternative to creating one trigger per user would be creating only one trigger database-wide by saying AFTER LOGON ON DATABASE instead of AFTER LOGON ON username.SCHEMA, but that would require the ADMINISTER DATABASE TRIGGER privilege which I deemed to dangerous in my case. Also note that the users's schemas are now longer empty (since they contain one trigger each), so CASCADE is now needed if later DROPping the user again.

Thanks for your help again, and I hope I have now helped others, too - Holger.

-- 
Dr. Holger Peine
Fraunhofer IESE, Kaiserslautern, Germany
Phone +49-6301-707-134, Fax -209 (shared)
www.iese.fraunhofer.de/Staff/peine -- PGP key on request or via pgp.mit.edu