ORA-01039: insufficient privileges on underlying objects of the view

[shailesh]

Hello,

We have written function which is used in our application to check cost of queries. Although not helpful, but application has passed queries on data dictionary to this function and this has raised error "ORA-01039: insufficient privileges on underlying objects of the view". Our user has required privileges on view DBA_TAB_COLUMNS, on oracle 8i it was working, but when we migrated from 8i to 10g it is giving this error. In USER_TAB_PRIVS following privileges exist

On 8i: INSERT, UPDATE, DELETE, SELECT
On 10g: INSERT, UPDATE, DELETE, SELECT, REFERENCES, ON COMMIT REFRESH, FLASHBACK, DEBUG, QUERY REWRITE

Query and sample function code given below please help us to solve this issue.

SELECT Getcost('SELECT * FROM SYS.Dba_Tab_Columns WHERE Table_Name = ''TABLENAME''') FROM Dual;

-----------------------------------------------------
FUNCTION GetCost(Sqltext In Varchar2) RETURN NUMBER is
PRAGMA AUTONOMOUS_TRANSACTION;
nCost NUMBER;
cSeqName Varchar2(30) := NULL;
Stmt Varchar2 (4000) := NULL;

Type Cur1 IS REF Cursor;
C1 Cur1;
Begin

Execute Immediate 'SELECT SeqExplainPlan.Nextval FROM Dual' Into nPlanVal;

Name := 'I1PLAN'||nPlanVal;

Stmt:= 'Explain Plan Set Statement_Id=' ;
Stmt:= Stmt||''''||Name||''''||' For '||Sqltext;
Execute Immediate Stmt;
Commit;

Open C1 for
SELECT Cost
FROM Plan_Table
WHERE Statement_Id = Name
AND Id = 0;
Fetch C1 into nCost ;
Close C1;
RETURN nCost;

End;
/


Thanks & Regards,

Shailesh

[shailesh]

GRANT SELECT ANY DICTIONARY TO ;

has solved my problem.

Regards,

Shailesh