Re: Check session role form trg/proc

From: Sridhar Subramaniam <avion_at_ozemail.com.au>
Date: 1995/11/22
Message-ID: <30B3FB69.416_at_ozemail.com.au>#1/1


Rune Nygård wrote:
>
> I can't figure out how to retrieve the session role from a
> trigger (or procedure).
> The SESSION_ROLES view gives zero rows back when i run it
> inside a trigger, but from sql*plus it works fine (of course)..
>
> I have also tried to use the USER_ROLE_PRIVS view, but it will
> only give me the role of the owner and not the current user.
>
> Look at my simple example under of how I have done it in my
> trigger..
>
> -Rune
>
> DECLARE
> CURSOR session_cur IS
> select role from session_roles;
> rolename session_roles.ROLE%TYPE;
>
> BEGIN
>
> open session_cur;
> loop
> FETCH session_cur INTO rolename;
> EXIT WHEN session_cur%NOTFOUND;
> dbms_output.put_line(rolename);
> end loop;
> close session_cur;
>
> -- further processing....
> END
Hi Rune,

Procedural objects execute in the privilege domain of the owner. You can't expect to get the same results from : * executing a query on session_roles / user_role_privs in SQL+ and * exec'ing a procedural object owned by someone else. The former would be the roles granted to you or active in that session.
The latter would give the roles granted/active for the owner of the procedural object.

What you can do is modify your procedural object to do the foll : User A creates this object, say:
DECLARE
    CURSOR session_cur IS
    select role
    from sys.dba_role_privs
    where grantee = user;
...
..
END; Grant execute to user B. If this object is executed by User B, he would now see the roles active he has active in that session.

Ask your DBA to grant select on sys.dba_role_privs to the creator of this procedural object.      

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : ssubrama_at_nibucorp.ccdn.otc.com.au / avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.
Received on Wed Nov 22 1995 - 00:00:00 CET

Original text of this message