RE: anyone have a package for creating objects in other schemas?

From: Bobak, Mark <>
Date: Wed, 29 Jul 2009 17:18:41 -0400
Message-ID: <>

Hi Jay,

Perhaps I'm missing something, but I'm thinking you're making it more complicated than it needs to be. (Or, perhaps I'm not clear on what restrictions have been inflicted on you by damagement.)

The way we work here, DBAs have ownership of the application owner schema, even in devel.

The way we work is something like this:

1.) DBA owns app owner schema. DBA creates public synonyms for all app owner objects. Various login ids are created, and privileges are granted to various tables and stored procs, according to requirements of that particular user. This is true of all instances, devel, test, preprod, and prod.

2.) In development, developers are allowed their own login id. The developer is granted select/insert/update/delete on all app owner tables and execute on all app owner stored objects. The developer is granted full create abilities in his own schema, but no create any privileges of any kind.

3.) Now, the developer develops by relying on the app owner objects, except for in his little corner of the world, where he's currently working. Suppose he's working on a new version of proc1. He creates proc1 in his schema, and can do all his development and unit testing of proc1 right there in his schema. Anything he doesn't have a copy of, public synonyms ensure he seems the currently released object in the app owner schema.

4.) At the time of the preset deadline, "release to development", developers provide DBAs all DDL to make changes to sync up the main application owner schema with the changes they've been working on in their local schemas.

This way, developers can develop to their hearts content, do what they need to in their own schema, etc, and aren't asking you (the DBA) to do this, make that change, etc. At the predefined time, when the changes are to be officially released into development, you (the DBA) make all the changes, based on scripts they provide you.

This works pretty well for us, and avoids some of the issues you mention.

It's by no means perfect, but for a relatively small development team, it works pretty well.

Hope that helps....and sorry if I missed your point.....


From: [] On Behalf Of Sent: Wednesday, July 29, 2009 4:42 PM
Subject: anyone have a package for creating objects in other schemas?


The new company policy is that developers can neither have the password for schemas nor have CREATE ANY xxx on development databases.

The only remaining option I see (other than having developers call me every 2 minutes to keep putting new iterations of code into dev) is to write a package with procedures to create various types of objects which will be owned by the schema and grant execute on it.

I thought it was worth asking if anyone knows of an already existing version of such a script.

I also considered telling the manager who set the policy that he has to write the script himself but decided that wasn't advisable :).


-- Received on Wed Jul 29 2009 - 16:18:41 CDT

Original text of this message