Hmm. Makes sense. Thanks Tim.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
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" <ORACLE-L_at_fatcity.com>
> 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" <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).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: Tim_at_SageLogix.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).
Received on Thu Dec 26 2002 - 17:23:40 CST
