Re: grant execute on package by DBA

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 10 Feb 2009 18:37:11 +0100
Message-ID: <4991bb46$0$2853$ba620e4c_at_news.skynet.be>

ciapecki schreef:
> 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

You should check as USERB what privileges you have been granted, ie. where USERB is the grantee.

Maybe USERB has been granted "execute any procedure". Received on Tue Feb 10 2009 - 11:37:11 CST