Re: Select From ALL_SOURCE

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/22
Message-ID: <34cacaba.5176293@192.86.155.100>#1/1

On 22 Jan 1998 20:07:34 GMT, "Eric Fredriksen" <fredrike_at_reston.btna.com> wrote:

>The following Select statement results in ORA-1403 when executed from a
>procedure within a package, but it successfully returns a single row when
>run from SQL Worksheet.
>
>SELECT DISTINCT(TYPE) INTO src_type
>FROM ALL_SOURCE
>WHERE rtrim(owner) = rtrim(src_owner)
>AND rtrim(name) = rtrim(src_name)
>AND type != 'PACKAGE BODY';
>
>The package is owned by the DBA user.
>
>Any suggestions?
>

The ALL_* data dictionary views show you only objects you have access to andwhen queried in a procedure show only objects the owner of the procedure has DIRECT access to (as opposed to via a role).

Stored procedures always run with the priveleges granted directly to the owner of the procedure. The ALL_* data dictionary views obey that same logic -- they only return the data you can see in the environment you are executing in.

Try this:

SQL> select count(*) from ALL_SOURCE;
SQL> set role none;
SQL> select count(*) from ALL_SOURCE;

You'll probably get 2 different numbers. When you disable the roles you'll be working in the environment your stored procedure is. Also, if you create a procedure that queries ALL_SOURCE and someone else executes it, they will get the same answer you got when you ran it since the procedure is effectively executing as you -- not them.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 22 1998 - 00:00:00 CST