Re: drop package. Where are the grants ?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 21 Mar 2009 16:17:14 +0100
Message-ID: <72keo2FqikqnU1_at_mid.individual.net>

On 21.03.2009 16:14, Gokul wrote:
> From some tests, I understand that when a package is dropped and
> recreated, the grants need not be given again.
>
> I don't see those grants in the dictionary tables, but the package
> executes fines from ua_system_app user. Am I missing something ?
>
> SQL> connect ua_system_tables_at_sdd1cmtu
> Connected.
> SQL> create or replace package dummy_2
> 2 as
> 3 i int;
> 4 end;
> 5 /
>
> Package created.
>
> SQL> create or replace package body dummy_2
> 2 as
> 3 begin
> 4 null;
> 5 end;
> 6 /
>
> Package body created.
>
> SQL> grant execute on dummy_2 to ua_system_app;
>
> Grant succeeded.
>
> SQL> connect ua_system_app_at_sdd1cmtu
> Connected.
>
> SQL> select * from all_tab_privs where table_name = 'DUMMY_2';
>
> GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME
> PRIVILEGE
> ------------------------------ ------------------------------
> ---------------
> UA_SYSTEM_TABLES uA_SYSTEM_APP UA_SYSTEM_TABLES DUMMY_2
> EXECUTE
>
> SQL> connect ua_system_tables_at_sdd1cmtu
> Connected.
> SQL> drop package dummy_2;
>
> Package dropped.
>
> SQL> connect ua_system_app_at_sdd1cmtu
> Connected.
> SQL> select * from all_tab_privs where table_name = 'DUMMY_2';
>
> no rows selected
>
> SQL>
Did you verify that the grants are really there once you recreate the package?

Cheers

        robert Received on Sat Mar 21 2009 - 10:17:14 CDT