A role hierarchy that survives a reorg

The natural first move is one role per person-shaped thing: analyst, then analyst_eu, then analyst_eu_finance, each granted directly on tables. It works for about a quarter. Then somebody joins who is almost an EU analyst but also needs revenue, and rather than reopen the model, a sixth role appears that is a copy of the fifth plus one grant.

What follows is mechanical. Every new table means visiting every role. Every job change means auditing which of thirty near-identical roles the person should now hold. Nobody deletes anything, because nobody can prove a role is unused. Within a year the role list is longer than the user list, and the reviewer, whose entire job is reading that list, has no chance.

• Access role - a role that holds privileges on objects and is granted to nothing but other roles. Named for the data it opens.
• Functional role - a role that represents a job. It holds no direct object privileges at all; it holds access roles. Named for the person doing the job.
• Role grant - grant role a to role b. The privileges of a become available to b, which is the upward direction from session 1.
• Re-pointing - changing which access roles a functional role holds. The single operation a reorg should require.
• PUBLIC - the role every user holds automatically. Anything granted here is granted to the entire future population of the account.
• Orphan role - a role with no members and no obvious purpose. Common, and the reason a safe deletion procedure is worth having.

One access role per meaningful bundle of privileges on objects. Meaningful means a bundle that at least two jobs might plausibly want, or one job wants and the design treats as a unit. For Saltbox the design produces three.

• ac_customers_read - the non-sensitive projection: customer_id, region, plan. Everybody who touches the table needs this and nothing about it is contentious.
• ac_customers_pii_read - name, email, phone and support_notes. One job needs it in clear. Every other job's relationship to this bundle is an argument that should happen once, here, rather than per table.
• ac_customers_finance_read - mrr. Not personal data, still restricted, and separated because the set of jobs that needs revenue is not the set that needs contact details.

Two properties make these roles do their job. They are granted to nothing but other roles, never to a user, so there is no path by which a person accumulates object privileges outside the model. And they are named for what they open, so the name survives the reorg that renames the team.

One functional role per job in the written design, and the design already named five. A functional role holds access roles and holds no direct privileges on any object, which is the constraint that keeps the two layers from silently becoming one.

Notice that three functional roles hold an identical pair of access roles and are still three roles. They differ on the row dimension, which session 3 handles, and on the masking of customer_id for the scientist, which session 4 handles. Collapsing them into one role now would make both of those sessions impossible to express, and it is exactly the collapse that produced the shared role the bench scores at 109 leaks.

Reorgs are not an edge case, they are the weather. The two-layer model earns its keep because each of the three common changes touches exactly one thing.

• A job changes. The EU analyst picks up revenue responsibility. You grant one access role to one functional role. No object is re-granted, no user is touched, and every other job is untouched by construction.
• A new table arrives. You create one access role for it and grant it to the functional roles that need it. In a single-layer model this is a visit to every job-shaped role, which is where the copies come from.
• A person moves. Revoke one functional role, grant another. There is nothing else to check, because a person has never held anything else.

The cost is one extra layer of indirection and roughly three more roles. The saving is that the number of grant operations per change stops scaling with the size of the estate, which is the property that actually decides whether a model is still true in two years.

Naming reads as cosmetic and is not, because a role list is the artifact a reviewer actually reads. They are not going to open every role and enumerate its grants; they are going to scan a list of names and stop on the ones that look wrong. That only works if a name carries its layer, its subject and its scope.

Three rules are enough. Prefix by layer, so ac_ and fr_ sort into two blocks and a functional role holding a direct grant becomes visible. Name access roles after the object and the class, never after the team that first asked. Name functional roles after the job, never after the person, because fr_marias_role is a role nobody will ever revoke.

Privileges inherit upward. Grant role A to role B and B holds A's privileges, not the other way round. The session 1 diagram covers the model; what matters here is the sentence you say to yourself before typing, because the statement reads naturally in the wrong direction.

grant role fr_support_agent to role fr_analyst_eu sounds like it gives support something. It gives the analyst everything support can see: name, email, phone and support_notes in clear, across both of support's regions. The analyst design asked for four non-sensitive columns. That one statement is the difference.

