Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: GRANTS & ROLES
--Boundary-8666713-0-0
Access to the function must be granted explicitly because it's not being executed as the owner. When you run the procedure, it is actually being executed as the owner. But if you were to copy the procedure into this running users schema and execute it, it would fail because objects within the procedure must also have privileges explicitly granted, not thru roles. I know this is a weird distinction, but this has been my experience.
Tim Sawmiller
tsawmill_at_us.oracle.com
"The opinions expressed here are my own and not necessarily those of the
Oracle Corporation".
--Boundary-8666713-0-0
Content-Type: message/rfc822
Date: 28 Feb 96 03:55:19
From:"Ian Skepper " <gblegl7j_at_IBMMAIL.COM>
To: Multiple,recipients,of,list,ORACLE-L,ORACLE-L_at_CCVM.SUNYSB.EDU
Subject: GRANTS & ROLES
Reply-to: ORACLE-L_at_CCVM.SUNYSB.EDU
X-Orcl-Application: Sender: "ORACLE database mailing list."
<ORACLE-L_at_CCVM.SUNYSB.EDU>
X-To: oracle-l_at_ccvm.sunysb.edu
As you can see from the example, I was already using the full name of the function (SELECT user1.test_func FROM DUAL) so synonyms are not the problem here. Also this does not explain why a procedure should work in this manner but not a function.
Regards
Ian Skepper
Legal & General
gblegl7j_at_ibmmail.com
_____________________Original Mail Item follows_________________________ >> >> Dear All, >>
>> SQL*Plus: Release 3.1.3.5.5 >> Oracle7 Server Release 7.1.4.1.0 >> PL/SQL Release 2.1.4.0.0 >> >> Scenario :- >> >> User1 creates a function test_func >> User1 grants execute on test_func to user_role >> >> User2 (who has the role user_role) executes the following... >> >> SET ROLE user_role; >> >> SELECT user1.test_func FROM DUAL; >> >> ...and receives the following error... >> >> ORA-06550: line 1, column 12: >> PLS-00201: identifier 'TEST_FUNC' must be declared >> ORA-06550: line 1, column 7: >> PL/SQL: Statement ignored >> >> If user1 now grants execute on test_func directly to user2 then >> everything works ok. Why ? >> >> Also, if user1 changes the function test_func into a procedure >> test_proc and grants execute on test_proc to user_role, then user2 can >> run this with no problems without being granted execute directly. >> Why the difference ? >> >> TIA >> >> Ian Skepper
--Boundary-8666713-0-0-- Received on Wed Feb 28 1996 - 09:16:44 CST
![]() |
![]() |