Home » SQL & PL/SQL » SQL & PL/SQL » Create package in another schema without using 'ALTER ANY PROCEDURE' privilege
Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203051] Mon, 13 November 2006 13:23 Go to next message
gp01
Messages: 4
Registered: November 2006
Location: Argentina
Junior Member
Hi all,

I face this situation: Need to permit a user to compile packages in another schema, but just in this schema. If i grant 'CREATE ANY PROCEDURE' that user could be modify procedures on all schemas (bad idea jeje). Is there any privilege that cover this situation?

Thanks in advance
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203055 is a reply to message #203051] Mon, 13 November 2006 13:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
compile package in other schema: bad idea as well.
Why would he need to do that?
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203057 is a reply to message #203051] Mon, 13 November 2006 13:56 Go to previous messageGo to next message
gp01
Messages: 4
Registered: November 2006
Location: Argentina
Junior Member
Uff...long and bored story. There are some Auditory requeriments over our heads, one of them involves that kind of actions (too much political decision Mad )
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203074 is a reply to message #203057] Mon, 13 November 2006 15:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But why do I need to recompile if I don't have privilege to alter?
That has nothing to do with politics..
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203084 is a reply to message #203074] Mon, 13 November 2006 16:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you should be able to create a stored procedure in the package owner's schema to compile the code. Grant execute on the new proc to the second schema. This new proc is unlikely to need re-compiling itself becuase it doesn't need dependencies on the other code or tables etc. This approach is also commonly used to allow a schema to truncate a table they don't own.
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203224 is a reply to message #203084] Tue, 14 November 2006 06:49 Go to previous messageGo to next message
gp01
Messages: 4
Registered: November 2006
Location: Argentina
Junior Member
Frank, the not-owner user should be have permissions to alter that packages too. The problem resides on the 'most wide' permisions that grants the 'ALTER ANY PROCEDURE' privilege (he eventually could alter packages in other schemas)

Andrew, granting execute couldn't allow to modify the package.

Maybe i´m not being clear. Hope this helps:

-------------
User1.Package1

Logged as User2, need to:

CREATE OR REPLACE PACKAGE User1.Package1 AS ...
and
ALTER PACKAGE User1.Package1 COMPILE / COMPILE BODY
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203239 is a reply to message #203224] Tue, 14 November 2006 07:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've missed the point of andrew's suggestion.

Create a procedure in USER1 like this:
CREATE OR REPLACE PROCEDURE make (p_code  in  varchar2) is
BEGIN
  IF user != 'USER2' THEN
    raise_application_error(-20001,'Only USER2 can run this');
  ELSE
    execute immediate p_code;
  END IF;
END make;

Grant execute on this procedure to USER2.
If USER2 runs this
begin
  user1.make('CREATE OR REPLACE PROCEDURE user1.make_test is begin null; end;');
end;/
then as long as USER1 has the CREATE PROCEDURE privilege granted explicitly then this will allow USER2 to create the procedure MAKE_TEST in the schema USER1.
Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203247 is a reply to message #203239] Tue, 14 November 2006 08:06 Go to previous messageGo to next message
gp01
Messages: 4
Registered: November 2006
Location: Argentina
Junior Member
You're right JRowbottom, i've missed the point of andrew's suggestion Embarassed
Tried this and works perfect!

Thanks to all!!

[Updated on: Tue, 14 November 2006 08:07]

Report message to a moderator

Re: Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203258 is a reply to message #203247] Tue, 14 November 2006 08:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I read Andrew's post as create a procedure that can recompile EXISTING packages, not do anything you want in another schema.

Why do you have multiple schemas in a single instance, when these schemas seem to be intertwined?
Previous Topic: doubts in suquery
Next Topic: NEW to ORACLE
Goto Forum:
  


Current Time: Fri Dec 02 12:08:24 CST 2016

Total time taken to generate the page: 0.09620 seconds