Re: drop package. Where are the grants ?

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 Received on Sat Mar 28 2009 - 10:44:50 CDT