Skip navigation

RSA IGL Version: , v7.2x

Modules: Governance and Lifecycle

Product Area: Workflows

Time to apply: ~2 hours

 

Most organizations struggle to accurately manage and understand all their Application/Directory Accounts and which ones are actively being used. There is a huge risk associated to all accounts which are no longer in use but are still active and could be susceptible for attacks if compromised. Additionally, there could be unnecessary cost associated with licenses assigned to accounts that are no longer in use.

 

We see organizations introduce manual dormancy processes, but these can be inaccurate and require significant ongoing effort to be effective.

 

Typically, organizations want to introduce an automated and phased approach to managing dormant accounts. This process would first notify account owners and administration teams of any dormant accounts, enabling the user/team to take action if the account is still required. If no action is taken, the solution should then perform automated Account Disablement and possibly Account Deletion after a specified number of days of inactivity.

 

 

The following document, created by RSA Professional Services, provides steps on how to configure this solution within your environment.

 

NOTE:

Always first add configuration to a non-production environment and test thoroughly against real life data before promoting to further environments.

 

Always take a backup of the environment/s before making any significant changes.

 

RSA IGL Version: 7.1.x & 7.2.x

Modules: Governance/Lifecycle

Product Area: Workflows/Emails

Time to apply: <10 minutes (dependent on number of emails and SQL complexity)

 

Summary:

This guide uses the SQL Nodes within RSA IGL workflows to create HTML tables that can be used within the body of emails to improve how data is displayed.

 

Example of HTML Table within email:

This is a very simple solution that helps to improve end user experience.

 

For this solution to work, 'Sanitized HTML' must be allowed for Email body Templates, Review Emails and Workflow Emails. 

 

This is configured under Admin > System > Security

 

By default, all settings are set to 'No markup allowed' to prevent cross-site scripting attacks. Allowing Sanitized HTML is done so at your own risk and must be in line with your companies security standards. Further details on this can be found in the online help under 'Managing System Security'.

 

 

Background

Within RSA IGL email notifications are sent to specific end users containing details of items that require action, for example Approvals or Manual Fulfillment.

 

Although the out of the box email templates contain all the required details, it’s not displayed in the most user friendly format.

 

This solution will present the details in a user-friendly table format.

 

 

Challenge

Out of the box emails can sometimes contain a large number of items which can become confusing for the recipient. This can result in processing mistakes occurring or items being missed entirely.

 

Additionally, there’s no option to apply default sorting/ordering to the list in line with customer requirements. For example, order the items by the user name.

 

Solution 

As mentioned above, for this solution to work 'Sanitized HTML' must be allowed for email bodies.

 

Using SQL XMLELEMENT within SQL Select nodes, HTML tables can be created to display the data in a more user-friendly way and also allow for results to be ordered/sorted.

 

The HTML table is created as a Variable which is then referenced in the body of the email.

 

Implementation

Make sure you thoroughly test any changes in lower environments before promoting to Production.

The SQL Query used to extract the details required 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 used to create the HTML table.

If you’re adding this solution to an out of the box workflow, always create a copy and add to that. Do not update the original workflow as the changes may be overwritten when patching/upgrading.

The table is created using the following query which is added to a SQL Select node:

SELECT
    XMLELEMENT(
        "table",
        XMLATTRIBUTES(
            '1' AS "border" --, 'border-collapse: collapse;' AS "style"
        ), XMLELEMENT(
            "thead", XMLELEMENT(
                "tr", XMLFOREST(/* Add your column headers in format: '<ColumnHeader>' as "th" */ 'Column 1' AS "th", 'Column 2' AS "th", 'Column 3' AS "th", 'Column 4' AS "th")
            )
        ), XMLELEMENT(
            "tbody", XMLAGG(XMLELEMENT(
                "tr", XMLFOREST(/* Add your selected columns here in format: '<ColumnName>' as "td" */ COLUMN1 AS "td", COLUMN2 AS "td", COLUMN3 AS "td", COLUMN4 AS "td")
            ))
        )
    ) act_table
FROM
    (
         -- Insert SQL statement here

    )

 

As highlighted by the comments in the above, it’s then simply a case of adding the query needed to return the required results under the ‘Insert SQL statement here’ section.

 

The ‘Add your selected columns here’ section is used to determine which columns from the query you wish to display within the table.

 

The ‘Add your column headers’ section is then used to display a friendly name as the column header of the table. For example, ‘Column 1’ instead of ‘COLUMN1’.

 

The table is created with the variable act_table as highlighted in Yellow. Set the Variable Type on the SQL Select Node to ‘Job’ and Save the workflow.

 

Add a Send Email node to the workflow and join the nodes together with a transition. Within the body of the email, add the ‘act_table’ variable:

Depending on the configuration of the workflow, you may also need to disable emails being sent from the approval/activity node otherwise this could result in duplication.

Save the workflow.

 

That’s it!

 

Example

The below image shows how the data is currently displayed using the out of the box 'Task Assigned' email notification.

