Re: unable to create stored outline for sql inside a procedure --

That would be Mary-Ann Davidson. I'll ask her.

Mogens

Jared Still wrote:

>This is in conflict with what a few Oracle folks have said.
>
>Interesting. I guess you'd have to ask the oracle security architect
>to get the full story.
>
>Jared
>
>On Thursday 26 December 2002 12:39, Khedr, Waleed wrote:
>
>
>>This may be more convincing:
>>
>>http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data
>>b ase_id=NOT&p_id=1031418.6
>>
>>regards,
>>
>>Waleed
>>
>>-----Original Message-----
>>Sent: Thursday, December 26, 2002 2:19 PM
>>To: Multiple recipients of list ORACLE-L
>>--Resolved
>>
>>
>>jared,
>>
>>Thanks for explanation. Still not convinced because of following two
>>reasons
>>
>>1) Same scenario can happen with explicit privileges as well. User A grants
>>ALL privileges on MY_TABLE to B without GRANT OPTION. Now B can create a
>>stored procedure to do DML on MY_TABLE and grant execute permission to
>>anybody. Which would allow user B to grant access on A.MY_TABLE, though A
>>did not give that kind of access to user B (No GRANT OPTION).
>>
>>2) To take care of this problem invokers rights facility was introduced.
>>Then why this restriction on roles.
>>
>>Please let me know if I am missing something here.
>>
>>Thanks
>>Shaleen
>>
>>----- Original Message -----
>>To: <ORACLE-L_at_fatcity.com>; "Shaleen" <shgarg_orafaq_at_hotmail.com>
>>Sent: Wednesday, December 25, 2002 11:09 PM
>>procedure --Resolved
>>
>>
>>
>>>Shaleen,
>>>
>>>This is done to preserve security.
>>>
>>>User A owns a table MY_TABLE.
>>>
>>>Role A_STUFF allows insert, select, update, delete on A.MY_TABLE.
>>>
>>>grant insert,select,update,delete on MY_TABLE to A_STUFF;
>>>
>>>( note that the role was not granted admin privs on the table )
>>>
>>>User B is granted role A_STUFF.
>>>
>>>If user B were able to create a stored procedure based on
>>>privs from the role A_STUFF, he would be able to grant
>>>execute on the SP, which would allow user B to grant
>>>access to A.MY_TABLE, though A did not give that kind
>>>of access to role A_STUFF.
>>>
>>>Hence the need to grant a user explicit rights to an object
>>>if it is to be used in a stored procedure.
>>>
>>>System privs work the same way, they must be explicit.
>>>
>>>Jared
>>>
>>>On Tuesday 24 December 2002 11:13, Shaleen wrote:
>>>
>>>
>>>>All,
>>>>
>>>>Oracle support was able to resolve this issue for me and I would like
>>>>to share the learning. The problem was that I was unable to create
>>>>stored outline for sql executing within a stored procedure after
>>>>turning create_stored_outlines=true. Create outlines for sql
>>>>satetements
>>>>
>>>>
>>executing
>>
>>
>>
>>>>from sqlplus/plsql blocks was not an issue.
>>>>
>>>>The problem is resolved by granting create any outline privilege to the
>>>>user explicitly.
>>>>
>>>>Once I again I was bit by the limitation of roles not passing privilege
>>>>within stored procedures and this has to be done explicitly. Why oracle
>>>>
>>>>
>>has
>>
>>
>>
>>>>this limitation beats me!!
>>>>
>>>>Thanks for help Jared & Raj.
>>>>
>>>>Shaleen
>>>>
>>>>
>>>----------------------------------------
>>>Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
>>>Content-Transfer-Encoding: quoted-printable
>>>Content-Description:
>>>----------------------------------------
>>>
>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).