Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: unable to create stored outline for sql inside a procedure --

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

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 26 Dec 2002 12:39:04 -0800
Message-ID: <F001.005228AE.20021226123904@fatcity.com>


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

>
> 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).
Received on Thu Dec 26 2002 - 14:39:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US