OraFAQ Forum: SQL & PL/SQL » CREATE ANY PACKAGE alternative

Home » SQL & PL/SQL » SQL & PL/SQL » CREATE ANY PACKAGE alternative (Oracle 10g)

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

CREATE ANY PACKAGE alternative [message #637229]

Wed, 13 May 2015 06:16

Gnans
Messages: 2
Registered: May 2015
Location: United Kingdom

Junior Member

Hello everyone,

I have a requirement as below. Any way to achieve this rather than providing a blanked "CREATE ANY" access ?

1) Application is running as User/Schema id X
2) At the trigger of a particular function, the application will have to compile a PACKAGE on Schema Y.

How do I achieve this?

Thanks in advance for your input.

Report message to a moderator

Re: CREATE ANY PACKAGE alternative [message #637231 is a reply to message #637229]

Wed, 13 May 2015 06:19

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Why have you to do this? I don't see any reason and I don't see even less reason to not do it with the owner of the package.
2/ If you REALLY want to do it, you can create a procedure in the owner's schema, procedure which will recompile the package, and grant the execute privilege on this procedure to the one that must recompile the package.

Report message to a moderator

Re: CREATE ANY PACKAGE alternative [message #637232 is a reply to message #637231]

Wed, 13 May 2015 06:36

Gnans
Messages: 2
Registered: May 2015
Location: United Kingdom

Junior Member

To answer that
1 -> The user/schema X is a super user in terms of the application and cannot change it to be running as User Y.
2 -> Schema Y is where all our code is preferred to be located.

Report message to a moderator

Re: CREATE ANY PACKAGE alternative [message #637233 is a reply to message #637232]

Wed, 13 May 2015 06:48

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ If Y is the owner of the package why can't you connect as Y to alter the package?
2/ Why some one other that Y can alter the package?
3/ What "super user of the application" means? If it is a super user for application data why would he be able to alter the application code/structure?
Only the owner of the application (or a DBA) should be able to alter the application objects.

In the end, why someone would just compile a package? Oracle does it automatically when needed.

Report message to a moderator

Re: CREATE ANY PACKAGE alternative [message #637249 is a reply to message #637229]

Wed, 13 May 2015 08:09

mnitu
Messages: 159
Registered: February 2008
Location: Reims

Senior Member

Your requirement remembers me this article on Oracle Magazine concerning some similar demand. See if the suggested solution can work in your case

Report message to a moderator

Previous Topic:	Temporary table space error
Next Topic:	Transpose/Pivot with all combination

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 28 12:07:07 CDT 2026