A Permissions Question

[LaurenM]

This is just a hypothetic question for right now. Supose a user/schema is granted select permission to use a function, call it function1, in the DB, and suppose function1 calls other functions. Now, does the select permission of the user/schema cascade down so that the user/schema can now call the other functions directly, or does function1 use the permissions of the owner/creator to call the other functions?

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

... does function1 use the permissions of the owner/creator to call the other functions?

Short answer: "Yes".


Below is code that shows that the permissions of the owner of the function are the key. The end user of the function needs permission to access the explicitly called function only...The end user does not need direct/explicit permission to objects to which the function refers (provided that the owner of the function has appropriate privileges to objects to which the function refers):

SQL> conn yada3/yada3
Connected.
SQL> create or replace function test2 return varchar2 is 
  2      begin
  3          return 'It worked';
  4      end;
  5  /

Function created.

SQL> grant execute on test2 to yada2;

Grant succeeded.

SQL> conn yada2/yada2
Connected.
SQL> create or replace function test return varchar2 is 
  2      begin
  3          return yada3.test2;
  4      end;
  5  /

Function created.

SQL> 
SQL> conn yada1/yada1
Connected.
SQL> select yada2.test from dual;
select yada2.test from dual
             *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> conn yada2/yada2
Connected.
SQL> grant execute on test to yada1;

Grant succeeded.

SQL> 
SQL> conn yada1/yada1
Connected.
SQL> select yada2.test from dual;

TEST
-----------------------------------------------------------------------
It worked

1 row selected.

SQL>

Let us know if the above experiment answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LaurenM]

Yes it does, I couldn't find anything that explicitly stated it, thanks Santa.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[KenCunningham]

No Christmas star fot Santa, Lauren?

I want to be good, is that not enough?

 

[KenCunningham]

fot = for

Too many mince pies!

I want to be good, is that not enough?