OraFAQ Forum: SQL & PL/SQL » Create package in another schema without using 'ALTER ANY PROCEDURE' privilege

Home » SQL & PL/SQL » SQL & PL/SQL » Create package in another schema without using 'ALTER ANY PROCEDURE' privilege

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create package in another schema without using 'ALTER ANY PROCEDURE' privilege [message #203051]

Mon, 13 November 2006 13:23

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

Report message to a moderator

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

Frank
Messages: 7901
Registered: March 2000

Senior Member

compile package in other schema: bad idea as well.
Why would he need to do that?

Report message to a moderator

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

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

)

Report message to a moderator

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

Frank
Messages: 7901
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..

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

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

Frank
Messages: 7901
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?

Report message to a moderator

Previous Topic:	doubts in suquery
Next Topic:	NEW to ORACLE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 09:24:25 CST 2024