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:
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.
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
Security Matrix – Catalog File Link
Feel free to modify it as per your requirement and post your feedback in comments section.