drop package. Where are the grants ?

Gokul

unread,

Mar 21, 2009, 11:14:55 AM3/21/09

to

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@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@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@sdd1cmtu
Connected.
SQL> drop package dummy_2;

Package dropped.

SQL> connect ua_system_app@sdd1cmtu
Connected.
SQL> select * from all_tab_privs where table_name = 'DUMMY_2';

no rows selected

SQL>

Robert Klemme

unread,

Mar 21, 2009, 11:17:14 AM3/21/09

to

Did you verify that the grants are really there once you recreate the
package?

Cheers

robert

Gokul

unread,

Mar 21, 2009, 11:21:14 AM3/21/09

to

No, I did not grant again. But the execution from user ua_system_app
is still possible. I dont understand why.

Michel Cadot

unread,

Mar 21, 2009, 11:39:48 AM3/21/09

to

"Gokul" <gokulkum...@gmail.com> a écrit dans le message de news:
782268ec-c2a5-492c...@a12g2000yqm.googlegroups.com...

| From some tests, I understand that when a package is dropped and
| recreated, the grants need not be given again.
|

This is wrong, when you drop a package all grants are
dropped at the same time and your test clearly shows it.

| I don't see those grants in the dictionary tables, but the package
| executes fines from ua_system_app user. Am I missing something ?

Nothing in your post proves what you say.
It may have "execute any procedure" privilege.

Regards
Michel

Gokul

unread,

Mar 21, 2009, 12:01:37 PM3/21/09

to

Ok. I have done the tests again.

connect ua_system_tables@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@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@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@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

Robert Klemme

unread,

Mar 21, 2009, 12:26:33 PM3/21/09

to

On 21.03.2009 16:21, Gokul wrote:
> No, I did not grant again. But the execution from user ua_system_app
> is still possible. I dont understand why.

And you did close the session of ua_system_app so nothing can be left in
the session context?

robert

Gokul

unread,

Mar 21, 2009, 12:37:32 PM3/21/09

to

I tried it again with a new session (all previous ones closed)..

sqlplus ua_system_app@sdd1cmtu

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 21 17:34:06 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining
Scoring Engine options

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 all_objects where object_name = 'DUMMY_2';

no rows selected

SQL>

Rgds,
Gokul

Michel Cadot

unread,

Mar 21, 2009, 1:47:22 PM3/21/09

to

"Gokul" <gokulkum...@gmail.com> a écrit dans le message de news:

9839b70f-95b0-485e...@h20g2000yqj.googlegroups.com...
|

<snip>

| -- How this works ??
|

<snip>

|
| Rgds,
| Gokul

Maybe it has the privilege through a role.

Regards
Michel

Gokul

unread,

Mar 21, 2009, 2:08:07 PM3/21/09

to

I checked user_role_privs as well. No rows selected.

But, when I execute the package without explicit grants the first
time, I get an error for insufficient privileges.

I will discuss this behaviour with my DBA and write what I find.

Rgds,
Gokul

Gokul

unread,

Mar 28, 2009, 11:44:50 AM3/28/09

to

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> @test

Package created.

Package body created.

SQL> @all_objects

OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------- ----------
TEST3 PACKAGE BODY 7793348
TEST3 PACKAGE 7793338

SQL> @grant

Grant succeeded.

SQL> @drop

Package body dropped.

Package dropped.

SQL> @test

Package created.

Package body created.

SQL> @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> @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> @drop

Package body dropped.

Package dropped.

SQL> create table test3 (i int);

Table created.

SQL> @all_objects

OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------- ----------
TEST3 TABLE 7793338

SQL> drop table test3;

Table dropped.

SQL> @test

Package created.

Package body created.

SQL> @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

johnb...@sbcglobal.net

unread,

Mar 28, 2009, 4:50:15 PM3/28/09

to

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 ?