squirrelworks
Manual account provisioning creates security risks, stale permissions, and offboarding delays. Here is how we engineered an autonomous, database-driven Joiner/Mover/Leaver (JML) synchronization engine using PowerShell, SQLite, and Active Directory on server APP1 to enforce role-based access control and export execution audit logs.
The synchronization script Invoke-HRIdentitySync.ps1 runs on host APP1, querying an authoritative HR database (hr_system.db) to evaluate employee record states against Active Directory (DC1).
| Lifecycle Trigger | HR Database State | Automated Active Directory Action |
|---|---|---|
| JOINER | status = 'Active' (New record) |
Provisions AD object in OU=Employees,OU=SW, sets UPN/Title/Dept, and assigns default VPN/Dept groups. |
| MOVER | status = 'Transferred' |
Updates Title and Department attributes via Set-ADUser, revokes stale department groups, and assigns target role groups. |
| LEAVER | status = 'Terminated' |
Strips non-primary security groups, disables account, and relocates object to OU=Disabled_Users,OU=SW. |
OU structure on DC1 isolating active accounts in OU=Employees and offboarded accounts in OU=Disabled_Users.
Updated Organization tab for Elena Rostova (erostova) confirming automated Title and Department property synchronization.
Instead of managing individual user access manually, group memberships are calculated dynamically during every execution loop using hashtable mapping structures.
Maps HR database department strings directly to Active Directory global security groups:
"Information Technology" => "SG_IT_Dept"
"Cybersecurity" => "SG_Cybersecurity_Dept"
Evaluates wildcard job title matches to grant specialized operational roles:
If job_title -like "*SOC Analyst*", user is added to SG_SOC_Analysts.
Below is the core execution logic used within Invoke-HRIdentitySync.ps1 to query SQLite, drive AD object state transitions, strip memberships upon termination, and log events.
# Hash Table for Department Mapping $deptGroupMap = @{ "Information Technology" = "SG_IT_Dept"; "Cybersecurity" = "SG_Cybersecurity_Dept" } # Query HR Database Employees $employees = Invoke-SqliteQuery -Database $dbPath -Query "SELECT * FROM employees" foreach ($emp in $employees) { $upn = $emp.user_principal_name $samName = $upn.Split('@')[0] $adUser = Get-ADUser -Filter "UserPrincipalName -eq '$upn'" -ErrorAction SilentlyContinue # LEAVER STATE: Strip access, disable, move to disabled OU if ($emp.status -eq 'Terminated' -and $adUser) { $userGroups = Get-ADPrincipalGroupMembership -Identity $adUser -Server DC1 | Where-Object { $_.Name -ne 'Domain Users' } if ($userGroups) { Remove-ADPrincipalGroupMembership -Identity $adUser -MemberOf $userGroups -Confirm:$false -Server DC1 } Disable-ADAccount -Identity $adUser -Server DC1 Move-ADObject -Identity $adUser.DistinguishedName -TargetPath $disabledOU -Server DC1 } }
Live console verification showing dynamic role grants: mvance granted SG_IT_Dept, SG_VPN_Users; erostova granted SG_Cybersecurity_Dept, SG_SOC_Analysts.
Every execution step writes a structured audit record into the SQLite database table jml_sync_log. At the end of each run, the engine automatically compiles recent events into a clean HTML dashboard for operational monitoring.
Maintaining decoupled audit tables (jml_sync_log) ensures complete traceability across all identity modifications, giving SOC analysts and auditors immediate visibility into automated privileged access changes.
Explore Related Identity & Access Management Modules: