Re: PLS-00201: identifier SYS.DBA_DATA_FILES must be declared

OK, here's the deal. You can perform DML on a table or view with privileges from a role, BUT you can not compile a stored procedure/function/package/trigger which uses privileges from a role. The owner of the stored program must have been GRANTed the privileges explicitly. Why? Because a role is only enabled when a user is actually connected. User's can run a stored procedure without the owner of the SP being connected.

So, GRANT SELECT on DBA_DATA_FILES to the owner of UUU procedure, and it'll work. Actually, this is a bit of an advantage. Now you can GRANT EXECUTE on the SP to users or roles, and they don't have to have a SELECT grant (either explicitly or through a role) on DBA_DATA_FILES themselves.

Regards,

--
David C. Sisk
The Unofficial ORACLE on NT site
http://www.ipass.net/~davesisk/oont.htm

Ferran Foz wrote in message <7m4m9p$7r0$1_at_nnrp1.deja.com>...
>
>
>I'm running on a very strange problem.
>
>I'm logging in to Oracle 7 with a username that has roles
> RESOURCE, CONNECT, DBA.
>
>
>I can do a "desc dba_data_files" but the following procedure does
>not compile...
>
>But the following procedure:
>
>procedure uuu is
> kk varchar2(1);
>begin
> select '1' into kk
> from dba_data_files
> where rownum=1;
>end;
>
>Errors for PROCEDURE UUU:
>
>LINE/COL ERROR
>------------------------------------------------------------
>4/1 PL/SQL: SQL Statement ignored
>5/6 PLS-00201: identifier 'SYS.DBA_DATA_FILES' must be declared
>
>
>Any idea?
>
>Thanks a lot
>--
>-- Ferran Foz mailto:ferran.foz_at_technologist.com
>-- STE Consulting, SA http://www.ste.es
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Sun Jul 11 1999 - 21:29:18 CDT