Hmm. Makes sense. Thanks Tim. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 26, 2002 2:34 PM
> I don't agree that anyone "shirked". Roles are, by design, changeable > within a session. The SET ROLE command is not DDL, altering the metadata of > the database. Instead, it is only altering already-granted permissions to > used subsequently by the session. So, why should "permanent" objects (such > as views, procedure, packages, triggers, etc) be created using permissions > which are inherently transitory (i.e. available via roles)? Just because > very few people use SET ROLE during a session doesn't alter its basic > properties... > > When that note says that "complexity would be raised to the Nth degree", > they are not necessarily indicating that Oracle could not have implemented > it. This stuff is simplicity itself compared to the transaction-consistency > model. Rather, the complexity would have been on the database > administration side (not in the database engine), and a major pain in > everyone's behind. Think it through. Oracle made a good design decision to > prevent unnecessary complexity in database administration. > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, December 26, 2002 2:13 PM > > > > 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" <[EMAIL PROTECTED]> > > 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: <[EMAIL PROTECTED]>; "Shaleen" <[EMAIL PROTECTED]> > > > 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: [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: Khedr, Waleed > > > 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: Shaleen > > 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: Tim Gorman > 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: Shaleen 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).
