Re: Grant Execute On all packages of another user

On Fri, 29 Oct 1999 08:39:49 +0200, AK <akriket_at_sqli.fr> wrote:

>Hello,
>
>I use OWAS 3.0 and Oracle 7.3.4 and i have developped an application
>under one schema (one user).
>Now, i want to use the DataBase-Controlled Authentification. So, each
>user will connect whith his database username and
>password.
>However, i want that these users can execute all the package of the
>application who are stored in another schema.
>I think that i have to create synonyms for each package and to grant
>execute too.
>How can i do this.
>Thanks.
>
>

You need to grant execute on all packages to a role. Then you need to grant that role to all users. Also you need to create public synonyms for all packages.

basically (I will demonstrate the principle for one script only) you can use sql*plus to do this, for example

set feedback off pagesize 0
spool %TEMP%\cregrn.sql
select 'grant execute on '||object_name||' to <any_role>' from user_objects
where object_type in ('PROCEDURE','FUNCTION','PACKAGE') /
SPOOL OFF
SET FEEDBACK ON
SPOOL %TEMP%\CREGRN.LOG
START %TEMP%\CREGRN.SQL
SPOOL OFF
HOST DEL %TEMP%\CREGRN.SQL public synonyms can be handled in the same fashion. You'll need create public synonym privilege to do this.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Oct 29 1999 - 03:06:05 CDT