Thursday Aug 08, 2013

Reporting on User Roles in Fusion Applications

We often find a need to get a list of enterprise roles assigned to a Fusion Applications user, a need for a simple report. This can also be useful when there is no access to OIM screens, but only a simple read-only access is provided to the Fusion database. Below are certain simple SQL scripts that would assist in getting such a report. These scripts can be run by creating data model queries in BI Publisher if you are accessing a SaaS implementation or directly run in any SQL client if you are in an on-premise setup.

1. The SQL below can be used to get a list of roles assigned to an FA user:

SELECT a.USERNAME,
  c.ROLE_COMMON_NAME,
  c.ROLE_DISTINGUISHED_NAME
FROM PER_USERS a,
  PER_USER_ROLES b,
PER_ROLES_DN_VL c
WHERE a.USER_ID = b.USER_ID
AND b.ROLE_ID = c.ROLE_ID
AND a.USERNAME = '&username'

Below is a sample output from the SQL and the screenshot from OIM for the same user (FA user 'FUSION' is used for this example here).

OIM Screenshot for 'FUSION' user is below:


2. Further drill-down of the individual roles can be obtained using the query below which provides the detailed listing of roles inherited by a specific user session. The result from this query would match the results you see when drilling down 'Application Implementation Consultant', 'Employee' and 'IT Security Manager' above.

SELECT ROLE_NAME,
ROLE_GUID,
  SESSION_ID
FROM FND_SESSION_ROLES
WHERE  SESSION_ID IN
  (SELECT SESSION_ID
  FROM
    (SELECT SESSION_ID
    FROM FND_SESSIONS
    WHERE fnd_sessions.user_name = ‘&username’
    ORDER BY FIRST_CONNECT DESC
)
WHERE rownum<=1
)
ORDER BY role_name


The same result can also be obtained using the below query:

SELECT srs.ROLE_NAME
FROM FND_SESSIONS s,
FUSION.FND_SESSION_ROLE_SETS srs
WHERE s.SESSION_ROLE_SET_KEY = srs.SESSION_ROLE_SET_KEY
AND s.SESSION_ID IN
  (SELECT SESSION_ID
  FROM
    (SELECT b.SESSION_ID
    FROM FND_SESSIONS b
    WHERE b.USER_NAME = ‘&username’
    ORDER BY FIRST_CONNECT DESC
    )
  WHERE ROWNUM <= 1
)
ORDER BY srs.ROLE_NAME

The above queries, using FND_SESSIONS, will only be valid if the FA user has logged into Fusion Applications at any time (or if there is an active session of this user) and the user's login information exists in this table (not purged by any purge routines).

For a list of duties and privileges assigned to various job (or external) roles, please refer to My Oracle Support Reference Note: 1460486.1 Mapping of Roles, Duties and Privileges in Fusion Applications.

Keep visiting our blog for other useful tips and tricks in Fusion Applications.

About

This blog shares with the broader Fusion Applications community instructional material in the areas of Enterprise Structures, Extensibility, Integration and Security with the a focus on implementation. This blog is updated by the Fusion Applications Implementation Solutions Task force, part of the Fusion Applications Fusion Architecture organization.

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today