Re: grant execute on package by DBA

From: ddf <oratune_at_msn.com>
Date: Mon, 9 Feb 2009 10:42:07 -0800 (PST)
Message-ID: <5f901f34-1015-4ad4-bed6-14921c340afa_at_m22g2000vbp.googlegroups.com>

On Feb 9, 11:21 am, ciapecki <ciape..._at_gmail.com> wrote:
> On Feb 9, 4:03 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > 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?
>
> Hi David
>
> It's not entirely true. Please see below.
>
> I just did a test on my local db 10gR2.
>
> as user with DBA role called CHRIS.
>
> CREATE OR REPLACE
> function up(input_string varchar) return varchar2 is begin return
> chrispack.up(input_string); end;
> /
> GRANT EXECUTE ON up TO public;
>
> now I connected with a user TEST with minimal privileges
>
> I can run:
> select chris.up('asdf') from dual; -- ASDF
> but I cannot run:
> select chris.chrispack.up('asdf') from dual;
>               *
> ORA-00904: : invalid identifier
>
> This means that execute GRANT on up function that points to
> chrispack.up function does not automatically GRANTS EXECUTE priv on
> the whole package.
>
> My question still remains, how can I (is it possible) to list
> GRANTEES, GRANTORS on one of my objects (package) when I did not
> execute that GRANT?
>
> thanks,
> chris- Hide quoted text -
>
> - Show quoted text -

Not without DBA privilege.

David Fitzjarrell Received on Mon Feb 09 2009 - 12:42:07 CST