Re: Simple Q re schemas and users

Holger Peine wrote:

>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.
>
>
>

Just one recommendation. Alter your dynamic SQL to use bind variables.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)