Re: PL/SQL function problem

There are two issues your stored function is facing:

1. USER_ views and stored/packaged procedures/functions;
2. Differences between user logon and the way how ORACLE executes stored/packaged procedures/functions.

When you execute a stored/packaged procedure/function ORACLE switches to procedure/function owner's security domain.In order to implement that, ORACLE changed USER_ views. ORACLE V6 USER_ views were referencing ORACLE function UID to select user objects. In V7 USER_ views are referencing ORACLE function USERENV('SCHEMAID'). Now by simply changing schema id to procedure/function owner's ORACLE USER_ views will point to procedure/function owner's objects.
Moral: Never use USER_ views in procedure/function to get info on USER (who will run it) objects. Instead query ALL_ or DBA_ views WHERE OWNER=USER. ORACLE function USER always returns login user name even within procedure/function. SESSION_ROLES view references USERENV('SCHEMAID'). You can find SESSION_ROLES definition by executing:

 SQL> set long 2000
 SQL> select text from dba_views where view_name='SESSION_ROLES';

   select u.name

     from  x$kzsro,user$ u
     where kzsrorol!=userenv('SCHEMAID')
       and kzsrorol!=1
       and u.user#=kzsrorol

But even if you would execute your function being logged on to ORACLE as function owner it still would not work. As part of user logon process ORACLE checks if granted role is a default role and if it is, enables it (It also happens when user executes SET ROLE statement or DBMS_SESSION.SET_ROLE procedure). You can check SESSION_ROLES view for currently enabled roles. When you execute a stored/packaged procedure/function ORACLE simply switches to procedure/function owner's security domain. Do not confuse it with login. Switching to procedure/function owner's security domain does not enable procedure/function owner's default roles, therefore querying SESSION_ROLES within procedure/function always returns no rows.

Solomon Yakobson.
In article <32D561E3.3824_at_hort.cri.nz>,   mwest_at_hort.cri.nz wrote:
>
> I have to following function which i want to return whether a user has a
> specified role assigned and then return the answer to a paradox app.
>
> Can someone tell me why the function always returns 'NORMAL ACCESS' even
> though the desired role is assigned for the user.
>
> FUNCTION DETERMINEACCESS
> RETURN varchar2
> IS
> BEGIN
> declare
> cursor c1 is
> select ROLE from SESSION_ROLES;
>
> Role_value varchar2(30);
>
> BEGIN
> open c1;
> fetch c1 into Role_value;
>
> while c1%FOUND
> LOOP
> if Role_value = 'ADMIN_ROLE' then
> return('ADMIN ACCESS');
> end if;
> fetch c1 into Role_value;
> END LOOP;
>
> return('NORMAL ACCESS');
> close c1;
> END;
> END;

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet