Access Control - Leavers with Disabled Accounts

Description

The percentage of people who have left the organisation whose identity account has been disabled, ensuring former staff cannot keep accessing company systems and data after their employment ends.

How we measure it

Find everyone whose employment end date in Salesforce (Krow project resources) has passed, excluding people who were rehired and have a current record. Leavers are considered compliant if their Okta account has been suspended, deprovisioned or removed.

Meta Data

Attribute	Value
Metric id	ac_leavers_disabled
Category	Access Control
SLO	98.00% - 100.00%
Weight	0.8
Type

References

Framework	Ref	Domain	Control
ISO 27001:2022	A.5.18	5 Organizational controls	Access rights
CIS 8.1	6.2	Access Control Management	Establish an Access Revoking Process
NIST CSF v2.0	PR.AA-05	Identity Management, Authentication, and Access Control (PR.AA)	PR.AA-05: Access permissions, entitlements, and authorizations are defined in a policy, managed, enforced, and reviewed, and incorporate the principles of least privilege and separation of duties

Code

WITH employment AS (
  SELECT lower(user_email__c) AS email, CAST(employment_end_date__c AS DATE) AS end_date
  FROM {{ ref('salesforce_krow__project_resources__c') }}
  WHERE user_email__c IS NOT NULL
),
leavers AS (
  -- people whose every employment record has ended (rehires with a current record are excluded)
  SELECT email, max(end_date) AS end_date
  FROM employment
  GROUP BY email
  HAVING count(*) = count(end_date) AND max(end_date) < CURRENT_DATE
)
SELECT
  leavers.email AS resource,
  'user' AS resource_type,
  CASE WHEN users.id IS NULL OR users.status IN ('SUSPENDED', 'DEPROVISIONED') THEN 1 ELSE 0 END AS compliance,
  'Left ' || CAST(leavers.end_date AS VARCHAR) || '; Okta account ' ||
    CASE WHEN users.id IS NULL THEN 'removed' ELSE lower(users.status) END AS detail
FROM leavers
LEFT JOIN {{ ref('okta_users') }} AS users
  ON lower(users.profile_login) = leavers.email