Rights: rules and scope

Rules determine what a caller can reach: which aliases, which kinds of objects and which objects, which methods, and whether access is read-only. Service keys, people, Windows accounts and groups, and SQL Server logins are assigned rules. This page explains how rules work together.

• Scope per caller and per alias.
• Several rules for different aliases, each evaluated as a whole.
• Rights per table, view, and procedure, including a view without rights to its tables.
• A personal key can narrow its person's rights but never grants more.

Rules are defined under Gateway:Security:Clients: the allowed aliases, resource kinds, verbs, and a read-only flag. A caller without a matching rule can't reach anything. A key without a rule stops the service from starting, because it could sign in but reach nothing.

Two callers can't share the same key; the startup check rejects such a configuration. Callers are identified by their key: if two callers had the same one, every request would be attributed to whichever the configuration lists last, and the other one couldn't be reached from outside at all. Which one that is would depend on the order of the entries—that's not scoping. The message names both callers but never the key, not even as a hash: a startup message ends up in a log and often in a support ticket after that.

A rule can consist of several entries with the same Id, and each entry names its own aliases. This is meant for the common case where an application only reads one database but has to write to a second one:

"Clients": [
  { "Id": "fachanwendung", "Databases": [ "fachdaten" ],
    "Resources": [ "views" ],                "Verbs": [ "GET" ],          "ReadOnly": true  },
 
  { "Id": "fachanwendung", "Databases": [ "anmeldung" ],
    "Resources": [ "views", "procedures" ],  "Verbs": [ "GET", "POST" ],  "ReadOnly": false }
]

Without this option, a caller's scope would apply to all of its aliases together. An application that has to write somewhere would then be able to write everywhere it reads—and what that means depends on what's in the other database. The service reads the catalog from the database, and nobody maintains it by hand, so everything that exists there can be reached. In a database with many procedures, a shared scope would make all of them callable.

Two entries with the same Id can't name the same alias, and * can't appear next to a second entry. Either one stops the service from starting, with a message that names the Id and the duplicate alias. Otherwise the order of the entries would decide which scope applies, and each entry would look correct on its own. Because at most one entry of an Id can match a request this way, the order doesn't matter. Different rules (different Ids)—for example, the rules a person gets from several Windows groups—can name the same alias; their rights add up.

An entry is evaluated as a whole. Taking the alias from one entry and the verb from another would add two narrow permissions up to a broad one that nobody wrote down. In the example above, a POST against fachdaten is refused, even though the same caller may POST against anmeldung.

A single entry that lists * next to a specific alias is still allowed—within one entry, the two can't contradict each other.

A rule can name the objects it applies to (Objects) and exclude individual ones (ExceptObjects). Both lists use the same format as an alias's table lists—*, schema.*, or schema.name—with names written the way the service records them (special characters encoded; see Names with special characters). Case doesn't matter.

"Clients": [
  { "Id": "lager-lesen", "Databases": [ "fachdaten" ],
    "Resources": [ "views" ], "Objects": [ "dbo.viewLagerbestand" ],
    "Verbs": [ "GET" ], "ReadOnly": true },
 
  { "Id": "lager-pflege", "Databases": [ "fachdaten" ],
    "Resources": [ "tables" ], "Objects": [ "lager.*" ], "ExceptObjects": [ "lager.Preise" ],
    "Verbs": [ "GET", "PATCH" ], "ReadOnly": false }
]

• Without a list, the rule applies to all objects of its kinds. The kinds under Resources still apply: a caller reaches an object listed under Objects only if its kind is allowed.
• Exclusion wins within a rule. Across a person's rules, rights add up as everywhere else: if another rule grants the object, it stays reachable.
• A view doesn't need rights to its tables. Only the object the request names is checked. SQL Server reads the tables behind the view through the ownership chain, as it does for any login that may read only the view. This is how you release part of a table: create a view, then release the view.
• What a caller may not read doesn't exist for them. The service document, $metadata, and the driver's catalog lists show only allowed objects; a request for any other object gets 404, just like a request for an object that doesn't exist. A readable object that the caller isn't allowed to change in the requested way gets 403.
• Not alongside sql. A custom statement reaches everything that the login it runs under can reach—the alias's login or, for a SQL Server login and under Impersonate, the person's own login. The service doesn't read the text and can't enforce an object list there, so a rule with both sql and an object list stops the service from starting or is refused when saved. The same applies to callers whose statements run under their own login (see the pass-through route): there the object list doesn't limit a custom statement. Checked statements without sql, on the other hand, are checked object by object against the object list.

