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

Stevan,

when running a PL/SQL procedure/function/etc (but NOT an annonymous block) then any objects being accessed MUST have permissions granted directly and not via a role.

When running a PL/SQL procedure/etc owned by another user and to which you have execute privs, means that the procedure runs as the OWNER and not as the CALLER. In Oracle 8 (8i I think) you can define the AUTHID clause to make it run as the CALLER rather than the OWNER.

So, if the proc you wrote is owned by and run by USER_A, then you need to login as SYS and grant select on dba_roles to user_a;

And now the proc will work.

HTH Regards,
Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com


------------------------------------------------------------------------

-----Original Message-----

From: traxx2319_at_hotmail.com (Stevan van der Werf) [mailto:traxx2319_at_hotmail.com]
Posted At: Thursday, March 28, 2002 8:50 AM Posted To: server
Conversation: Can't query sys.dba_* views from stored procedure Subject: Can't query sys.dba_* views from stored procedure

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 Received on Thu Mar 28 2002 - 04:10:41 CST