Re: grant execute on package by DBA
Date: Mon, 9 Feb 2009 07:03:49 -0800 (PST)
Message-ID: <771924a3-5ce4-48db-aa4b-957cc753a8ba_at_m15g2000vbl.googlegroups.com>
On Feb 9, 8:22 am, ciapecki <ciape..._at_gmail.com> wrote:
> On Feb 9, 3:13 pm, sybra..._at_hccnet.nl wrote:
>
> > >How can I see who (I suppose DBA) granted EXECUTE on my whole package?
>
> > Just replace ALL by DBA.
> > And DBA is NOT a PERSON, it is a ROLE.
>
> as "a non-DBA user" I meant a user without DBA role!
>
> It means I do not have access to DBA_ objects.
>
> any other ideas?
>
> thanks,
> chris
No, since some DBA-privileged account did grant access to your package. Surprisingly this was SYS, and it happened when you granted execute on your 'wrapper' procedure to PUBLIC. Since your 'wrapper' procedure calls your packaged offering PUBLIC needs access to it to run the 'wrapper', thus SYS, with some creative recursive SQL, granted the necessary privilege on your package to PUBLIC. Unfortunately for you REVOKE doesn't behave the same way:
revoke execute on retrieverows_only from public
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
So now PUBLIC has access to your package as well as your 'wrapper'. If you REALLY want to keep that from occurring you'll need to actually create a standalone procedure that performs the same work as your packaged offering without calling the packaged counterpart. Which is silly, really, since you'll have duplicated code. Why you didn't simply grant execute on this package to PUBLIC in the first place is a mystery -- what deep, dark secrets does this package unlock that the unwashed can't access it?
David Fitzjarrell Received on Mon Feb 09 2009 - 09:03:49 CST