Re: privilege explain plan top sessions all_objects

[Quoted] 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] Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri May 16 2003 - 19:25:51 CEST