The structural defence is the two-layer rule. Functional roles are not granted to each other, ever. When a job genuinely needs what another job has, the answer is to grant it the same access role, which is one extra keystroke and leaves a reviewable trace in the right layer. A functional-to-functional grant in a show grants output is then unambiguously a mistake rather than something that has to be reasoned about.

Role lists only grow, and the reason is rational: dropping a role might break something invisible, and the reward for being right is nothing. So the orphans accumulate, and every one of them is a standing privilege waiting for somebody to be granted it by mistake.

The procedure below makes deletion boring enough to actually happen. It takes about a sprint of wall-clock time and roughly twenty minutes of work.

1. Find the members. show grants of role lists the users and roles holding it. If it is genuinely empty, you are already most of the way there.
2. Find what it actually used. Query the account usage history for queries run under that role over the last 90 days. A role with members and no queries is dormant, which is a different and more interesting finding.
3. Re-point. If it is in use, grant the members the correct functional role first. Nobody should lose access at the moment of deletion; that is what turns a cleanup into an incident.
4. Revoke, do not drop. Revoke the role from every member and leave the role in place. It is now inert and instantly restorable.
5. Wait a sprint. Two weeks catches the monthly report, the quarterly close job and the person who was on leave. This is the step everybody skips.
6. Then drop it. With a commit message saying what it was, who held it and what they hold now, so the next person to find the gap has an answer.

PUBLIC is granted automatically to every user, including the ones who have not been hired yet. A grant to PUBLIC is therefore not a grant to the current team; it is a standing commitment about everybody who will ever have an account, and it will not be re-examined when the contractor arrives in March.

It gets used because it is the fastest way to unblock somebody, and because a shared reference table genuinely does feel like it should be readable by all. Two things make it dangerous in a two-layer model specifically.

• It bypasses both layers. A privilege on PUBLIC is held by every functional role without appearing in any of their grant lists in a way a reviewer would notice, so the design and reality diverge silently.
• It is inherited into everything. PUBLIC sits at the bottom of the hierarchy, so its privileges travel up into every role above it, which is every role.

The workable position: allow PUBLIC to hold usage on the database and nothing else, and check for grants to it on a schedule. If a table really is readable by everyone, make an access role called ac_reference_read and grant it to every functional role. It is more typing and it is visible in the review, which is the entire difference.

Session 8 scores the finished policy set against 150 concrete access requests over five jobs, ten columns and three regions. The ladder starts from one shared analyst role and adds one control at a time. The row that matters for tonight is the fourth.

Two masking policies, both real work, take leaks from 109 to 70. Splitting the shared role five ways takes them from 70 to 22 in one move, and takes standing privileges from 17 to 1. Nothing else on the ladder moves standing privileges at all, because a standing privilege is a column a role can read in clear that the design does not mark clear for that role, and while five jobs share one role every one of them is over-granted by definition.

That is the argument for spending tonight on roles rather than on something that demonstrates better. Role modelling does the most work and gets the least attention, because it reads as administration.

The two-layer model adds roles on purpose, which raises the obvious question of where it stops being a model and starts being sprawl with better prefixes. Four signals are worth watching.

• Functional roles should track jobs, and jobs are countable. If you have more functional roles than distinct job descriptions, some of them are people, and people-shaped roles never get revoked.
• Access roles should track data bundles, not requests. An access role created for one request that one job made is a grant wearing a costume. Fold it into an existing bundle or accept that the bundle was wrong.
• Two roles with identical grants are one role. Worth a scheduled check, because these appear during migrations and nobody notices while both are in use.
• A role held by nobody for a quarter is a candidate. Not automatically wrong, but it goes on the list and gets the deletion procedure rather than being ignored for another year.

A useful sanity number: for one table with four classification tiers and five jobs, three to four access roles and five functional roles is right. If your first pass produces twenty, the bundles are too fine. If it produces two, at least one role is doing several jobs and the bench has already told you what that costs.