OracleFree generatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS User and Responsibility Audit SQL Generator

Auditors ask for the same evidence every year: who has which responsibility, who holds system administrator and who has not logged in for months. This generator writes the queries so you can run them and export the result.

Fill in

User name pattern

Use % as a wildcard, for example JSMITH% or %

Responsibility name pattern

Matches the responsibility name, case insensitive

Responsibility key for administrator check

The RESPONSIBILITY_KEY value

Last logon window in days

Generated code

Download:

1. Active users and their active responsibilities

SELECT fu.user_name,
       fu.description,
       frv.responsibility_name,
       fa.application_short_name,
       urg.start_date AS resp_start,
       urg.end_date   AS resp_end,
       fu.last_logon_date
FROM   fnd_user fu,
       fnd_user_resp_groups_direct urg,
       fnd_responsibility_vl frv,
       fnd_application fa
WHERE  fu.user_id = urg.user_id
AND    urg.responsibility_id = frv.responsibility_id
AND    urg.responsibility_application_id = frv.application_id
AND    frv.application_id = fa.application_id
AND    UPPER(fu.user_name) LIKE UPPER('%')
AND    UPPER(frv.responsibility_name) LIKE UPPER('%Payables%')
AND    (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND    (urg.end_date IS NULL OR urg.end_date > SYSDATE)
ORDER  BY fu.user_name, frv.responsibility_name;

2. Who holds the administrator responsibility

SELECT fu.user_name,
       fu.description,
       fu.last_logon_date,
       urg.start_date,
       urg.end_date
FROM   fnd_user fu,
       fnd_user_resp_groups_direct urg,
       fnd_responsibility fr
WHERE  fu.user_id = urg.user_id
AND    urg.responsibility_id = fr.responsibility_id
AND    urg.responsibility_application_id = fr.application_id
AND    fr.responsibility_key = 'SYSTEM_ADMINISTRATOR'
AND    (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND    (urg.end_date IS NULL OR urg.end_date > SYSDATE)
ORDER  BY fu.user_name;

3. Responsibilities assigned but not used recently

SELECT fu.user_name,
       frv.responsibility_name,
       fu.last_logon_date
FROM   fnd_user fu,
       fnd_user_resp_groups_direct urg,
       fnd_responsibility_vl frv
WHERE  fu.user_id = urg.user_id
AND    urg.responsibility_id = frv.responsibility_id
AND    urg.responsibility_application_id = frv.application_id
AND    (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND    (urg.end_date IS NULL OR urg.end_date > SYSDATE)
AND    (fu.last_logon_date IS NULL OR fu.last_logon_date < SYSDATE - 90)
AND    UPPER(fu.user_name) LIKE UPPER('%')
ORDER  BY fu.user_name;

4. Count of responsibilities per user

SELECT fu.user_name,
       COUNT(*) AS active_responsibilities
FROM   fnd_user fu,
       fnd_user_resp_groups_direct urg
WHERE  fu.user_id = urg.user_id
AND    (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND    (urg.end_date IS NULL OR urg.end_date > SYSDATE)
AND    UPPER(fu.user_name) LIKE UPPER('%')
GROUP  BY fu.user_name
ORDER  BY active_responsibilities DESC;

Run as a read-only user such as APPS_READ or through APPS in a test instance first. FND_USER_RESP_GROUPS_DIRECT lists directly assigned responsibilities. Roles granted through the User Management (UMX) framework can appear in WF_USER_ROLE_ASSIGNMENTS instead, so add that source if your instance uses role-based access. Verify column names on your release.

Access review you can query yourself

Ask how Netray can give auditors a read-only, explainable view of EBS user access.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter a user pattern and a responsibility pattern, then copy the queries into SQL Developer or SQL*Plus connected to your EBS database. Use % as a wildcard. Leave the user pattern as % to review every user. Export the result sets to Excel and attach them to the access review ticket along with the date and the instance name.

Query 1 is the general access listing
Query 2 isolates administrator access
Query 3 finds access that may not be needed
Query 4 spots users with unusually many responsibilities

Tables behind the queries

FND_USER holds the login, its start and end dates and the last logon. FND_USER_RESP_GROUPS_DIRECT links a user to responsibilities, with its own start and end dates. FND_RESPONSIBILITY_VL supplies the translated responsibility name and FND_RESPONSIBILITY carries the stable RESPONSIBILITY_KEY. Both the user and the assignment must be unexpired for the access to be effective, which is why each query checks the two end dates.

Reading the results

A short list of named administrators is healthy. Many administrator accounts, or accounts that have never logged in, are findings. For segregation of duties, look for users holding both an entry and an approval responsibility in the same module, such as a payables invoice entry role with a payment release role. Note that responsibility names are customer specific, so map them to your own SoD matrix.

Common mistakes

Forgetting the end date test returns revoked access as if it were live. Searching on responsibility name misses renamed or translated copies, so use the key for seeded roles. Finally, a clean result from direct assignments alone can be misleading if your site uses UMX roles that grant responsibilities indirectly.

Frequently Asked Questions

Which table links users to responsibilities in Oracle EBS?

FND_USER_RESP_GROUPS_DIRECT holds direct assignments, with USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID and the start and end dates. Join it to FND_RESPONSIBILITY_VL for the name.

How do I list users who have SYSTEM_ADMINISTRATOR?

Join FND_USER to FND_USER_RESP_GROUPS_DIRECT and FND_RESPONSIBILITY, filter on RESPONSIBILITY_KEY equal to SYSTEM_ADMINISTRATOR and check that both end dates are null or in the future. Query 2 does this.

Does this show roles granted through User Management?

Not fully. Role-based grants appear in the workflow role assignment tables such as WF_USER_ROLE_ASSIGNMENTS. If your instance uses UMX roles, extend the audit with that source and map roles to responsibilities.

Can I run these queries on production?

They only read data and are light, but run them under your organization's change and access rules, ideally with a read-only account, and avoid peak batch windows on very large instances.

Netray can turn these checks into a standing, read-only access review that your auditors can query themselves, with the SQL always visible.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Oracle tools

See all

Back to All Tools