FAQ
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@EvDBT.com
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
Sent: Wednesday, February 8, 2012 02:49 PM
To: tim@evdbt.com, oracle-l@freelists.org
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
Sent: Wednesday, February 08, 2012 12:39 PM
To: Jeff Chirco; oracle-l@freelists.org
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@EvDBT.com
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
Sent: Wednesday, February 8, 2012 01:27 PM
To:oracle-l@freelists.org
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 --http://www.freelists.org/webpage/oracle-l



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

Search Discussions

  • Jeff Chirco at Feb 8, 2012 at 11:17 pm
    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
    Sent: Wednesday, February 08, 2012 2:56 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 02:49 PM
    To: tim@evdbt.com , oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 12:39 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 01:27 PM
    To: oracle-l@freelists.org
    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 -- http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Guillermo Alan Bort at Feb 9, 2012 at 12:30 am
    That way lays SQL Injection.
    Also, why are developers modifying stuff in a productive environment? I am
    working to remove even readonly access from our prod databases to the
    developers!

    You should have a repository (svn?) with the procedure creation scripts and
    set up a way to release them (manually, scripted, etc).

    Anyway, granting an untrusted user (i.e. not a DBA) the ability to alter a
    single procedure in another schema potentially gives him the ability to run
    anything with that schema's privileges, which is generally a bad idea.

    hth
    Alan.-

    On Wed, Feb 8, 2012 at 8:16 PM, Jeff Chirco wrote:

    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;

    --
    http://www.freelists.org/webpage/oracle-l
  • David Fitzjarrell at Feb 9, 2012 at 12:50 am
    Here is an example of what you might do:

    SQL> connect bong/################
    Connected.
    SQL> create or replace procedure create_proc_elsewhere (p_procname in varchar2, p_proc_params varchar2, p_proctxt in varchar2) is
    2
    3        v_sqltxt varchar2(32767):='create or replace procedure ';
    4
    5  begin
    6        v_sqltxt:=v_sqltxt||p_procname||'('||p_proc_params||') as '||p_proctxt||';';
    7        dbms_output.put_line(v_sqltxt);
    8
    9        execute immediate v_sqltxt;
    10
    11        if sqlcode = 0 then
    12         v_sqltxt:='grant execute on '||p_procname||' to '||user;
    13         v_sqltxt:='create public synonym '||p_procname||' for '||p_procname;
    14
    15         execute immediate v_sqltxt;
    16        else
    17         raise_application_error(-20999, 'Procedure failed to create');
    18        end if;
    19
    20  end;
    21  /

    Procedure created.

    SQL>
    SQL> show errors
    No errors.

    SQL>
    SQL> grant execute on create_proc_elsewhere to bing;

    Grant succeeded.

    SQL>
    SQL> create public synonym create_proc_elsewhere for create_proc_elsewhere;

    Synonym created.

    SQL>
    SQL> connect bing/********************
    Connected.
    SQL>
    SQL> exec create_proc_elsewhere('my_proc', 'p_val in number', ' v_result number; begin select empno into v_result from emp where empno = p_val;  dbms_output.put_line(''Requested empno is: ''||v_result);  end')

    PL/SQL procedure successfully completed.

    SQL>
    SQL> desc bong.my_proc
    PROCEDURE bong.my_proc
    Argument Name                  Type                    In/Out Default?
    ------------------------------ ----------------------- ------ --------
    P_VAL                          NUMBER                  IN

    SQL>
    SQL> set serveroutput on size 1000000;
    SQL>
    SQL> exec my_proc(7499);
    Requested empno is: 7499

    PL/SQL procedure successfully completed.
    SQL>

    The procedure was created in the desired schema and the user who created it can't modify any other procedures in that schema.

    David Fitzjarrell



    ________________________________
    From: Jeff Chirco <JChirco@innout.com>
    To: "tim@evdbt.com" <tim@evdbt.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
    Sent: Wednesday, February 8, 2012 4:16 PM
    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
    Sent: Wednesday, February 08, 2012 2:56 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 02:49 PM
    To: tim@evdbt.com , oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 12:39 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 01:27 PM
    To: oracle-l@freelists.org
    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 -- http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Tim Gorman at Feb 8, 2012 at 11:44 pm
    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:
    The schemas owning tables, indexes, procedures etc have passwords controlled by developersDuring normal processing, those schema accounts are locked by the DBAsDuring a change window, DBAs unlock the schema accountsDevelopers login to make the changesAt the end of the change window, DBAs lock the schema accounts again and normal processing resumesOf 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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 04:16 PM
    To: tim@evdbt.com, oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 2:56 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 02:49 PM
    To:tim@evdbt.com, oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 12:39 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 01:27 PM
    To:oracle-l@freelists.org
    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 --http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l
  • Jeff Chirco at Feb 8, 2012 at 11:58 pm
    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
    Sent: Wednesday, February 08, 2012 3:43 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 04:16 PM
    To: tim@evdbt.com , oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 2:56 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 02:49 PM
    To: tim@evdbt.com , oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 12:39 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 01:27 PM
    To: oracle-l@freelists.org
    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 -- http://www.freelists.org/webpage/oracle-l





    --
    http://www.freelists.org/webpage/oracle-l
  • Eren Bayazitoglu at Feb 9, 2012 at 7:33 pm
    Does the developer need to work with real data in this issue?

    Eren

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jeff Chirco
    Sent: Wednesday, February 08, 2012 8:27 PM
    To: tim@evdbt.com; oracle-l@freelists.org
    Subject: RE: Re: Re: developer access to alter procedure

    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
    Sent: Wednesday, February 08, 2012 3:43 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 04:16 PM
    To: tim@evdbt.com , oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 2:56 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 02:49 PM
    To: tim@evdbt.com , oracle-l@freelists.org
    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
    Sent: Wednesday, February 08, 2012 12:39 PM
    To: Jeff Chirco; oracle-l@freelists.org
    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@EvDBT.com
    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
    Sent: Wednesday, February 8, 2012 01:27 PM
    To: oracle-l@freelists.org
    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 -- http://www.freelists.org/webpage/oracle-l





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


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 8, '12 at 10:57p
activeFeb 9, '12 at 7:33p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase