Re: Can't query sys.dba_* views from stored procedure

Packages and procedures can only access views to which direct grants of the privilege to use those views have been made. In other words, if you've got the permissions to use a DBA_ view because you've been given them via a *role*, then whilst you can see it at the SQL*Plus command line, you're packages and procedures won't.

You need an explicit grant of the form 'grant select on dba_tables to fred;' not 'grant dba to fred'.

Regards
HJR

--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================

"Stevan van der Werf" <traxx2319_at_hotmail.com> wrote in message
news:8c436463.0203280050.2684a610_at_posting.google.com...


> Hi,

>

> We want to use an app that verifies role-grant scripts generated by

> Oracle Designer to prevent having to drop an entire role and recreate

> it (which in some cases would take a lot of time and would cause an

> unability to work for over 100 users for quite some time).

> In order to do this I created a package which contains a procedure

> which queries the sys.dba_roles view. Whenever I compile this package

> I get:

> PLS-00201: identifier 'SYS.DBA_ROLES' must be declared

> Strange, because I can query the view anyway from the same user which

> owns and compiles the package.

> Is there anything I might be doing wrong (building a procedure which

> only queries the view, results in the same error-message) or is there

> a workaround for this?

>

> Any help would be much appreciated.

>

> Thanks in advance,

>

>

> Stevan van der Werf