The Contractor Role Sprawl
Your instance has 3,200 active users and 75 roles. An audit found 900 users with at least one role assigned directly on their user record, and 18 users with the admin role although the security policy allows four. Contractors rotate every 90 days; offboarding is a manual checklist, and six contractor accounts were still active 30 days after their end date last quarter. A new requirement: 25 finance users need to read vendor payment records (u_vendor_payment) and be able to write only the u_dispute_notes field. The finance manager asks you to 'just give them the existing payment_admin role' because it already works. You need a least-privilege design that survives the next audit.
1. Why is direct role assignment a problem here, and how should the model change?
Read the worked answer
Direct role assignment makes onboarding, offboarding, and audit per-user work. With 900 users carrying direct roles, every change multiplies the chance of stale access, and offboarding depends on someone remembering every role. Move to groups: create groups by job function, assign roles to groups, and add users to groups.
Use role containment (a parent role contains child roles) only when the roles are truly always co-assigned; otherwise it recreates the same over-grant. For the 18 admins, recertify: identify which four are required, remove admin from the rest, and check whether admin is direct or inherited through a group. Removing a role from a group is not enough if the user also has it directly; check both direct and inherited assignments in sys_user_has_role.
2. Design the finance requirement without granting payment_admin.
Read the worked answer
Do not grant payment_admin. Create a group such as Vendor Payment Finance and a role such as u_vendor_payment_finance with read on u_vendor_payment. Put the field write in an ACL: write on u_vendor_payment.u_dispute_notes requires that role; the table write ACL can remain restrictive. Add the 25 users to the group.
This gives read plus one field write and nothing else. If they also need to create disputes, add a separate create role/ACL, not blanket write. Test with impersonation: read should work, write to other fields should fail, write to u_dispute_notes should succeed.
3. What lifecycle controls would you put around contractors?
Read the worked answer
Create contractor groups (for example, per agency or cohort) and put role assignment on the group. On offboarding, remove group membership and deactivate the user. Deactivation blocks login but does not remove roles, so also remove group memberships.
Run a weekly scheduled report or flow: active contractors whose end date has passed, users with admin, and users with direct roles. Use the 'Users with role' and group membership reports for attestation. Keep group names stable so role changes apply to all members. Group-inherited roles refresh on session/login, so verify after changes rather than assuming immediate effect.