Re: What are user's active roles

Perhaps this will work for you. This is a read-only query that finds responsibilities for users. You can limit what users and what responsibilities to view, or see 'all' of either or both.

• Don't copy this line. SET heading OFF - verify OFF - echo OFF

SET serveroutput ON -
 feedback OFF -
 termout ON -
 pagesize 66 -
 linesize 86

COLUMN "User Name"  FORMAT A8
COLUMN "Responsibility" FORMAT A25 WORD
COLUMN "Start"  FORMAT A10
COLUMN "Application" FORMAT A40 TRUNC

BREAK ON "User Name" SKIP 1 ON "Start" ON "Application"

COLUMN the_time NEW_VALUE my_time NOPRINT COLUMN the_parm NEW_VALUE my_parm NOPRINT

SELECT ' You will be prompted for a USER NAME or substring (any part of a name)'
 || CHR(10) ||
 ' and then for a RESPONSIBILITY or substring (any part of responsibility).'
 || CHR(10) || CHR(10) ||
 ' You can also just hit "Enter" at either or both of the prompts, to accept'
 || CHR(10) ||
 ' the defaults and view ALL RESPONSIBILITIES and / or ALL NAMES.'  || CHR(10) || CHR(10)
FROM dual
/

ACCEPT v_name PROMPT "User Name or substring (default is '%') : " ACCEPT v_resp PROMPT "Responsibility or substring (default is '%'): "

--SELECT 'You can use this to see "Who does What" in ' || name ||

• '. Queried on ' || TO_CHAR( sysdate, 'dd-Mon-yyyy') ||
• ', looking for User Name like ''%' || UPPER( '&&v_name') ||
• '%'', and / or Responsibility like ''%' || UPPER( '&&v_resp') ||
• '%.''' SELECT 'Query Responsibilities Like ' || DECODE( '&&v_resp' , NULL, '-ALL-' , '%' || UPPER( '&&v_resp') || '%') || ' for User Like ' || DECODE( '&&v_name' , NULL, '-ALL-' , '%' || UPPER( '&&v_name') || '%') the_parm , 'In ' || name || ', on ' || TO_CHAR( sysdate, 'dd-Mon-yyyy "at" hh24:mi "(System Time)"') the_time FROM v$database, dual /

SET heading ON -
 feedback ON

TTITLE CENTER my_parm SKIP -
 CENTER my_time SKIP 2

BTITLE SKIP 2 LEFT 'Page ' FORMAT 999 sql.pno -  RIGHT 'WHAT_RESP.SQL' SELECT SUBSTR( user1.user_name, 1, 20) "User Name"  , SUBSTR( resp.responsibility_name, 1, 25) "Responsibility"
-- , resp.responsibility_name "Responsibility"

• Could use this to show 'complete' Responsibility_Name , user_resp.start_date "Start" , appl.application_name "Application" FROM applsys.fnd_user user1 , applsys.fnd_responsibility resp , applsys.fnd_user_responsibility user_resp , applsys.fnd_application appl WHERE user1.user_id = user_resp.user_id AND user_resp.responsibility_id = resp.responsibility_id AND resp.application_id = appl.application_id AND user_resp.end_date IS NULL AND UPPER( user1.user_name) LIKE UPPER( '%&&v_name%') AND UPPER( resp.responsibility_name) LIKE UPPER( '%&&v_resp%') ORDER by "User Name", "Responsibility" /

CLEAR COLUMN TTITLE OFF
BTITLE OFF
--Obtained from Alok & Shalini Chadda's Oracle Applications Page
--http://www.geocities.com/SiliconValley/Network/9828/
--Original author name unknown. Modified by Chris Nelson, Eaton MAD /
--Oracle Implementation Team, Mar 1999

--Use to find various Users and Responsibilities in the system
--Saved as 'What_Resp.SQL'

*********************  Don't copy this line.

Juhan Kundla wrote:

> Is there a view for dba to see what roles of a user are currently
> active?
>
> Juhan
> --
> ================================================================
> |\ ||   /\   |__ /   /\   || \ || \    /\   |__ | |____|\\    //
> | \||  //\\    //   //\\  || / || /   //\\     || |\     \\  //
> ||\ | //__\\  //   //__\\ ||\\ ||  \ //__\\    || |/      \\//
> || \|//    \\/___|//    \\|| \\||__///    \\\___/ |____|   \/
>
> (c) Juhan Kundla 1998    mailto:juhan_at_ensib.ee     tel.: 6726291
> ================================================================