Re: Re: developer access to alter procedure
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: "Jeff Chirco" <JChirco@xxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 08 Feb 2012 22:56:05 +0000
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
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]
Sent: Wednesday, February 8, 2012 02:49 PM
To: tim@xxxxxxxxx, 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]
Sent: Wednesday, February 08, 2012 12:39 PM
To: Jeff Chirco; 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
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]
Sent: Wednesday, February 8, 2012 01:27 PM
To: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:
- » Re: Re: developer access to alter procedure - Tim Gorman
