Re: Roles are not granted in PL/SQL ???

Yann,
The fastest way to solve this problem is to : Connect to database as sys
Grant select on dba_role_privs to PUBLIC recompile your procedure.

Your DBA may not be happy about this , but it is a way around roles in stored procedues.
BTW. look into script CATALOG.SQL in ...RDBMS7x\ADMIN subdirectory. You will find all USER_..., ALL_..., DBA_... views and synonyms defined there.
You can create your own views and grant privs on these only to owner of stored procedures.
(grant it to USER not to the ROLE)
This may keep your DBA happy.

Thomas Olszewicki
CPAS Systems Inc.
ThomasO_at_cpas.com
www.cpas.com

Yann Chevriaux <chevriaux_at_theleme.com> wrote in message news:38203DA6.A961EA28_at_theleme.com...
> Hi.
> I've got a problem with roles.
> I first develop an application with Forms 4.5 on Personnal Oracle
> 7.3.2.2.0
> It works and there are several roles defined and dispatched over some
> users.
>
> Some days ago, this application has been transferred on our Oracle
> Server:
> (NT 4, Oracle 7.3.2.3.1).
>
> And ... it doesn't work anymore.
>
> In fact I discover that procedures, functions and packages can't be
> compilated !
>
> The error occurs when I try to access objects for which I got privileges
> trough a role.
>
> i.e.:
>
> Assume I've been granted role DBA:
>
> THAT WORKS:
> SQL> select granted_role from dba_role_privs where grantee='DBA';
> GRANTED_ROLE
> ------------------------------
> ...
> ...
>
>
> THAT DOESN'T WORK:
> SQL> create or replace procedure foo
> 2 is
> 3 begin
> 4 for c in (select granted_role from dba_role_privs where
> grantee='TEST')
> 5 loop
> 6 dbms_output.put_line(c.granted_role);
> 7 end loop;
> 8 end;
> 9 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show err
> Errors for PROCEDURE FOO:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 4/12 PL/SQL: SQL Statement ignored
> 4/37 PLS-00201: identifier 'SYS.DBA_ROLE_PRIVS' must be declared
> 6/4 PL/SQL: Statement ignored
> 6/25 PLS-00364: loop index variable 'C' use is invalid
> SQL>
>
>
> Does it mean that privileges are not viewable trough roles when using in
> procedures, functions or packages ?
> As it works with PO7, is there some parameter I could change ?
>
> Please Help !!!!!
>
> Yann.
>
Received on Wed Nov 03 1999 - 09:43:02 CST