Using the below query will return the data in a table format making it much more user friendly. Additionally, results are sorted/ordered as part of the SQL. For example, this table shows the users full name in ascending order (ORDER BY pUSR.FIRST_NAME||' '||pUSR.LAST_NAME ASC)

SELECT
    XMLELEMENT(
        "table",
        XMLATTRIBUTES(
            '1' AS "border" --, 'border-collapse: collapse;' AS "style"
        ), XMLELEMENT(
            "thead", XMLELEMENT(
                "tr", XMLFOREST(/* Add your column headers in format: '<ColumnHeader>' as "th" */ 'Action' AS "th", 'User Full' AS "th", 'Application' AS "th", 'App-Role Name' AS "th")
            )
        ), XMLELEMENT(
            "tbody", XMLAGG(XMLELEMENT(
                "tr", XMLFOREST(/* Add your selected columns here in format: '<ColumnName>' as "td" */ ACTION AS "td", USRNAME AS "td", APPNME AS "td", APRNAME AS "td")
            ))
        )
    ) act_table
FROM
    (
         -- Insert SQL statement here
SELECT
pCRD.TYPE AS ACTION,
pUSR.FIRST_NAME||' '||pUSR.LAST_NAME as USRNAME,
pBSC.NAME as APPNME,
pCRD.VALUE_NAME as APRNAME
FROM PV_CHANGE_REQUEST_DETAIL pCRD
LEFT JOIN PV_USERS pUSR
ON pCRD.AFFECTED_USER_ID = pUSR.ID
LEFT JOIN PV_BUSINESS_SOURCE pBSC
ON pCRD.VALUE_BUSINESS_SOURCE_ID = pBSC.ID
WHERE pCRD.CHANGE_REQUEST_ID = '${access_request_requestID}'
AND TYPE = 'AddUserToAppRole'
ORDER BY pUSR.FIRST_NAME||' '||pUSR.LAST_NAME ASC
    )

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);


 

 

 

Majority, if not all customers of RSA IGL use Active Directory (AD) as a way of managing who can access certain applications and what actions they can perform within the application.

 

Early phases of an RSA IGL project focus on visibility, and this typically includes the collection of all accounts, groups and group memberships from a primary AD domain. Although this data provides great insight in to the AD environment, it doesn’t quickly and clearly identify which AD managed applications users have access to.

 

This normally results in customers requesting a solution for on-boardng AD managed applications in to RSA IGL that must be:

  • Easy to implement
  • Uses out of the box functionality
  • Easy and repeatable to on-board applications
  • Does not duplicate data
  • Works with all areas of IGL
    • Visibility of access
    • Reviews
    • Access request
    • AFX

 

The following document, created by RSA Professional Services, provides details on the out of the box components used to separate the AD managed applications so that they are displayed as individual applications, instead of AD groups within a directory. Once separated, these applications are clearly displayed against the user, within User Access Reviews and also Access Request where changes can be automatically fulfilled re-using existing connectors.

 

Active Directory (AD) Managed Applications  

 

Although this solution covers the use of Access Request and AFX, it can be still be used without these to enhance visibility around the users access and during user access reviews.

 

RSA IGL Version: V7.1x & V7.2x

Modules: Governance 

Product Area: Account Reviews

Note: A summary of all RSA IGL recipes can be found here: (TBC)

Time to apply: ~2 hours

 

Summary:

Within Account Access and Ownership Reviews, the Maintain and Revoke buttons are mandatory and cannot be hidden or removed. When a reviewer selects Revoke against the account, this will result in a change request being generated to delete the account.

 

It is not always possible to delete accounts and it has been asked by multiple customers how the Revoke button behaviour can be changed to perform disablement of the account or even disablement and removal of access.

 

The attached document, created by RSA Professional Services, provides details on the out of the box components used and steps followed to create an Approval Workflow to:

  • Reject the automatically created Delete Account change request
  • Create a new change request to disable the account
  • Create a new change request to remove access (if required)

Services 101 blogs, help to explain various areas of RSA Identity Governance and Lifecycle, to ensure you are getting the most out of the product and following recommended practices. We hope to show you lots of great features, tips and tricks that you may not have been aware of!

Please reply below with any questions or hit like if this is helpful!

We are starting by looking at workflow nodes and in this blog, specifically the "Create Admin Error" node. 

Click the images to enlarge if you need!

Product Area: Workflow's

Note: A summary of all workflows is found here: RSA IGL Services 101: Explaining Workflow Nodes - Summary

Workflow Node: Create Admin Error

Time to apply: <10 minutes

Impact: High positive impact for administrative users and support, Low risk to workflow process and performance as this node has a very low data footprint.

 

Summary: "Create Admin Error" nodes provide a great way to help log success or failures from within workflows. These results can be captured in a clear and meaningful format and are then available within the RSA IGL UI (Admin > Admin Errors > Summary) and via reports/charts.

 

Capturing Admin Errors helps to highlight processing issues that require attention to administrative users without them having to drill down in to workflows. 

 

RSA Field Example: To put this in to a real-life scenario, it’s a common requirement that Active Directory accounts have the password reset and are moved to a different OU when the associated user is flagged as a leaver.

 

