I remember seeing another reason (I think from Tom Kyte). Roles tend to be used for a larger number of users than system privileges. If procedures could be created using rights from roles, every time a role was changed (grant a new priv, disable, ...), the procedure would need to be invalidated and recompiled. That's overhead.
Henry -----Original Message----- Sent: Thursday, December 26, 2002 3:44 PM To: Multiple recipients of list ORACLE-L --Resolved Convinced or not, that's the reason, fallible as it may be. On Thursday 26 December 2002 11:18, Shaleen wrote: > 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 ----- > From: "Jared Still" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; "Shaleen" <[EMAIL PROTECTED]> > Sent: Wednesday, December 25, 2002 11:09 PM > Subject: 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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).
