Security Matrix – Users and their Roles
Security Matrix – Users and their Roles

 1,428 total views

For every implementation, there would be functional consultant who looks into the security and their job is to create a security matrix with all users list and their roles mapped on to a sheet. It would be tough to create this sheet manually when there are 1000’s of employees and it doesn’t make any sense to do it manually.

In order to overcome this challenge, we can create a BI Report and use the direct database option of OTBI to create pivot table to get the users/roles in matrix format.

SQL Query:
SELECT  
PU.USERNAME,
PRD.ROLE_NAME,
‘X’
FROM 
PER_USER_ROLES PUR,
PER_USERS PU,
PER_ROLES_DN_TL PRD
WHERE  PU.ACTIVE_FLAG = ‘Y’
AND SYSDATE BETWEEN PU.START_DATE AND NVL(PU.END_DATE,’4712/12/31′)
AND PU.USER_ID = PUR.USER_ID
AND PRD.ROLE_ID = PUR.ROLE_ID
AND PRD.LANGUAGE = ‘US’
ORDER BY PU.USERNAME DESC

Sample Output:

Catalog File: Security_Matrix_-_User_Roles.catalog

Import steps:
a) Login to the instance/Pod.
b) Navigate to Reports and Analytics
c) Click on Browse Catalog and navigate to the desired folder where you want this analytics to be imported.. Ex: My Folders or Custom Folder.
d) Click on the Unarchive button in the left bottom and select the catalog file that you downloaded from this post.
e) The analytics is imported with name “Security Matrix – User Roles” and is ready to use.
f) Click the Open hyperlink to run the analytics. The o/p can be exported using the export option at the end of the o/p (scroll till the end to see this option).

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 6 Comments

  1. sai krishna

    I have read many of your articles and these are very useful.Thanks for sharing your knowledge.All our posts helped me a lot.

  2. Usama LaDLa

    Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

  3. Remi

    This is interesting. Could you please share how to create privileges to role matrix report with similar format. Thanks

  4. Phani

    I tired of performing the same mentioned above but getting the below error.

    Error
    View Display Error

    Error generating view. Starting with 20A Update, Direct Database Query option in BI Answers is no longer available. You can no longer run Direct Database Query reports. Please create reports with OTBI subject areas or BI Publisher.
    Error Details
    Error Codes: OAMP2OPY

    1. Sricharan

      Direct database query functionality is deprecated. So we cannot use this catalog file anymore. However you can use the query and extract data into BI Report

  5. Karthik

    I need to get first name and last name for these user list and how to retrieve it

Leave a Reply