This would be achieved using Provisioning Command nodes (1) within the fulfillment workflow. These Provisioning Command nodes then call the relevant AFX capability on the Business Source to perform the action and provide a status of 0 if successful or -1 if unsuccessful (2). Further details on the values and how they can be referenced as variables can be found here - https://community.rsa.com/docs/DOC-64651

 

A Decision node (3) then separates the successful from the unsuccessful allowing the Create Admin Error nodes (4) to return different errors depending on the result.

 

The Create Admin Error is then configured to capture the relevant details in a clear format that is easily understood by administrators.

 

Below is a format we typically use when configuring Admin Errors for the following reasons:

  • Highlighting the status (WARNING) at the start of the message helps focus attention

  • Including the associated process (LEAVER) helps sorting/filtering/reporting and can form part of daily checks

  • Including dynamic variables from the workflow makes the message more meaningful and easier to track

  • Inclusion of the Change Request ID enables easily linking to other tables/views for extended reporting options

  • Pipe separator simplifies report query

 

 

The Admin Errors are then visible within the UI from the Admin > Admin Errors screen

And can be easily extracted in to a report format which can be emailed on a scheduled basis or added to a dashboard to form part of the daily checks.

As mentioned, the inclusion of the CR ID in the Admin Errors provides a logical join to the CR views which provide useful additional detail such as user details, dates, times, days late, etc.

 

Within the above report, the join was achieved using the following query:

 

FROM avuser.PV_CHANGE_REQUEST_DETAIL pCRD
LEFT JOIN  avuser.V_AVR_ADMIN_EXCEPTIONS vAAE
ON PCRD.CHANGE_REQUEST_ID = TRIM(REGEXP_SUBSTR(VAAE.Description, '[^|]+', 1, 5))

 

The REGEXP_SUBSTR function uses the pipe separator (|) as a way of determining the string to return. Once joined to the PV_CHANGE_REQUEST_DETAIL view, this can be easily extended, for example:

 

LEFT JOIN avuser.PV_CHANGE_REQUEST pCR
ON pCRD.CHANGE_REQUEST_ID = pCR.ID

 

LEFT JOIN avuser.PV_USERS pUSR
ON pCRD.Affected_User_ID = pUSR.ID

 

Usage: All workflows that contain provisioning activities and provide status response (Provisioning Command node, Web Service) should include error handling, where possible.

 

General Notes/Benefits:

  • Reduce troubleshooting effort (no need to dig around in workflow)

  • Help create audit trail

  • Quickly and clearly highlight issues that require attention

  • Easy to configure

  • Very low data footprint so won’t impact performance

  • Ability to include variables in error messages provides huge flexibility

  • Populated to V_AVR_ADMIN_EXCEPTIONS view

  • Ease of reporting/dashboards

  • Admin Errors are not included in data purging although can be manually deleted from the UI if required

     

     

 

Configuration:

  • We are using v7.1 P04 in the example below, however most versions of the older product also have milestones available. 
  • Create Admin Error nodes are found under the "Modeler Toolbox", about halfway down, as shown in the image below. To add a new node to your workflow, just single left-click the required node icon from the left-side panel then double left-click anywhere in the middle area to add that node.

Create Admin Error nodes are made up of 3 sections:

Type

Available to select from a drop-down list and can be used as grouping/sorting criteria on the Admin Errors page.

 

Priority

Drop-down select of either Low, Medium or High

 

Error Description

Free text box

 

As mentioned above, within the Error Description box you can also use variables from within the workflow. The use of dynamic variables makes the message more meaningful and also provides greater flexibility when it comes to reporting.

 

RSA PS Recommendation

Unless absolutely necessary, RSA recommends to only create Admin Errors for the failed/un-successful changes. This helps keep the Admin Error page lean and focus attention on only those items that require action/remediation.

RSA IGL Services 101 blogs, help to explain various areas of RSA Identity Governance and Lifecycle, to ensure you are getting the most out of the product and following recommended practices. We hope to show you lots of great features, tips and tricks that you may not have been aware of!

 

Please reply below with any questions or hit like if this is helpful!

 

Product Area: Reports/Charts/Table's

Data: App-Roles

Summary: Application roles collected within "directories" are not located in the PV_APPLICATION_ROLE view but are instead found under PV_DIRECTORY_ROLE view. If you use directories and collect in APP-ROLES, you must take this into account for all your reports/charts that you create, so that you dont miss out any information. 

RSA Field Example: If creating a report/chart to display all app_roles within RSA IGL which have a "privileged" flag set to "yes", you will need to take into account both these tables in the SQL.

SQL Example:

select 
    application_id,
    name
from avuser.pv_application_role
where lower(privileged) = 'yes'
union all
select
    application_id,
    name
from avuser.PV_DIRECTORY_ROLE
where lower(privileged) = 'yes'

 

These images show where the data is found.

 

Within the Directory "Navision - SQL Database" we can see the "app role" called "db_access_admin"

When searching against PV_APPLICATION_ROLE table - the result is not found

When searching against PV_DIRECTORY_ROLE table - the result is found

 

Thought I'd share this to save others time if they weren't already aware.

 

Cheers,

Clive