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

As to your first question: yes, roles do not work in PL/SQL. The reason for this is roles are dynamic and pl/sql is compiled, so no guarantee it continues to keep working over time. Forms 4.5 still uses PL/SQL version 1, which runs at the client side and that probably is a completely different story.
Usually problems like this can be minimized by making sure the owner of the tables owns all procedures. At least you will get direct grants to one user only.
I'm not too sure why you're not using session_roles instead of dba_granted_roles. As far as I know session_roles continues to work in pl/sql.

Hth,

--
Sybrand Bakker, Oracle DBA
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 - 08:32:59 CST