RE: Re: Re: developer access to alter procedure

  • From: Jeff Chirco <JChirco@xxxxxxxxxx>
  • To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 8 Feb 2012 23:56:45 +0000

Yeah this is only for dev environments, not production.  And I do give proxy 
permissions to certain schemas for the developers in DEV only but there are a 
few schemas that have procedures that I don't want them touching but other 
procedures they can touch in that schema.    But this isn't too frequent so I 
guess we can live with it.
Thanks for all the tips though, they were helpful.


From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Wednesday, February 08, 2012 3:43 PM
To: Jeff Chirco; oracle-l@xxxxxxxxxxxxx
Subject: Re: Re: Re: developer access to alter procedure

Jeff,

Most shops have a change-management process where developers submit approved 
changes to the DBAs, and the DBAs with the privileges run the scripts.  Shops 
where developers are making changes to production without an accountable 
change-management process controlled by the end-users are playing russian 
roulette.

An alternative to having the DBA run the scripts is the following arrangement:

  1.  The schemas owning tables, indexes, procedures etc have passwords 
controlled by developers
  2.  During normal processing, those schema accounts are locked by the DBAs
  3.  During a change window, DBAs unlock the schema accounts
  4.  Developers login to make the changes
  5.  At the end of the change window, DBAs lock the schema accounts again and 
normal processing resumes

Of course, this alternate arrangement requires that the schema accounts which 
own objects (i.e. tables, indexes, procedures, etc) are used only as 
"containers", and are not used for access by the application.

Hope this helps.
Tim Gorman
consultant => Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035
email      => Tim@xxxxxxxxx<mailto:Tim@xxxxxxxxx>
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

-----Original Message-----
From: Jeff Chirco 
[mailto:JChirco@xxxxxxxxxx]<mailto:[mailto:JChirco@xxxxxxxxxx]>
Sent: Wednesday, February 8, 2012 04:16 PM
To: tim@xxxxxxxxx<mailto:tim@xxxxxxxxx>, 
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: [QUARANTINE] Re: Re: developer access to alter procedure
Thanks, yours and Andy?s examples make sense but not really what I am looking 
for.  Say I have user JEFF who wants to create or change a procedure that is 
owned under Schema B, but I don?t want JEFF to be able to change any procedure 
under B.  I guess maybe if I took your similar approach and created a procedure 
which had a parameter as a clob or external file which is code they wanted 
compiled and then did a execute immediate.  But that is pretty cumbersome and 
my developers will give me a lot of grief.
I wish you could just say something like this.

Grant create any procedure under schema B to JEFF;
Grant alter procedure b.my_procedure to JEFF;


Jeff Chirco | Database Administrator

o 949 509 6374


From: Tim Gorman [mailto:tim@xxxxxxxxx]<mailto:[mailto:tim@xxxxxxxxx]>
Sent: Wednesday, February 08, 2012 2:56 PM
To: Jeff Chirco; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: [QUARANTINE] Re: Re: developer access to alter procedure
Importance: Low

Jeff,

You can go really crazy with this idea, but let's start with the concept of 
truncating a table in a production environment.  You do not want everyone to be 
able to do it, and you certainly don't want to grant the DROP [ANY] TABLE 
privilege necessary to allow someone to truncate the table natively.

So, you create a procedure named TRUNCATE_TABLE_XYZ whose body contains the 
sole command "TRUNCATE TABLE XYZ".  Then, you can grant EXECUTE on the 
procedure to AMY and AARON but not to BETSY and BOB.  So now, AMY and AARON 
have the ability to truncate the table.

Now you can start to get fancier.  Instead of a procedure per operation (as in 
this example), you can add parameters to the procedure. So, now instead of a 
procedure named TRUNCATE_TABLE_XYZ to truncate the table named XYZ and another 
procedure named TRUNCATE_TABLE_ABC to truncate the table named ABC, you can 
have a procedure named TRUNCATE_TABLE which takes a table name as a parameter 
and then executes the dynamic PL/SQL statement "EXECUTE IMMEDIATE 'truncate 
table '||in_table_name" where "in_table_name" is the input parameter to the 
procedure.  So now AMY and AARON can execute 
"TRUNCATE_TABLE(in_table_name=>'XYZ')" and 
"TRUNCATE_TABLE(in_table_name=>'ABC')" if they are granted EXECUTE to it.

And you can keep getting fancier;  maybe the TRUNCATE_TABLE procedure has some 
qualifying logic of its own, where it will only perform the requested operation 
during certain times of day?  Or maybe it also logs the requested action as 
well as the outcome.  And so on...

Does that make sense?

Hope this helps...

Tim Gorman
consultant => Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035
email      => Tim@xxxxxxxxx<mailto:Tim@xxxxxxxxx>
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

-----Original Message-----
From: Jeff Chirco 
[mailto:JChirco@xxxxxxxxxx]<mailto:[mailto:JChirco@xxxxxxxxxx]>
Sent: Wednesday, February 8, 2012 02:49 PM
To: tim@xxxxxxxxx<mailto:tim@xxxxxxxxx>, 
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: [QUARANTINE] Re: developer access to alter procedure
I am not sure if I understand what you mean.  Can you give me more detail.
Thanks.






From: Tim Gorman [mailto:tim@xxxxxxxxx]<mailto:[mailto:tim@xxxxxxxxx]>
Sent: Wednesday, February 08, 2012 12:39 PM
To: Jeff Chirco; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: [QUARANTINE] Re: developer access to alter procedure
Importance: Low

Jeff,

Encapsulate the commands you want to provide within a PL/SQL packaged- or 
stored-procedure.  Then, you can control access to that the way you'd like.  
You can also build in auditing/tracking, etc.

Hope this helps...

Tim Gorman
consultant => Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035
email      => Tim@xxxxxxxxx<mailto:Tim@xxxxxxxxx>
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

-----Original Message-----
From: Jeff Chirco 
[mailto:JChirco@xxxxxxxxxx]<mailto:[mailto:JChirco@xxxxxxxxxx]>
Sent: Wednesday, February 8, 2012 01:27 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: developer access to alter procedure

I would like to give a developer access to alter a specific list of 
procedures/functions/packages from multiple schemas but I can think of a way to 
do it. I don't want to give him access to a whole schemas because there are 
other procedures he should not touch, and I don't want to give him the alter 
any procedure privilege. Is there any way to do this that I am not thinking of? 
How come Oracle doesn't have the command: Grant alter on to user; Jeff -- 
//www.freelists.org/webpage/oracle-l





--
//www.freelists.org/webpage/oracle-l


Other related posts: