Re: Setting CURRENT_SCHEMA externally

<ritu_at_my-deja.com> wrote in message news:8esmn1$pj3$1_at_nnrp1.deja.com...
> Is it possible to set a default Schema for a users?
>
> For example, how do I assign the user BATCH, to schema OWNER etc.
>
> I am aware of the following:
>
> I can open a session with the BATCH user and then issue the following
> command:
> ALTER SESSION SET CURRENT_SCHEMA = OWNER
>
> My problem is that I want to be able to set this as a system default
> externally (for example, using Security Manager or Schema Manager).
>

That's interesting. I've never used that clause - I've always left the user in the default schema but created synonyms for all the objects they need access to (and are allowed to access). It's quick and it works, but I'm not sure if its what you want...

To generate the synoyms I use recursive SQL. The script is not at hand tonight, but you use something like..

SELECT
   'CREATE SYNONYM ' || table_name || ' FOR schemahere.' || table_name || ';'
FROM all_tables
WHERE user = &target_user

Spool it to a file and run it. If interested in this and not sure how to turn of all the SQL*Plus stuff then mail me and I can send you the actual scripts. Note that you need a similar one to do the grants.

/Richard/
roxl_at_ozemail.com.au Received on Fri May 26 2000 - 00:00:00 CDT