Re: grant execute on package by DBA

From: <sybrandb_at_hccnet.nl>
Date: Mon, 09 Feb 2009 15:13:29 +0100
Message-ID: <h7e0p4luhi6nv22su6jg9o8r2maqvev2bo_at_4ax.com>



Comments embedded

On Mon, 9 Feb 2009 05:37:05 -0800 (PST), ciapecki <ciapecki_at_gmail.com> wrote:

>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.
>

DBA is a ROLE not a PERSON!!
>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.
>

This is, really sorry to say so, EXTREEMLY stupid.

>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!
>

You don't mention a version, so there is nothing to reproduce.

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

Obviously, as you never granted any privilege. Apart from that, you are selecting from an incorrect data dictionary view. ALL_ ... views lists anything YOU HAVE ACCESS TO. As you don't have access to the package it isn't listed.

>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?
>

Just replace ALL by DBA.
And DBA is NOT a PERSON, it is a ROLE.

>thanks,
>chris

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Mon Feb 09 2009 - 08:13:29 CST

Original text of this message