Tuesday Dec 10, 2013

Auditing in Fusion Applications

Release 7 of Fusion Applications provides the much needed functionality of auditing, leveraging the Fusion Middleware auditing capabilities. The functionality provided in this release covers the auditing of various applications business objects and the Fusion middleware components, including the below:

  • Fusion Applications Business Objects
  • Oracle SOA Suite –SOA Metadata Customizations
  • Pages and Business Objects Extensibility
  • BI Publisher – Report request, report execution, etc.
  • ODI, ESS, MDS, OPSS

In Release 7, the audit framework provided covers both capturing and reporting the audit events. Business objects or events to be audited can be configured using Manage Audit Policies in Fusion Applications while the reporting on these captured audit events is facilitated using Audit History UI. Users with appropriate roles will be able to configure (Manage Audit Policies with Application Administrator Job Role) and view these reports (Audit History UI with Internal Auditor Job Role).

The following Oracle University sessions provide a detailed overview of the auditing functionality available in Fusion Applications.

  1. Fusion 11g Release 7 (11.1.7.0.0) TOI: Technical Overview of Audit Trail – A technical overview of configuring audit capture and audit reporting
  2. Auditing in Fusion Applications – Provides an overview of auditing the various business objects in Fusion Applications
  3. Security Audit and Reporting in Fusion Applications Release 7 – An overview of the OPSS audit in Fusion Applications

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
« April 2014
SunMonTueWedThuFriSat
  
1
2
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
   
       
Today