Re: Newbie:Including other users in my own schema

From: Guy van der Werf <gwerf_at_cm-muc.de>
Date: Mon, 31 May 1999 13:19:05 +0200
Message-ID: <37527029.9666D601@cm-muc.de>

The answer, I believe, is using synonyms (and grants of course).
Julio Negueruela already told you about these, BUT...
I would not use public synonyms unless you are just developing in a protected DB. My experience is that public synonyms will only make you application a potential trouble maker if another app installed into the same DB needs the same synonym (public synonyms are "public" and are controlled by one name space).
To be on the safe side, create a script that generates another script that grants access to your schema's objects to other users. I should even say "...schema's objects to a role. This role is granted to the other users". The synonyms are generated in the same way and created for each user (not public but private). Its more hassle, but much safer. Besides, you only create the scripts once. You run them everytime you change you schema (e.g. add or remove tables).

The second suggestion from Javier Rojas would work, but is really not needed and looses some of the advantages you get by keeping users in their own schema.

Cyril Elkaim wrote:

        Hi,

        Is it possible to intern other users in my own schema. That is, for the
other users, assuming I grant them rights on my tables, to select,
etc... without prefixing the name of my tables with my schema?

        creator:
                create table test (...)
                grant all on test to other
        other:
                select * from test
        and not:
                select * from creator.test

        The reason is that I must modify many, many queries in my application
and I really prefer to avoid that :-)

        Thanks
        Cyril Elkaim

Received on Mon May 31 1999 - 06:19:05 CDT