Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem in Create Store Procedure use non-schema owner account
A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
(if that email address didn't require changing)
On Wed, 09 Jun 1999 17:58:57 -0400, you wrote:
>I heard you did a pretty extensive thread on this pretty recently. Got an address
>for it?
>- Dc.
>
this one is invokers rights and roles (new feature in 8i)
http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=474001169&fmt=text
this one is outlineing the main reason behind not using roles in procedures:
http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=416212575&fmt=text
>Thomas Kyte wrote:
>
>> A copy of this was sent to anthonywmhui_at_hotmail.com
>> (if that email address didn't require changing)
>> On Wed, 09 Jun 1999 03:17:30 GMT, you wrote:
>>
>> >Hi,
>> >
>> >I created all database objects(table, indexes) use the account CCRDEV
>> >and grant select, update, insert and delete to a ROLE. This role is
>> >then granted to a user DCCRMGR. I describe the table RULE use the
>> >DCCRMGR account and everything Ok. However, when I try to create a
>> >Store Procedure that access RULE, the following error appear
>> >
>> >LINE/COL ERROR
>> >-------- ---------------------------------------------------------------
>>
>> roles are never enabled during the execution of a procedure.
>>
>> Try this:
>>
>> SQL> set role none;
>> SQL> "statement you want to test to see if it'll work in a procedure"
>>
>> If you can do it in plus with no roles you can do it in a procedure. If you
>> can't, you must have the privelege from a role and hence won't be able
>> to do it in a procedure.
>>
>> You probably have the privelege to do what you are trying to do in the procedure
>> via a role. Grant the privelege directly to the owner of the procedure and
>> it'll work.
>>
>> Roles are not used to manage developers priveleges (you have 1's or 10's of
>> developers) but are used to manage end user priveleges (you have 100's or 1000's
>> of them)
>>
>> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
>> Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
>>
>> Current article is "Fine Grained Access Control", added June 8'th
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>> --
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
>
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--