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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
