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

Thanks Waleed. For the first time I have understood the reason behind this. Which is, "Larry shirked"!!

"The reason why roles are disabled in stored procedures is not due to the roles themselves, but has to do with the dependency model. If roles were enabled in stored procedures, Oracle would need to administer what roles are being used in each individual procedure. When a role changes or is deleted, Oracle would need to invalidate the procedure. This administration raises the complexity of this process to the nth degree and as such the decision was made to ignore roles. "

Thanks
Shaleen
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, December 26, 2002 12:39 PM

> This may be more convincing:
>
>

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
> 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: Shaleen
> INET: shgarg_orafaq_at_hotmail.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Shaleen
  INET: shgarg_orafaq_at_hotmail.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).