Re: unable to create stored outline for sql inside a 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: Jared Still
  INET: jkstill_at_cybcon.com

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