RE: anyone have a package for creating objects in other schemas?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "Jay.Miller@xxxxxxxxxxxxxxxx" <Jay.Miller@xxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Jul 2009 17:18:41 -0400
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:
