Bug? with wrong results from all_objects in stored plsql procedures

This page has been moved to http://andrewfraserdba.com/?p=32

Advertisements

4 Responses to “Bug? with wrong results from all_objects in stored plsql procedures”

  1. Nigel Thomas Says:

    Andrew

    I think this is a side effect of the not well enough known fact that by default PL/SQL procedures ignore privileges granted to you via roles (rather than directly). You need to create the procedure with invoker rights. See http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#18575.

    CREATE PROCEDURE create_dept (
    my_deptno NUMBER,
    my_dname VARCHAR2,
    my_loc VARCHAR2) AUTHID CURRENT_USER AS
    etc…

    HTH

    Regards Nigel

  2. Andrew Fraser Says:

    Thanks Nigel. I checked this and yes, “authid current user” stops the discrepancy in results. But note that the anomaly occurs (with authid definer, the default) even when the owner and the current user are one and the same.

    For example, in the below c+p I ran the code as the system user and I created the procedure as the system user. So:
    current user = SYSTEM; and
    definer = SYSTEM
    So it should not matter what authid is set to. But it does in fact – widely different results each way. Which is a bit of an unpleasant surprise.

    Andrew.

    SQL> sho user
    USER is "SYSTEM"
    SQL> set serverout on
    SQL> declare
    2 var1 number ;
    3 begin
    4 select count(*) into var1 from all_objects ;
    5 dbms_output.put_line('all_objects: '||var1) ;
    6 end ;
    7 /
    all_objects: 14559

    PL/SQL procedure successfully completed.

    SQL> create or replace procedure af_temp authid definer as
    2 var1 number ;
    3 begin
    4 select count(*) into var1 from all_objects ;
    5 dbms_output.put_line('all_objects: '||var1) ;
    6 end ;
    7 /

    Procedure created.

    SQL> exec af_temp ;
    all_objects: 5380

    PL/SQL procedure successfully completed.

    SQL> drop procedure af_temp ;

    Procedure dropped.

    SQL> create or replace procedure af_temp authid current_user as
    2 var1 number ;
    3 begin
    4 select count(*) into var1 from all_objects ;
    5 dbms_output.put_line('all_objects: '||var1) ;
    6 end ;
    7 /

    Procedure created.

    SQL> exec af_temp ;
    all_objects: 14560

    PL/SQL procedure successfully completed.

    SQL> drop procedure af_temp ;

    Procedure dropped.

    SQL> sho user
    USER is "SYSTEM"

  3. Gabriel Paulovic Says:

    Nigel got it right, roles are disabled (unless using invoker rights), all_objects contains only objects that a user has access to, all_objects shows you correctly only those objects you have access to. Try revoking roles from the user and the counts will match or grant all privileges granted via role directly to a user. Of course DBA users have DBA (or similar) role so the “discrepancy” seems larger, SYS has always access to all objects.
    So this is not an anomaly but a useful feature.

  4. Using 10g datapump and scheduler to copy schemas « Andrew Fraser DBA Says:

    […] Update 02-Mar-07: dba_ views rather than all_ views have to be used to identify what objects to drop prior to datapump, for reasons explained in this post. […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: