Re: How do I change the owner of an table?

A common approach is to create two, or more users, one that owns schema/table objects
and one that holds stored procedures. The second can also be any of your developers. Then create private synonyms from each app user to the schema user.

The advantage to this is that you can do things like have multiple versions of the schema in the same database. (you just change where the app points to), and also multiple versions of the app user. Each account that logs into the system would then only really need access to the app users's account. (execute priv on the procedures.)

You can also use public synonyms, but I generally prefer using just private.

I have also seen where both the stored objects and the table objects are in the same user, but are referenced with synonyms.

--
Bob Fazio
Remove no.spam from my email to reply

Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us> wrote in message news:37DE863B.3C0CE07_at_dced.state.ak.us...
> Thanks. The apps do reference the owners. Obviously I want to avoid
> this in the future. Would you recommend creating synonyms and referencing
> them in the apps? I haven't used synonyms before, but I'm starting to
> read up on them. Would we need to create the synonyms for each user, or
> could we create public synonyms and use those?
>
> I'm afraid that insisting that the developers create their tables under
> a generic ID would cause a revolt, although it's definitely feasible for
> this case sinc the developer's left.
>
> Jonathan Gennick wrote:
> >
> > I would start by exporting this user's objects, and
> > reimporting them under some other user id. It sounds like
> > you create a generic user-id that just exists to own this
> > data.
> >
> > If your applications reference these objects by referencing
> > the owner name (e.g.: select * from owner.table), you will
> > need to make some modifications.
> >
> > Jonathan
> >
> > On Mon, 13 Sep 1999 13:34:29 -0800, Calvin Crumrine
> > <Calvin_Crumrine_at_dced.state.ak.us> wrote:
> >
> > >One of our developers has left and I want to delete them but they
> > >own lots of the tables & procedures we use. How do I change this?
>
> --
> "Programming today is a race between software engineers
> striving to build bigger and better idiot-proof programs,
> and the universe trying to produce bigger and better idiots.
>
> So far, the universe is winning."
>
> Rick Cook, mission manager,
> NASA Mars Pathfinder project.
Received on Tue Sep 14 1999 - 19:09:33 CDT