grant execute on package by DBA

From: ciapecki <ciapecki_at_gmail.com>
Date: Mon, 9 Feb 2009 05:37:05 -0800 (PST)
Message-ID: <31fd6f89-14d4-4de9-a871-c611c102f82d_at_d36g2000prf.googlegroups.com>

I would like to find out if there is a possibility (as a non-DBA user) to list all objects (owned by that user) including the grants done by DBA on these objects.

I have a package called chrispack.
Whenever I want to expose a procedure or function from within that package I create this function unpackaged and just return in the body call to my package.
Then I can grant execute only on this unpackaged function/procedure.

e.g. in my schema USERA I have a function called DIGITS_ONLY: CREATE OR REPLACE
FUNCTION digits_only(input_string varchar) return varchar is
begin

    return chrispack.digits_only(input_string); end;
/

• Grants for Function GRANT EXECUTE ON digits_only TO public /

Then I tried to log in with the USERB credentials

I can
select USERA.digits_only('asd2f1') from dual; but as well:
select USERA.chrispack.digits_only('asd2f1') from dual;

this second call should not succeed!

when I check with:
select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'CHRISPACK'
-- no rows returned

select * from all_tab_privs a
where a.privilege = 'EXECUTE'
and a.table_name = 'DIGITS_ONLY'
-- 1 row as expected

How can I see who (I suppose DBA) granted EXECUTE on my whole package?

thanks,
chris Received on Mon Feb 09 2009 - 07:37:05 CST