Re: privilege explain plan top sessions all_objects

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<tn7acv8m3d75itlqhh5j1jroc2h7a5frel_at_4ax.com>...
> On 15 May 2003 16:41:09 -0700, joel-garry_at_home.com (Joel Garry) wrote:
>
> >Oracle 8.1.7.2 hp-ux 11.0 OEM 2.2 W2K Pro
> >
> >I'm trying to figure out what is wrong with a vendor supplied OCI
> >generated bit of sql that throws an ORA-923 (perhaps because it is
> >looking up it's own list of tables, for a table that it doesn't know
> >about because it was added through Oracle - whatever). I'm looking at
> >the cursors through Top Sessions, at where it's code is getting a list
> >of tables and views from Oracle. I'm logged in as SYSTEM, which has
> >the DBA role, including select any table. Most of the cursors I can
> >get an explain plan on, but on this particular one it tells me
> >ORA-1039, insufficient privileges on underlying objects of the view.
> >Then it shows me the code with no explain plan. The code (owner
> >obscured by me) is:
> >
> > SELECT DECODE (OBJECT_TYPE,'TABLE','T','VIEW','V','X') FROM
> >ALL_OBJECTS WHERE OBJECT_NAME = :1 AND OWNER = 'XXXXX'
> >
> >Which view do I have insufficient privilege on? System can certainly
> >see sys.obj$ and sys.user$ and the owner has select on them. Or is
> >this telling me that the owner can't look at it's own ALL_OBJECTS? Or
> >does the owner have to grant something on ALL_OBJECTS to system? Or
> >did I miss something about explain plan and sys objects? Or is OEM
> >unsure how privilege works?
> >
> >jg
>
>
> Views are compiled. You have access through a role.
> PERIOD.
[Quoted] OK, so SYSTEM has access through the DBA role. Why is it having a problem? It doesn't have problems with other cursors from the same session.

Running this same experiment on another database that does not have a plan table doesn't give an error! So that leads me to think it must have something to do with the plan table access by SYSTEM. Hmmmmm... what could be different about this cursor that it would block access to the plan table? The problem is replicable... adding the plan table to the other database also shows it. I don't have to get the 923 error to see this effect either, the OCI code will perform the ALL_OBJECTS stuff if I put in any table that doesn't exist, and top sessions will consistently give the error on only that cursor.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

jg

--
_at_home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/fri/business/news_1b16gateway.html