Looking for best practice to track user to application access and auto create termination tasks

[Andrew Meza]

Hi all. I am looking for advice on the right way to model and automate user access deprovisioning in ServiceNow.

A business group wants to build a custom table with one row per user and about 30 plus application columns. Each cell would be Yes or No to indicate access. On user termination they want the table to automatically create one task per application where the value is Yes so for example 15 tasks if the user has 15 of the 30 applications and then flip those Yes values to No when the tasks complete.

Before we build this I would love input on whether this design is practical or if there is a better pattern using out of the box capabilities or common designs. Specific questions

1 What is the recommended data model for user to application access
My instinct says a normalized many to many model rather than a wide table with one column per application. For example a User to Application Access table with fields like User reference to sys_user Application reference to cmdb ci appl Entitlement optional Status Active Pending Removal Removed Source Requested by Catalog HR Feed IAM Last validated date Owner Group and Audit fields. That would let us create one record per access instead of many columns.

2 How would you trigger deprovisioning tasks on termination
I am thinking HRSD Lifecycle Events or an HR Profile state change could trigger Flow Designer to create one task per active access record for the user and route it to the application owner or a group. Completion or an integration callback could set the access record to Removed.

3 Are there out of the box features or plugins I should leverage first
Things like HRSD Lifecycle Events Access Management via Catalog items and Entitlements Application Portfolio with ownership groups IntegrationHub for revocation automations or Identity related products like Identity Governance Identity Lifecycle or connectors to Azure AD Okta.

4 Reporting and audit
With a normalized table it is easy to answer questions like Who has access to App X or What was removed for User Y on Date Z. Any tips for Performance Analytics or compliance reporting patterns you use

5 Pitfalls to avoid
Any common mistakes around modeling entitlements roles and nested group membership Application ownership and routing or keeping data in sync with the source of truth for access such as AD Okta or an IGA tool

Context for why I am concerned about the wide Yes No table approach
Maintenance overhead columns will constantly change as applications are added or renamed
Data integrity Yes No flags do not tell us entitlement scope role or source of truth
Automation complexity harder to reuse across more than 30 applications or to scale beyond Yes No
Auditability difficult to reconstruct who changed what and when at a granular level
Reporting limits harder to slice and dice by application owner entitlement or source

If you were implementing this from scratch how would you design the tables and flows Would you use HRSD Offboarding Lifecycle Events Catalog driven access with Remove Access items or Identity Governance Also if you went the normalized route what fields and relationships proved most useful

Thanks in advance for any patterns references or gotchas

[Sandeep Rajput]

@Andrew Meza I recommend to proceed with the normalised table approach instead of maintaining separate columns for each applications. Your applications may keep on changing, some new applications may get added/ others may get removed/deprecated, updating columns based on these changes will be a nightmare to handle in future. 

As mentioned by you, I recommend creating a mapping table where a reference to sys_user table and cmdb_ci_appl table along with status, source and audited fields can be kept. This will be a scaleable data model with ease of auditing/tracking the records later.

If you are already using an HRSD offboarding lifecycle event, the deprovisioning activity can be a part of this lifecycle event where a flow can trigger to generate deprovisioning tasks either for the application owners or automatically revoke the access if the approvals are not required as the user is already being offboarded from the system.