Or you can create logon trigger which sets the current schema:
create or replace trigger set_schema
after logon on database
begin
execute immediate 'alter session set current_schema=MASTER';
end;
Holger Peine <peine_at_iese.fraunhofer.de.this-is-junk> wrote in message news:<3f82bb52_at_news.fhg.de>...
> Hello everyone,
>
> you will see from my question that I'm not an Oracle expert.
>
> How can I make many DB users access the same set of tables
> without qualifying each access with the name of the schema
> containing the tables?
>
> I have one user MASTER which creates all the tables (thus all tables
> end up in the MASTER schema), and many other users (some of them
> created later at run-time) accessing the tables (sometimes via DML
> statements, sometimes via stored procedures). The users maintain no
> other data besides that in the MASTER schema's tables, i.e. there
> are no tables in all those user schemas. The users need to prefix
> the table names, like MASTER.sometable. I feel that this is not the proper
> way to do that, but what is the recommended way?
>
> Should I create a public synonym (or one private synonym in each user
> schema) for every table name?
>
> Is it possible to create the other users in the MASTER schema in the first
> place (which might create security problems - would GRANTs still provide
> protection within one common schema?)?
>
> - And one more related question about packages: Do packages exist within
> a schema or besides the schemas? I.e. does the package_name.package_member
> access syntax have to be prefixed with a schema name when used from
> another schema?
>
> Thanks for your help,
Received on Wed Oct 08 2003 - 03:33:36 CDT
