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

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.....

-Mark

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Wednesday, July 29, 2009 4:42 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: anyone have a package for creating objects in other schemas?

Sigh.

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 :).



TIA,
Jay

Other related posts: