Re: GRANTS & ROLES

From: TSAWMILL.US.ORACLE.COM <TSAWMILL_at_US.ORACLE.COM>
Date: Wed, 28 Feb 1996 09:03:58 -0500
Message-Id: <9602281416.AA02927@alice.jcc.com>

--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

• Received from ISS.SKEPPEI 6031 28-02-96 08.53 -> MAILEX.IBMMAIL.INTERNET -: - - - - - - - - - - > ROUTING VIA MAIL EXCHANGE

> Under what schema are the procedures stored? Are there synonyms for
> "test_proc" that user2 can access (either public or explict)? Assuming
> "test_func" is in user1's schema can user2 run "user1.test_func"? It
> sounds like user2 can't see the "test_func". Try creating a synonym
that> user2 can see or have user2 call for the function by its full name.

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,
>>

>> I'm probably being very stupid, but I can't get the following to work. >> can anyone help?

>> 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

>> Legal & General
>> gblegl7j_at_ibmmail.com

--Boundary-8666713-0-0-- Received on Wed Feb 28 1996 - 09:16:44 CST