• Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:September 30, 2020
  • Reading time:5 mins read
You are currently viewing Security Matrix with User and Role Mappings
Security Matrix with User and Role Mappings

In this article we will look into one of the most useful report for every implementation which is the Security Matrix Report listing all Users along with their corresponding Roles.

This report will be useful for the Security Specialists/IT team to validate roles attached to users and check any users with additional access which they shouldn’t be having at all.

Earlier, I have developed SQL Query to get this info and used the direct database Query feature to get the output in matrix form and the catalog has been posted on Report Sharing Center on Cloud Customer Connect. Here is the link – https://cloudcustomerconnect.oracle.com/posts/7cf9cd986f. However the Direct Database Query feature was disabled by oracle and from then the catalog won’t generate the output.

Oracle hasn’t provided any seeded solution to get this report. Instead Oracle has provided “User and Role Access Audit Report” which can be run from Scheduled Processes and it provides data by role and we can’t get the matrix of users and roles.

I have also tried creating the Security Matrix report using the subject area “Security – Roles and Privileges Real Time” and wasn’t successful in my attempt as it was pulling all roles and duties when it should be fetching only the roles that can be assigned to users.

The final approach that I tried is to get the users/roles data in BI Report using SQL Query and then create an excel template to apply pivot and show the output in the matrix form and it was successful.

Here is how the output will look like:

image 36 1024x351 - Security Matrix with User and Role Mappings
Security Matrix

It also displays the Instance name and the date on which the report was ran. It lists all the users in that instance along with their roles.

  • 1 indicates, that role is present for the corresponding user.
  • Blank indicates that role is not present for the corresponding user.

It will also show the grand totals of the total number or users for each individual role and total number of roles for an individual user at the right end and bottom left of the sheet.

The pivot on the template will auto-refresh on loading. You will have to enable editing if prompted for. And you will have to save that excel sheet if you want to save the pivoted sheet.

image 37 1024x306 - Security Matrix with User and Role Mappings
Grand Totals

Table of Contents

SQL Query used for this report:

SELECT  
PU.USERNAME,
PRD.ROLE_NAME,
1 as 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, PRD.ROLE_NAME

Catalog File:

Security Matrix – Catalog File Link

Feel free to modify it as per your requirement and post your feedback in comments section.

  • I am facing below error.

    oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: java.lang.NullPointerException

    the catalogue gives me an error.

    I also search on Oracle Support.

    As per solution:- “BIP is too large”

  • Hi KP,
    I was able to import it without any issues. Try importing in any other POD or copy the SQL and run it to check the results in tabular form.
    Thanks,
    Sricharan

  • In visible box by plugintheme