The Contractor Whose Role Outlived the Project
A retail company uses Snowflake with federated SSO through an external IdP. Contractors are provisioned into an IdP group SNOW_CONTRACTORS, which maps to a custom role CONTRACTOR_ANALYTICS with SELECT on SALES.MARTS. The project ended three weeks ago. The IdP team removed the group, but 14 contractor user accounts remain active in Snowflake because they were created manually by an admin two years ago with MUST_CHANGE_PASSWORD already satisfied. A quarterly access review flagged the 14 accounts, but the security lead says the accounts are 'harmless' since the group is gone. Last week a contractor logged in successfully using a password that had never been rotated. The company's policy requires quarterly access recertification and immediate revocation on project end.
1. Explain why the removal of the IdP group did not revoke the contractors' access, and identify the two independent control gaps this exposes.
Read the worked answer
Removing the IdP group only stops future authentication through that group's mapping; it does not retroactively delete Snowflake users or remove role grants that were already applied. The 14 users were created manually, so they are not lifecycle-managed by SCIM provisioning - they persist as local users with their own credentials. Two gaps: (1) Provisioning gap - manual user creation bypassed the automated deprovisioning path, so project-end revocation never fired. (2) Grant hygiene gap - the CONTRACTOR_ANALYTICS role grant to those users was never removed, so even if the accounts had been disabled via IdP, any non-SSO login path (password) would still work. The 'harmless' claim is wrong: access is defined by effective grants plus a working authentication path, and both survived.
2. The security lead proposes simply disabling the 14 accounts. Evaluate whether that fully satisfies the policy and describe what else must be verified before closing the finding.
Read the worked answer
Disabling the accounts is necessary but not sufficient. Policy requires immediate revocation on project end, and disabling achieves that outcome only if no other authentication path exists. Before closing: (1) Confirm no key-pair or PAT credentials remain active for those users - disabling a user does not always invalidate every credential type, so check and rotate/remove keys. (2) Verify the CONTRACTOR_ANALYTICS role has no other members, and if the role itself is no longer needed, drop it or remove its grants so a future misgrant cannot silently restore access. (3) Check session history for the 14 users since project end - the successful login last week means at least one session occurred; scope what it accessed. (4) Add a detective control: a recurring query against account usage for users not present in the IdP, so manual accounts are caught before the next quarterly review. Disabling alone closes the symptom, not the lifecycle defect.