Skip to Main Content
  • Questions

  • anything between create procedure and create any procedure privilege?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 22, 2003 - 9:40 am UTC

Last updated: January 17, 2004 - 1:13 am UTC

Version: 9iR2

Viewed 1000+ times

You Asked

two people(u1 and u2) are working on the same package, but I don't want to grant create any procedure privilege to them. is there any way u1 can grant the so called "edit" privilege to u2 only?
right now, what i need to do is grant create any procedure to u2 in order for u2 to modify u1's package, but
in the mean time, u2 can modify anybody else's package.
is there anyway we can fine tune this privilege control?

and Tom said...

You can in fact use a stored procedure for this.

U1 would create a procedure:

create or replace procedure create_procedure_p( p_str in varchar2 )
as
begin
execute immediate 'create or replace procedure p ' || p_str;
end;
/
grant execute on create_procedure_p to U2
/


Now, U2 can:

begin
u1.create_procedure_p(
'( x in out number )
is
begin
x := x+1;
dbms_output.put_line( ''How is that?'' );
end;' );
end;
/

Now, it can get nasty with quotes so I recommend something like this. use " instead of ' in the string and code:


begin
u1.create_procedure_p( replace(
'( x in out number )
is
begin
x := x+1;
dbms_output.put_line( "How is that?" );
end;',
'"', '''' ) );
end;
/



using a stored procedure allows you great flexibility in controlling access and "fined grained security" like this.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

too complicated

Christo Kutrovsky, December 23, 2003 - 9:10 am UTC

This is a workarround, however almost inapplicabe as it is "write only" and extremply inconvenient

If you read the procedure, you will read it with single quotes, then you have to convert them to double quotes , and then u2 could create/replace it.

And on top of everything, most developers use some kind of a developement tool, and they wouldn't be able to use it.

Same thing applies to tables and other object, it would be very nice if Oracle had a "create any procedure in schema <schema name>" and similar grants.



Tom Kyte
December 23, 2003 - 11:41 am UTC

it would be very nice if we could anticipate each and every use case, implement each and every request. I agree. wouldn't that be nice.

short of that, this be the only way to do it. sorry you don't "like it", but having u1 and u2 work on the same piece of code in the first place seems "fishy" to me. I don't see this as being a very common requirement.

Normally, u1 and u2 would use CVS or some source code repository, they would test in THEIR schema's, they would deploy to U3 on the "shared machine" via some process.

Having 2 cooks stirring the same soup. recipe for disaster in the first place.

Agreed.

Christo Kutrovsky, December 23, 2003 - 2:55 pm UTC

You are right. It is fishy, and having a version control system is way better then 2 users modifying the same code.

I guess I was thinking at a lower level, and too much comparing to table priviliges.



something fishy

Dan Clamage, December 24, 2003 - 9:10 am UTC

We had this trouble. Two developers were working on the same package, in SQL Navigator. Both had extracted the source code from the data dictionary (a common capability in Nav), both had edited it, both had saved it back to the database. But the second person overwrote the changes made by the first person. They asked me how to avoid that. I said, use VSS (our source code control tool), one of you check it out, only one of you edit it FROM THE SCRIPT. He said, I was afraid you'd say that. I've seen too many developers bitten on the butt, editing packages out of the database and saving it back to the database. I ALWAYS work out of a text file.

Tom Kyte
December 24, 2003 - 10:14 am UTC

remember

code is code is code is code.

stored procedures is just "code", no different then C, VB, Java, Cobol. It is code and needs to be developed in the same fashion with the same rules.

If you have more then one person mucking with it -- you'll need source code control just like you would with anything else.

Mark, January 16, 2004 - 3:25 pm UTC

Are there any apps now, or planned that you know of to incorporate VSS functionallity inside Oracle? It's a shame we have to use a M$ database to manage our Oracle database code. (I'm half joking, but it would be a nice addon).

Thanks



Tom Kyte
January 17, 2004 - 1:13 am UTC

i'm not familar with VSS, don't know what it is.

for source code control, I use rcs.

Source Code Control with Oracle development

Tim, August 16, 2006 - 2:41 pm UTC

Microsoft Visual Source Safe (MS VSS) can be successfully used for your Oracle objects. Obviously this can be done in a manual fashion. (ie - export the Oracle object and put it into MS VSS.) One would assume the previous poster was not asking about this - but rather about an automated and integrated method.

Quest Software offers both Toad and SQL Navigator. These both contain "team coding". This can be integrated with MS VSS so as to provide seamless source code control (SCC) capabilities.

In my last place of employment - we had implemented this quite successfully. I would expect that something other than MS VSS could be used - however my experience is with MS VSS.

An enhancement to this process which I have read about - but not implemented - would be to have a system trigger which (upon DDL on a particular object by a particular user) would check in the "team coding" repository to see if that object has been checked out by that user.

At my current place of employment - we are planning on implementing this in the near future. I will be trying to implement the "system trigger" option as well - it will be interesting to see if I can really get it to work - in the manner in which I have read about.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library