IdStory Identity Manager

Tested on 9.7.11 and 10.6.4.

Organization structure:
Parent - this has automatic role "autoByStructure" with recursion Down
├── Node1
└── Node2

First situation - wrong validity of automatic roles after changing work position and contract validity:

• The user has the contract valid till 31.1.2021 with the work position Node1. The automatic role is assigned with validity 31.1.2021
  
• Change the work position to Node2 and at the same time, change its validity
• The validity of the automatic role wasn't changed!
  
• On 1.2.2021, the automatic role would be removed by IdentityRoleExpirationTaskExecutor, even if the contract would be still valid!

Second situation - removed automatic roles when contract was extended on the last day - this can happen during synchronization of contracts/slices:

• Contract is valid till 31.1.2021
• The contract is extended in HR on the last day
• Synchronization of contracts/slices processes the contract on 1.2.2021.
• The validity of the contract is extended, but the validity of automatic roles by organizations is not changed => when IdentityRoleExpirationTaskExecutor runs that day, it removes the roles

---

How to find identities that have automatic roles with wrong validity:

SELECT i.username, i.external_code, itn.name organization, ic.valid_till as contractValidity, r.name rolename, ir.valid_till as roleValidity FROM idm_identity i JOIN idm_identity_contract ic ON ic.identity_id=i.id LEFT JOIN idm_tree_node itn on ic.work_position_id=itn.id JOIN  idm_identity_role ir ON ir.identity_contract_id=ic.id JOIN idm_role r on ir.role_id=r.id WHERE ir.role_id is not null and ir.automatic_role_id is not null and ((ir.valid_till is not null and ic.valid_till is null) or (ir.valid_till is null and ic.valid_till is not null) or (ir.valid_till <> ic.valid_till)) ;

How to find identities that don't have some automatic role. This is more problematic because of the recursion, but the following SQL can be used e.g. if you know that all identities with external code should have the role "autoByStructure":

SELECT i.username, i.external_code, itn.name organization, ic.valid_till as contractValidity, ir.role_id, ir.valid_till as roleValidity FROM idm_identity i JOIN idm_identity_contract ic ON ic.identity_id=i.id LEFT JOIN idm_tree_node itn on ic.work_position_id=itn.id LEFT JOIN  idm_identity_role ir ON ir.identity_contract_id=ic.id AND ir.role_id in (select id from idm_role where name='autoByStructure') WHERE  ( ic.valid_till  is null or ic.valid_till > '2021-11-01') AND ir.role_id is null and i.external_code is not null;