Restrict agents to their customers

An insurance agent works from their own laptop. They should read the customer and contract data of their customers, without health details and bank account, time-limited and revocable at any time. They shouldn't even see the rest of the portfolio.

Three layers, each where it's reliably enforced:

The view recognizes the agent from the session context: for every request with a personal key, the service sets SESSION_CONTEXT(N'wkl.person') read-only to the person's name (see row-level restriction).

1. Agent–customer assignment in the database, for example a table iam.vermittler_kunde (person_name, kunde_id), maintained by the line-of-business application.

2. Create a view that shows only assigned customers and only released columns:

   CREATE VIEW vermittler.kunde_vertrag AS
   SELECT k.kunde_id, k.name, k.ort, v.vertrag_id, v.produkt, v.beginn, v.beitrag
   FROM dbo.kunde AS k
   JOIN dbo.vertrag AS v ON v.kunde_id = k.kunde_id
   WHERE EXISTS (SELECT 1 FROM iam.vermittler_kunde AS vk
                 WHERE vk.kunde_id = k.kunde_id
                   AND vk.person_name = CAST(SESSION_CONTEXT(N'wkl.person') AS nvarchar(256)));

   Health details and bank account aren't in the view; for the agent they don't exist.

3. A rule on the view only, read-only (in the administration as ClientRules/ClientRuleItems):

   { "Id": "vermittler", "Databases": [ "bestand" ], "Resources": [ "views" ],
     "Objects": [ "vermittler.kunde_vertrag" ], "Verbs": [ "GET" ], "ReadOnly": true }

   Assign the rule to every agent person (PersonRules). Don't grant the right sql: with a custom statement the agent would reach everything the alias's login reaches, including the tables behind the view.

4. One key per agent with issue-key, time-limited to the contract term:

   { "person": "Max Vermittler", "label": "Laptop", "expiresAt": "2027-06-30T00:00:00Z",
     "readOnly": true, "databases": [ "bestand" ], "delivery": "mail" }

   For access from outside, the rule “keys from outside only with a code” from admit private devices applies.

5. Check with the agent's key: the service document (/odata/v4/bestand/vermittler/) lists only kunde_vertrag; a query returns only their customers; the service answers a request to /odata/v4/bestand/dbo/kunde with 404. With a second agent's key, that agent's customers appear.

Result: Each agent reads only their customers without sensitive fields, only through this view, until their key expires.

• The name is the link. wkl.person is the person's name in the administration; if the person is renamed, the assignment has to follow. To avoid that, give every person their own SQL login, set the alias to Persons: Impersonate, and filter by SUSER_SID() (example 1 under row-level restriction).
• Columns in $metadata. The view shows its columns to everyone allowed to see it; whatever isn't in the view appears nowhere.
• Passing data on. What the agent has read is on their device. So limit the view to what they need for their work.

• Rights: rules and scope
• Admit private devices
• Zero Trust and private devices