Re: Newbie: Select in PL/SQL

In article <9ri24l$5pi$1_at_netnews.upenn.edu>, gregoryk_at_futures.wharton.upenn.edu says...
>
>Thomas Kyte (tkyte_at_us.oracle.com) wrote:
>
>: sorry -- more specifically read:
>
>: http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html
>
>
>Thanks very much for your help. That explains exactly what's going on
>and my solution, I don't understand why yet, but thanks!
>
>Greg

The simple answer to why is simply that "you have access to the tables you are trying to build a compiled stored object on".

Your user has DBA.

DBA is a role.

The DBA role has the "select any table" priv.

Roles are not enabled during the compilation of a procedure (ever, even with invokers rights).

Hence you cannot compile an object to which you have access to via a role.

If you want the really long version of this story -- I have it in my book, it takes a couple of pages to go through but it shows that due to the fluidity of roles and how the dependency mechanism in Oracle is implemented, using privs from roles would be a disaster in a real system.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp