Re: grant execute on package by DBA

From: <>
Date: Mon, 09 Feb 2009 15:13:29 +0100
Message-ID: <>

Comments embedded

On Mon, 9 Feb 2009 05:37:05 -0800 (PST), ciapecki <> 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:
>FUNCTION digits_only(input_string varchar) return varchar
> return chrispack.digits_only(input_string);
>-- 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.


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

Original text of this message