SecurID® Governance & Lifecycle Recipes
RSA IGL Version: V7.1x & V7.2x
Modules: Governance
Product Area: Reviews, Reports
Note: A summary of all RSA IGL recipes can be found here: (TBC)
Time to apply: ~30 minutes
Summary:
This recipe uses the Reporting module within RSA IGL to create create Coverage Files in the correct format which can be then be uploaded directly to Review Definitions/Results.
The benefit of this approach is that it removes the manual effort normally associated with Coverage Files and the likelihood of user error.
Background
Within RSA IGL you can specify reviewers using the review definition wizard, for example ‘Supervisors review their subordinates’ or ‘Asset Owners review their assets’.
In scenarios where these options do not meet your requirements, you can, as an alternative use a Coverage File to specify who reviews what. Coverage Files provide greater flexibility and allow for granular filtering to be applied, for example, User A is responsible for reviewing application roles with the name ‘Admin’ for all users within the Department ‘Finance’.
Challenge
The immediate concern around the use of Coverage Files is the manual effort to create these and the ongoing maintenance required to ensure they contain the correct details.
Additionally, there’s also the added risk of user error associated with manual creation and management.
Suggested Solution
The Reports module within RSA IGL can be used to generate coverage files in the correct format, ready for uploading directly to the Review Definition. Using Reports allows for the internal RSA IGL database views to be queried each time, meaning the coverage file will include the correct details - assuming the environment has been kept up to date!
As an example, the following covers the scenario of Application Roles classified as ‘Privileged’ being assigned to the associated Application Business Owner within a User Access Review.
The classification against the Application Role is set using a Managed/Editable Custom Attribute (CAS6) and a Custom Value List.
The Application Business Owner is set using the out of the box attribute.
Using a database query tool such as SQL Developer or SQuirrel SQL to create the query, the following returns the name of the Application Role marked as ‘Privileged’, the User ID of the associated Application Business Owner and the ID of the Application.
The required entries and pipe separators are added so that the results are in the required Coverage File format ready for upload to RSA IGL.
SELECT -- APPROLE PRIVILEGED TO BUSINESS OWNER
'user_id='||''''||pUSR.USER_ID||'''|user'||'|'||'1=1'||''||'|'||'app-role|name='||''''||vAUE.NAME||''''||' AND APPLICATION_ID ='||''''||pAPP.ID||'''' AS COVERAGE_REVIEWER
FROM avuser.V_ALL_UNIFIED_ENTITLEMENTS vAUE
JOIN avuser.PV_APPLICATION pAPP
ON vAUE.APPLICATION_ID = pAPP.ID
JOIN avuser.PV_USERS pUSR
ON pAPP.BUSINESS_OWNER = pUSR.ID
WHERE vAUE.ENT_TYPES = 'app-role'
AND LOWER(APPROLE_CAS6) = 'privileged'
Result:
As mentioned previously, using Reports means that the Coverage File can be generated ahead of a Review cycle and will provide consistent results in sync with the details contained within RSA IGL. For example, if the Standard User Application Role was marked as Privileged, this would then also be captured in the Coverage File once the report has been run and exported to CSV:
Note: Ahead of the Review Cycle, the Report must be re-run, exported (without headers), saved and manually re-uploaded to the Review Definition/Result.
Implementation
Coverage Files are covered in detail within the online product help under the section ‘Coverage Files Overview’. Here you will find details around the require structure of Coverage Files for different review types and actors (reviewer, monitor) and also a number of examples.
The online product help should first be read and understood before trying to create Coverage Files using Reports.
Note: As always, apply configuration to the non-production environments first and only promote to Production once the solution has been fully test. Always take a backup of the environment before making changes.
The SQL Query used to extract the details required within the Coverage File should be written using a database query tool such as SQL Developer or SQuirrel SQL and referencing the RSA IGL Public Database Schema Reference document found under the Documentation menu of the Community.
Once happy with the query, this can be created as a Tabular Report within RSA IGL.
Other than a meaningful name and description, no further changes are required on the General tab.
Add the query under the Query tab.
Note: As with all reports created within RSA IGL, the query must be surrounded by parenthesis.
Save the report.
Run the Report and select Export and select .csv data only.
Note: The file will fail on upload if the headers are also included.
If you were to try and upload this file, it will fail with the following error:
The reason for this can be seen when opening the file in Notepad++, you’ll notice that the results are surrounded by double quotes (“):
Instead, open the file in Excel and and select Save As.
Ensure the file is saved as CSV (Comma delimited) and not CSV UTF-8
Navigate to the required Review Definition or Review Result and upload the Coverage File:
Once uploaded, you can click View to validate the contents are correct:
Run the review to validate that the correct reviewers have been assigned to the correct items:
Note: Always thoroughly check and validate the results before setting the state to Active.
Examples
The below covers reviewer requirements that have previously requested via the Community.
Example 1
Requirement: Entire application to be reviewed by an Application Administrator (not Business or Technical Owner)
Pre-Requisites: Requires a Custom User Attribute to be created at a Business Source level. This attribute must be Managed and Editable. Attribute used in this example is CAU3.
Query used:
(SELECT -- APP ADMINSTRATOR TO APP
'user_id='||''''||pUSR.USER_ID||'''|user'||'|'||'1=1'||''||'|'||'application|name='||''''||pAPP.NAME||'''' AS COVERAGE_REVIEWER
FROM avuser.PV_APPLICATION pAPP
JOIN avuser.PV_USERS pUSR
ON pAPP.CAU3 = pUSR.ID
WHERE pAPP.CAU3 IS NOT NULL);
Example 2
Requirement: Application Roles must be reviewed by specific Owner.
Pre-Requisites: Requires a Custom User Attribute to be created at an Application Role level. This attribute must be Managed and Editable. Attribute used in this example is CAU1.
Query Used:
(SELECT -- APPROLE TO OWNER
'user_id='||''''||pUSR.USER_ID||'''|user'||'|'||'1=1'||''||'|'||'app-role|name='||''''||vAUE.NAME||''''||' AND APPLICATION_ID ='||''''||pAPP.ID||'''' AS COVERAGE_REVIEWER
FROM avuser.V_ALL_UNIFIED_ENTITLEMENTS vAUE
JOIN avuser.PV_APPLICATION pAPP
ON vAUE.APPLICATION_ID = pAPP.ID
JOIN avuser.PV_USERS pUSR
ON vAUE.APPROLE_CAU1 = pUSR.ID
WHERE vAUE.ENT_TYPES = 'app-role'
AND vAUE.APPROLE_CAU1 IS NOT NULL);
