Re: drop package. Where are the grants ?

From: Gokul <gokulkumar.gopal_at_gmail.com>
Date: Sat, 21 Mar 2009 09:01:37 -0700 (PDT)
Message-ID: <9839b70f-95b0-485e-831c-0f0ad069ae9e_at_h20g2000yqj.googlegroups.com>



Ok. I have done the tests again.

connect ua_system_tables_at_sdd1cmtu
Connected.

create or replace package dummy_2
as
int;
procedure p(j int);
end;
/

Package created.

create or replace package body dummy_2
as
procedure p (j int)
as
begin
null;
end;
end;
/

Package body created.

grant execute on dummy_2 to ua_system_app;

Grant succeeded.

connect ua_system_app_at_sdd1cmtu

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> exec ua_system_tables.dummy_2.p(1);

PL/SQL procedure successfully completed.

SQL> connect ua_system_tables_at_sdd1cmtu Connected.

SQL> drop package dummy_2;

Package dropped.

create or replace package dummy_2
as
i int;
procedure p(j int);
end;
/

create or replace package body dummy_2
as
procedure p (j int)
as
begin
null;
end;
end;
/

Package created.

SQL> SQL> Package body created.

SQL> connect ua_system_app_at_sdd1cmtu

  • How this works ??

SQL> exec ua_system_tables.dummy_2.p(1);

PL/SQL procedure successfully completed.

SQL> select * from all_tab_privs where table_name = 'DUMMY_2';

no rows selected

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE
ADM
  • --- UA_SYSTEM_APP CREATE VIEW NO UA_SYSTEM_APP CREATE TABLE NO UA_SYSTEM_APP CREATE SESSION NO UA_SYSTEM_APP CREATE SYNONYM NO
SQL> Rgds,
Gokul Received on Sat Mar 21 2009 - 11:01:37 CDT

Original text of this message