Re: drop package. Where are the grants ?

From: <johnbhurley_at_sbcglobal.net>
Date: Sat, 28 Mar 2009 13:50:15 -0700 (PDT)
Message-ID: <41d89237-db5e-4784-a163-197d4d2caf1d_at_v15g2000yqn.googlegroups.com>



On Mar 28, 11:44 am, Gokul <gokulkumar.go..._at_gmail.com> wrote:
> Ok. I did some tests and this is what I find.
>
> The object retains the object id even after drop and create. Test
> files and tests below.
>
> SQL> host cat test.sql
> create package test3
> is
> procedure check3;
> end test3;
> /
>
> create package body test3
> is
> procedure check3
> is
> begin
> null;
> end check3;
> end test3;
> /
>
> SQL> host cat grant.sql
> grant execute on test3 to ua_system_app;
>
> SQL> host cat revoke.sql
> revoke execute on test3 from ua_system_app;
>
> SQL> host cat all_objects.sql
> select object_name,object_type,object_id from all_objects where
> object_name = 'TEST3';
>
> SQL> host cat drop.sql
> drop package body test3;
>
> drop package test3;
>
> SQL> _at_test
>
> Package created.
>
> Package body created.
>
> SQL> _at_all_objects
>
> OBJECT_NAME            OBJECT_TYPE     OBJECT_ID
> ------------------------------ -------------------    ----------
> TEST3                          PACKAGE BODY   7793348
> TEST3                          PACKAGE             7793338
>
> SQL> _at_grant
>
> Grant succeeded.
>
> SQL> _at_drop
>
> Package body dropped.
>
> Package dropped.
>
> SQL> _at_test
>
> Package created.
>
> Package body created.
>
> SQL> _at_all_objects
>
> OBJECT_NAME            OBJECT_TYPE      OBJECT_ID
> ------------------------------ ------------------- ----------
> TEST3                          PACKAGE BODY  7793348
> TEST3                          PACKAGE            7793338
>
> -- Here, the object id 7793338 for the package is retained even after
> drop and create. I think due to this the grant for the other user
> -- is still retained. I have not posted the test on the second session
> due to formatting constraints.
>
> SQL> _at_revoke
> revoke execute on test3 from ua_system_app
> *
> ERROR at line 1:
> ORA-01927: cannot REVOKE privileges you did not grant
>
> -- Now, if I try to revoke, the revoke fails. But it actually revokes.
> The user 2 is not able to access the package anymore. I think
> -- this is a bug ?
>
> -- If I trick to change the object id by creating a intermediate
> object, then the object id for the package changes and the grants
> -- are no longer valid
>
> SQL> _at_drop
>
> Package body dropped.
>
> Package dropped.
>
> SQL> create table test3 (i int);
>
> Table created.
>
> SQL> _at_all_objects
>
> OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID
> ------------------------------ ------------------- ----------
> TEST3                          TABLE                  7793338
>
> SQL> drop table test3;
>
> Table dropped.
>
> SQL> _at_test
>
> Package created.
>
> Package body created.
>
> SQL> _at_all_objects
>
> OBJECT_NAME            OBJECT_TYPE    OBJECT_ID
> ------------------------------ -------------------   ----------
> TEST3                          PACKAGE            7793349
> TEST3                          PACKAGE BODY  7793350
>
> -- Now, when there is a change of object id, the grants are no longer
> valid. So, even if the package is created using
> -- "create or replace" it is always safe to execute grants as the
> intermedite table objects changes the object id
>
> Any thoughts ?
>
> Rgds,
> Gokul

It does sound like a bug to me ... I did not take the time to run your test case on any of my systems yet.

Probably time to let some other people run it from here in a couple more environments and then package it up to oracle for submission.

Don't hold your breath probably will be fixed in time for 12.1 ? Received on Sat Mar 28 2009 - 15:50:15 CDT

Original text of this message