Re: schema oracle

Hi,

You must create private synonyms for each user instead of public synonyms for all of them. Create script in order to generate synonyms based on user names and object names.

And of course, you should have three different sets of users, one for each schema. For instance:

dev1, ... , devn uses development schema
test1, ... , testn uses test schema
final1, ... , finaln uses final test schema

Each user like devi must have his own set of synonyms:

create synonym devi.object1 for development.object1; ...
create synonym devi.objectm for development.objectm;

Create roles for three schemas:

create role development_role;
create role test_role;
create role final_role;

Grant necessary privileges to each role:

grant select on development.object1 to development_role; ...
grant execute on development.objectm to development_role;

Grant appropriate role to each user:

grant development_role to devi;
grant test_role to testi;
grant final_role to finali;

Do not make all of these by hands. Create tables in each schema describing users, objects and permissions. Write scripts to generate necessary statements based on these tables.

Andrew Protasov

> Hi there
>
> I have the following problem :
>
> we made 3 tablespaces with 3 schema object owners
>
> t.i 1 tablespace for development - owner1
> 1 tablespace for testing - owner2
> 1 tablespace for final test - owner3
>
> in all three tablespaces there are exactly the same tables.
> Our developers (development tool=Delphi 3) like'd to access the
> tables in the tablespaces without to qualify the owner of the objects.
> I can do the following
>
> Create synonym for those tables in tablespace development, but
> i have then a problem with the other 2 tablespaces; i can not use
> the same synonym.
> How can i handle this problem ???
> THANKS
> gkor_at_rdw.nl
>
>
>
>
>
>
Received on Fri Nov 20 1998 - 10:33:20 CST