4

Here's my situation (slightly envaguened to protect the innocent): I have a package on a Test system and a package on Prod system that need to have identical permissions to access exiting packages and functions. Both systems are Oracle. I use the following query to check existing permissions:

with objects as (
  select owner, table_name, listagg(privilege, ', ') WITHIN GROUP (ORDER BYPRIVILEGE) AS PRIVS
    from all_tab_privs_recd
   where grantee = 'EABINTEG'
   group BY owner, table_name
   order by owner, table_name)

select ob.owner as OWNER, ob.table_name as NAME, ao.object_type as TYPE, ob.PRIVS as GRANTS
  from objects ob left join all_objects ao on ob.owner = ao.owner
                                          and ob.table_name = ao.object_name
  order by ao.object_type, ob.owner, ob.table_name;

The queries come back almost identical, except Test has some rows that say the account has EXECUTE permissions granted for the PACKAGE BODY. The administrator has run queries to grant my account execute on the packages, I can see identical EXECUTE permissions granted for the PACKAGE on both Test and Prod.

I've looked around and haven't found anything. Has anyone else run up against this? How do I grant EXECUTE privileges for a PACKAGE BODY. Is this a SYNONYM issue? Help! Thanks!

3

Anything else in addition to EXECUTE? When granting DEBUG, you implicitly "get" EXECUTE on the PACKAGE BODY (I replaced EABINTEG with U2 in your query):

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> create package u1.p1 as
  2  procedure pr1;
  3  end;
  4  /

Package created.

SQL> create or replace package body u1.p1 as
  2  procedure pr1 as
  3    begin
  4      null;
  5    end;
  6  end;
  7  /

Package body created.

SQL> grant execute on u1.p1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.

SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             EXECUTE

Seems ok so far.

SQL> conn u1/u1
Connected.
SQL> grant debug on u1.p1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.

SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             DEBUG, EXECUTE
U1    P1    PACKAGE BODY        DEBUG, EXECUTE

If you revoke DEBUG now, the PACKAGE BODY row disappears.

SQL> conn u1/u1
Connected. 
SQL> revoke debug on u1.p1 from u2;

Revoke succeeded.

SQL> conn u2/u2
Connected.
SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------ 
U1    P1    PACKAGE             EXECUTE
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.