Re: grant execute on package by DBA

From: ciapecki <ciapecki_at_gmail.com>
Date: Mon, 9 Feb 2009 09:21:02 -0800 (PST)
Message-ID: <c9816237-e162-4780-b558-8b9e096fbe8b_at_w39g2000prb.googlegroups.com>

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 Received on Mon Feb 09 2009 - 11:21:02 CST