The service's rules end at alias, resource kind, object, verb, and the read-only flag. The service has no per-row rules, and it won't get any: whether a person may see or change a particular row is a question for the database—answered by a view that filters and by procedures that check. Two overlapping permission systems would both end up wrong.

Requirement: the statement runs as the person. A view can filter by person only if SQL Server knows who the person is. That's the case:

• on an alias with Persons: Impersonate, for callers with their own SQL Server login (a Windows account or a SQL Server login recorded for the person): the service switches with EXECUTE AS LOGIN. SUSER_SNAME() and USER_NAME() then return the person and ORIGINAL_LOGIN() the service; SUSER_SID() returns the person's SID, in the statement as well as in column defaults and triggers. See Pass people's identities through to SQL Server.
• when signing in with a SQL Server login over HTTP Basic: the statement runs on a separate connection with that login.

The default is the trap. On an alias with Persons: Service—the default—all statements run under the alias's login. SUSER_SNAME() and SUSER_SID() then return the service account, the same for every caller. A view that filters on them doesn't report an error: depending on the filter, it returns nothing or everything. The same is true for callers with a service key: a service key never switches identity, not even on an alias with Impersonate.

What arrives in each case:

The service sets SESSION_CONTEXT as read-only for callers with a personal key and for a chosen database identity, on every alias and before every switch: wkl.person (the person's name), wkl.key (the personal key's ID or NULL), and wkl.role (the chosen identity or NULL). The connection pool resets these values before the connection goes to the next request.

Example 1: filter on the person's login. For an alias with Persons: Impersonate. The comparison uses SUSER_SID(), not the name: a renamed Windows account keeps its old login name in SQL Server, but the SID stays the same (see "Der Dienst kann diese Anmeldung in SQL Server nicht annehmen" (The service can't assume this login in SQL Server) after renaming an account).

CREATE VIEW maske.auftrag_liste AS
SELECT a.auftrag_id, a.betreff, a.bereich_id
FROM dbo.auftrag AS a
WHERE EXISTS (SELECT 1
              FROM iam.konto_bereich AS kb
              WHERE kb.bereich_id = a.bereich_id
                AND kb.login_sid = SUSER_SID());

Example 2: filter on the person from SESSION_CONTEXT. For callers without their own login (a personal key on an alias with Persons: Service). The value is the person's name in administration; renaming the person there changes it.

CREATE VIEW maske.auftrag_liste_schluessel AS
SELECT a.auftrag_id, a.betreff, a.bereich_id
FROM dbo.auftrag AS a
WHERE EXISTS (SELECT 1
              FROM iam.konto_bereich AS kb
              WHERE kb.bereich_id = a.bereich_id
                AND kb.person_name = CAST(SESSION_CONTEXT(N'wkl.person') AS nvarchar(256)));

This leaves you with a decision. Either combine both conditions in one view (… = SUSER_SID() OR … = SESSION_CONTEXT(N'wkl.person')) and accept that the service vouches for the person's name, or set the alias to Persons: Impersonate, give every person a login of their own, and use only example 1. The service then refuses a Windows account without its own login with 403. A personal key without a recorded SQL Server login, however, still runs under the alias's login there—for that key, SUSER_SID() returns the service account, and a filter like example 1 shows it nothing or everything. On such aliases, record a SQL Server login for every person who has a key, or combine both conditions.

Which routes switch identity. All six data routes open their connection through the same session: reading (including $apply), single rows, counting, procedure calls, writing to tables, and pass-through.

The catalog doesn't. The service reads $metadata, columns, keys, and the list of objects under the alias's login, and the service's rules decide which objects a caller sees. That has two consequences:

• A view that filters rows still shows all its columns in $metadata.
• The key of a view (from SQL Server's browse metadata) is determined under the alias's login, not the person's.

• Configuration: rules
• “Create a rule” (documentation of the administration app)
• Sign-in and identity pass-through