ORACLE Selecting from ALL_SOURCE yields empty result when executed from within routine

Question

I have a routine whose task is to extract code from ALL_SOURCE. This is done using the following cursor:

    CURSOR w_cur ( p_name VARCHAR2 , p_type VARCHAR2) IS
                   SELECT text , line
                     FROM ALL_SOURCE
                    WHERE UPPER(TYPE)  = UPPER(p_type)
                      AND UPPER(NAME)  = UPPER(p_name)
                      AND UPPER(OWNER) = UPPER(NVL(G_Owner_to_User,USER))
                    ORDER BY line
                    ;

I'm executing this routine from a user with DBA and SYS privileges. When attempting to extract the "BODY" of a "PACKAGE", if the package belongs to the same user running the program, I get the correct result. If, on the other hand, I attempt to extract the "BODY" of a package that belongs to another user, I get an empty result.

The interesting thing is that, when running the above "SELECT" from within SQL DEVELOPER (same user) I do get the expected code.

This looks like a "PRIVILEGE" related thing, but I have no idea why I'm getting different results and which privilege is missing.

Thanks for your comments.

Fernando.

Answer 1

Create the procedure with AUTHID CURRENT_USER and Oracle will use the invoker's roles to determine access privileges. The default is AUTHID DEFINER, which does not include privileges granted through a role (except for special roles like PUBLIC).

These functions show the difference between invoker's and definer's rights with ALL_SOURCE.

create or replace function count_source_invoker return number authid current_user is
    v_count number;
begin
    select count(*) into v_count from all_source;
    return v_count;
end;
/

create or replace function count_source_definer return number authid definer is
    v_count number;
begin
    select count(*) into v_count from all_source;
    return v_count;
end;
/

select
    (select count(*) from all_source) real_count,
    count_source_invoker,
    count_source_definer
from dual;

Results:

REAL_COUNT   COUNT_SOURCE_INVOKER   COUNT_SOURCE_DEFINER
----------   --------------------   --------------------
333677       333677                 142871