Home » SQL & PL/SQL » SQL & PL/SQL » call to function in SYS schema (Oracle 10g)
call to function in SYS schema [message #446000] Fri, 05 March 2010 01:56 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello,

something is puzzling me. I am defining a function in a schema as a user with admin role (default). The function is to do a call to another function on a custom package in SYS schema.

something funny happens : when I compile the function (defined with authid as DEFINER)it says that the function SYS.custom_package.myFunction is not defined.

however if I do a
select sys.custom_package.myFunction from dual
it's okay.

why this behaviour and how to work around it?
You see, the package in SYS proposes a number of other functions that I don't want to expose. I only intend to create some sort of wrapper function that would marshall the call to the myFunction only on the custom_package in SYS.


my wrapper function lokks something like that :
create or replace function myFunction_wrapper authid definer return pls_integer is
begin
 return sys.custom_package.myFunction;
end;

thanks in advance,
Didier
Re: call to function in SYS schema [message #446002 is a reply to message #446000] Fri, 05 March 2010 02:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a standard problem people face - inside Packages and procedures, the only privileges that apply are those that are granted directly, rather than via a role.

So, if you want your package to be able to call sys.custom_package.my_function then you need to explicitly grant EXECUTE on that package to the user in question.

On a related note - don't put your own code in the SYS schema. SYS is not there for you to mess about with, it's there as a fundamental part of the database.
Re: call to function in SYS schema [message #446019 is a reply to message #446002] Fri, 05 March 2010 03:04 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi,

thanks for the information. Too bad things turn out this way...I even tried tricking Oracle by doing the function call via an execute immediate '' into construct but to no avail. I thought that the authid would have been of some help but in fact it all sounds logical after all.

regards,
Didier
Previous Topic: Truncate and checkpoint
Next Topic: relationship between the tables
Goto Forum:
  


Current Time: Tue Dec 06 12:25:34 CST 2016

Total time taken to generate the page: 0.08996 seconds