====== 4.1 DB-Funktionen — Auth Controller ======

//Stand: 2026-03-05//

Übergeordnet: [[de:int:wvdsshell:notes:01-siam:auth-database|4. Datenschicht — DB-Schemas]]
Verwandt: [[de:int:wvdsshell:notes:01-siam:auth-gateway|3. Gateway.Service]]

Dieses Dokument beschreibt alle gespeicherten Prozeduren, Funktionen und Views in
**ENIVERSSIAM**, die ''WvdS.Data.Gateway.Controller.Auth'' auf Datenbankebene unterstützen.

===== Endpoint → DB-Zuordnung =====

^ Endpoint                          ^ DB-Objekte                                                         ^
| POST ''/auth/kerberos''            | ''fn_user_lookup'', ''usp_login_success'', ''usp_device_session_create'', ''fn_user_roles'' |
| POST ''/auth/external''            | ''fn_user_lookup'', ''usp_password_failure'', ''usp_mfa_session_create'' |
| POST ''/auth/totp/verify''         | ''usp_mfa_session_consume'', ''usp_totp_failure'', ''usp_login_success'', ''usp_device_session_create'', ''fn_user_roles'' |
| POST ''/auth/token/refresh''       | ''usp_refresh_token_rotate''                                        |
| GET  ''/auth/totp/enroll''         | ''usp_totp_enrollment_begin''                                       |
| DELETE ''/auth/totp/enroll''       | ''usp_totp_reset''                                                  |
| GET  ''/features''                 | ''fn_get_features''                                                 |
| Middleware: Replay-Schutz         | ''usp_nonce_check_insert''                                          |
| Middleware: Rate-Limiting         | ''usp_rate_limit_record'', ''fn_is_rate_limited''                   |
| Middleware: Autorisierung         | ''fn_get_user_id'', ''fn_is_user_member_of''                        |
| Hintergrundaufgabe (periodisch)   | ''usp_expired_cleanup''                                             |

===== Naming-Konvention =====

^ Präfix    ^ Typ                          ^ Beispiel                              ^
| ''usp_''  | Stored Procedure (mit DML)   | ''auth.usp_login_success''            |
| ''fn_''   | Scalar / Table-Valued Function | ''auth.fn_user_lookup'', ''auth.fn_is_rate_limited'' |
| ''v_''    | View                         | ''auth.v_user_auth''                  |

> **Faustregel:** Reine Lesezugriffe mit tabellarischem Ergebnis → TVF (''fn_''), verwendbar in ''FROM''/''JOIN''/''CROSS APPLY''.
> Operationen mit DML (''INSERT'', ''UPDATE'') oder ''OUTPUT''-Parametern → Stored Procedure (''usp_'').

Identifier: lowercase snake_case (konsistent mit den Tabellennamen in ENIVERSSIAM).

===== Stored Procedures =====

==== auth.usp_login_success ====

**Zweck:** Erfolgreichen Login protokollieren — ''last_login_at'' aktualisieren, Passwort-Fehlversuche zurücksetzen.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_login_success
    @user_id    INT,
    @ip_address VARCHAR(45)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE auth.[user]
    SET    last_login_at            = SYSUTCDATETIME(),
           password_failed_attempts = 0,
           password_locked_until    = NULL,
           updated_at               = SYSUTCDATETIME()
    WHERE  id = @user_id;
END;
</code>

| Tabellen | ''auth.[user]'' |
| Aufgerufen von | POST ''/auth/kerberos'', POST ''/auth/totp/verify'' |

==== auth.usp_password_failure ====

**Zweck:** Fehlgeschlagenen Passwort-Versuch registrieren. Setzt Lockout, wenn der Schwellwert erreicht ist. Gibt die verbleibenden Versuche und ggf. den Lockout-Zeitpunkt zurück.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_password_failure
    @user_id        INT,
    @max_attempts   INT = 5,
    @lockout_minutes INT = 15,
    @attempts_remaining INT      OUTPUT,
    @locked_until       DATETIME2 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE auth.[user]
    SET    password_failed_attempts = password_failed_attempts + 1,
           password_locked_until    =
               CASE
                   WHEN password_failed_attempts + 1 >= @max_attempts
                   THEN DATEADD(MINUTE, @lockout_minutes, SYSUTCDATETIME())
                   ELSE NULL
               END,
           updated_at = SYSUTCDATETIME()
    WHERE  id = @user_id;

    SELECT
        @attempts_remaining = CASE
            WHEN password_failed_attempts >= @max_attempts THEN 0
            ELSE @max_attempts - password_failed_attempts
        END,
        @locked_until = password_locked_until
    FROM auth.[user]
    WHERE id = @user_id;
END;
</code>

| Tabellen | ''auth.[user]'' |
| Aufgerufen von | POST ''/auth/external'' |

==== auth.usp_mfa_session_create ====

**Zweck:** TOTP-Challenge-Session anlegen. Gibt eine neue ''challenge_id'' zurück, die der Gateway im JSON-Response an den Client sendet.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_mfa_session_create
    @user_id          INT,
    @reason           VARCHAR(50),
    @lifetime_seconds INT = 300,
    @challenge_id     VARCHAR(64) OUTPUT,
    @expires_at       DATETIME2   OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- challenge_id: Gateway übergibt eine bereits generierte UUID
    -- (NEWID() wäre akzeptabel, aber der Gateway-Layer kontrolliert die Entropie)
    SET @expires_at = DATEADD(SECOND, @lifetime_seconds, SYSUTCDATETIME());

    INSERT INTO auth.mfa_session
        (mfa_session_id, user_id, reason, expires_at)
    VALUES
        (@challenge_id,  @user_id, @reason, @expires_at);
END;
</code>

| Tabellen | ''auth.mfa_session'' |
| Aufgerufen von | POST ''/auth/external'' |
| Hinweis | ''@challenge_id'' wird vom Gateway generiert (kryptografisch zufällig) und als INPUT übergeben. |

==== auth.usp_mfa_session_consume ====

**Zweck:** TOTP-Challenge atomar prüfen und verbrauchen. Inkrementiert den Versuchs-Counter; sperrt die Challenge bei Überschreitung von ''@max_attempts''. Gibt Validierungsstatus und verbleibende Versuche zurück.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_mfa_session_consume
    @challenge_id       VARCHAR(64),
    @max_attempts       INT = 5,
    @user_id            INT       OUTPUT,
    @is_valid           BIT       OUTPUT,
    @is_expired         BIT       OUTPUT,
    @attempts           INT       OUTPUT,
    @attempts_remaining INT       OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    SELECT
        @user_id    = user_id,
        @is_expired = CASE WHEN expires_at < SYSUTCDATETIME() THEN 1 ELSE 0 END,
        @attempts   = attempts
    FROM auth.mfa_session WITH (UPDLOCK, ROWLOCK)
    WHERE mfa_session_id = @challenge_id
      AND is_verified     = 0;

    IF @user_id IS NULL
    BEGIN
        -- Challenge unbekannt
        SET @is_valid = 0;
        ROLLBACK;
        RETURN;
    END;

    -- Versuchs-Counter erhöhen
    -- NOTE: mfa_session hat keine updated_at-Spalte
    UPDATE auth.mfa_session
    SET    attempts = attempts + 1
    WHERE  mfa_session_id = @challenge_id;

    SET @attempts           = @attempts + 1;
    SET @attempts_remaining = CASE
        WHEN @attempts >= @max_attempts THEN 0
        ELSE @max_attempts - @attempts
    END;

    -- Gültig: Kein Ablauf, kein Überschreiten der Max-Versuche
    SET @is_valid = CASE
        WHEN @is_expired = 0 AND @attempts <= @max_attempts THEN 1
        ELSE 0
    END;

    -- Bei Erfolg: Session als verifiziert markieren
    IF @is_valid = 1
        UPDATE auth.mfa_session
        SET    is_verified    = 1,
               verified_at    = SYSUTCDATETIME()
        WHERE  mfa_session_id = @challenge_id;

    COMMIT;
END;
</code>

| Tabellen | ''auth.mfa_session'' |
| Aufgerufen von | POST ''/auth/totp/verify'' |
| Transaktional | Ja — UPDLOCK verhindert Race Conditions bei gleichzeitigen Verify-Requests |

==== auth.usp_totp_failure ====

**Zweck:** Fehlerhaften TOTP-Code registrieren. Analog zu ''usp_password_failure'', aber für das TOTP-Lockout.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_totp_failure
    @user_id          INT,
    @max_attempts     INT = 5,
    @lockout_minutes  INT = 15,
    @attempts_remaining INT       OUTPUT,
    @locked_until       DATETIME2 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE auth.[user]
    SET    totp_failed_attempts = totp_failed_attempts + 1,
           totp_locked_until    =
               CASE
                   WHEN totp_failed_attempts + 1 >= @max_attempts
                   THEN DATEADD(MINUTE, @lockout_minutes, SYSUTCDATETIME())
                   ELSE NULL
               END,
           updated_at = SYSUTCDATETIME()
    WHERE  id = @user_id;

    SELECT
        @attempts_remaining = CASE
            WHEN totp_failed_attempts >= @max_attempts THEN 0
            ELSE @max_attempts - totp_failed_attempts
        END,
        @locked_until = totp_locked_until
    FROM auth.[user]
    WHERE id = @user_id;
END;
</code>

| Tabellen | ''auth.[user]'' |
| Aufgerufen von | POST ''/auth/totp/verify'' |

==== auth.usp_device_session_create ====

**Zweck:** Neues Gerät registrieren und initialen Refresh-Token-Hash speichern.
Der eigentliche Refresh-Token-Wert verlässt den Gateway nie in Klartext — nur der SHA-256-Hash landet in der DB.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_device_session_create
    @user_id           INT,
    @device_id         VARCHAR(64),
    @name              NVARCHAR(100),
    @refresh_token_hash VARBINARY(64),
    @family_id         VARCHAR(64),
    @ip_address        VARCHAR(45),
    @user_agent        NVARCHAR(512),
    @device_pk         INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO auth.device
        (device_id, user_id, name,
         refresh_token_hash, refresh_family_id, refresh_generation,
         last_ip, last_user_agent,
         session_state, is_active, created_at)
    VALUES
        (@device_id, @user_id, @name,
         @refresh_token_hash, @family_id, 1,
         @ip_address, @user_agent,
         'ACTIVE', 1, SYSUTCDATETIME());

    SET @device_pk = SCOPE_IDENTITY();

    -- Refresh-Token-Family initialisieren
    INSERT INTO auth.refresh_token_family
        (family_id, user_id, device_id, current_generation, is_revoked, created_at)
    VALUES
        (@family_id, @user_id, @device_pk, 1, 0, SYSUTCDATETIME());
END;
</code>

| Tabellen | ''auth.device'', ''auth.refresh_token_family'' |
| Aufgerufen von | POST ''/auth/kerberos'', POST ''/auth/totp/verify'' |

==== auth.usp_refresh_token_rotate ====

**Zweck:** Refresh-Token rotieren — Generation prüfen, neuen Hash speichern, Generation inkrementieren.
Bei Generation-Mismatch: gesamte Token-Familie widerrufen (gestohlenes Token erkannt, CWE-613).

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_refresh_token_rotate
    @family_id          VARCHAR(64),
    @expected_generation INT,
    @new_token_hash     VARBINARY(64),
    @new_expires_at     DATETIME2,
    @success            BIT       OUTPUT,
    @user_id            INT       OUTPUT,
    @device_pk          INT       OUTPUT,
    @revoke_reason      VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    SELECT
        @user_id   = user_id,
        @device_pk = device_id
    FROM auth.refresh_token_family WITH (UPDLOCK, ROWLOCK)
    WHERE family_id          = @family_id
      AND current_generation = @expected_generation
      AND is_revoked         = 0;

    IF @user_id IS NULL
    BEGIN
        -- Generation-Mismatch oder bereits widerrufen →
        -- Gesamte Familie widerrufen (Replay / Diebstahl)
        SET @success       = 0;
        SET @revoke_reason = 'generation_mismatch';

        UPDATE auth.refresh_token_family
        SET    is_revoked      = 1,
               revoked_reason  = 'generation_mismatch',
               revoked_at      = SYSUTCDATETIME()
        WHERE  family_id = @family_id;

        COMMIT;
        RETURN;
    END;

    -- Generation inkrementieren, neuen Hash speichern
    UPDATE auth.refresh_token_family
    SET    current_generation = current_generation + 1,
           last_used_at       = SYSUTCDATETIME()
    WHERE  family_id = @family_id;

    UPDATE auth.device
    SET    refresh_token_hash       = @new_token_hash,
           refresh_token_expires_at = @new_expires_at,
           refresh_generation       = refresh_generation + 1,
           last_used_at             = SYSUTCDATETIME()
    WHERE  id = @device_pk;

    SET @success       = 1;
    SET @revoke_reason = NULL;

    COMMIT;
END;
</code>

| Tabellen | ''auth.refresh_token_family'', ''auth.device'' |
| Aufgerufen von | POST ''/auth/token/refresh'' |
| Transaktional | Ja — UPDLOCK verhindert parallele Rotation derselben Family |

==== auth.usp_session_revoke ====

**Zweck:** Geräte-Session widerrufen (Logout, Admin-Eingriff).

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_session_revoke
    @device_id VARCHAR(64),
    @reason    VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- NOTE: auth.device hat keine updated_at-Spalte
    UPDATE auth.device
    SET    session_state          = 'REVOKED',
           session_revoked_at     = SYSUTCDATETIME(),
           session_revoked_reason = @reason,
           is_active              = 0
    WHERE  device_id = @device_id;

    -- Zugehörige Refresh-Token-Familie ebenfalls widerrufen
    UPDATE rtf
    SET    rtf.is_revoked     = 1,
           rtf.revoked_reason = @reason,
           rtf.revoked_at     = SYSUTCDATETIME()
    FROM   auth.refresh_token_family rtf
    JOIN   auth.device d ON d.id = rtf.device_id
    WHERE  d.device_id = @device_id;
END;
</code>

| Tabellen | ''auth.device'', ''auth.refresh_token_family'' |
| Aufgerufen von | Logout-Endpoint (zukünftig), Admin-Revoke |

==== auth.usp_totp_enrollment_begin ====

**Zweck:** TOTP-Enrollment starten — ''totp_pending_secret'' setzen.
Das Geheimnis verbleibt bis zur Bestätigung im Pending-Zustand (CWE-256: verschlüsselt übergeben).

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_totp_enrollment_begin
    @user_id         INT,
    @pending_secret  VARBINARY(256),  -- AES-256-GCM-verschlüsselt vom Gateway
    @expires_minutes INT = 10
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE auth.[user]
    SET    totp_pending_secret  = @pending_secret,
           totp_pending_expires_at = DATEADD(MINUTE, @expires_minutes, SYSUTCDATETIME()),
           updated_at           = SYSUTCDATETIME()
    WHERE  id = @user_id;
END;
</code>

| Tabellen | ''auth.[user]'' |
| Aufgerufen von | GET ''/auth/totp/enroll'' |

==== auth.usp_totp_enrollment_confirm ====

**Zweck:** TOTP-Enrollment abschließen — ''totp_pending_secret'' → ''totp_secret'' übernehmen, ''totp_enabled = 1'' setzen.
Schlägt fehl (''@success = 0'') wenn kein gültiges Pending vorhanden oder abgelaufen.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_totp_enrollment_confirm
    @user_id INT,
    @success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE auth.[user]
    SET    totp_secret              = totp_pending_secret,
           totp_enabled             = 1,
           totp_verified_at         = SYSUTCDATETIME(),
           totp_failed_attempts     = 0,
           totp_locked_until        = NULL,
           totp_pending_secret      = NULL,
           totp_pending_expires_at  = NULL,
           updated_at               = SYSUTCDATETIME()
    WHERE  id                       = @user_id
      AND  totp_pending_secret      IS NOT NULL
      AND  totp_pending_expires_at  > SYSUTCDATETIME();

    SET @success = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END;
END;
</code>

| Tabellen | ''auth.[user]'' |
| Aufgerufen von | GET ''/auth/totp/enroll'' (nach Bestätigungs-Code vom User) |

==== auth.usp_totp_reset ====

**Zweck:** TOTP-Enrollment vollständig zurücksetzen — ''totp_secret'', ''totp_enabled'', Pending-Felder löschen.

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_totp_reset
    @user_id INT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE auth.[user]
    SET    totp_secret             = NULL,
           totp_enabled            = 0,
           totp_verified_at        = NULL,
           totp_failed_attempts    = 0,
           totp_locked_until       = NULL,
           totp_pending_secret     = NULL,
           totp_pending_expires_at = NULL,
           updated_at              = SYSUTCDATETIME()
    WHERE  id = @user_id;
END;
</code>

| Tabellen | ''auth.[user]'' |
| Aufgerufen von | DELETE ''/auth/totp/enroll'' |

==== auth.usp_nonce_check_insert ====

**Zweck:** Replay-Schutz — Nonce atomar prüfen und einfügen.
Ein PK-Konflikt bedeutet Replay (die Nonce wurde bereits verarbeitet).

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_nonce_check_insert
    @nonce     VARCHAR(64),
    @device_id INT,
    @expires_at DATETIME2,
    @is_replay BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO auth.nonce_cache (nonce, device_id, received_at, expires_at)
        VALUES (@nonce, @device_id, SYSUTCDATETIME(), @expires_at);
        SET @is_replay = 0;
    END TRY
    BEGIN CATCH
        -- PK-Violation = Nonce wurde bereits gesehen → Replay
        IF ERROR_NUMBER() = 2627
            SET @is_replay = 1;
        ELSE
            THROW;
    END CATCH;
END;
</code>

| Tabellen | ''auth.nonce_cache'' |
| Aufgerufen von | Replay-Schutz-Middleware (vor jeder authentifizierten Anfrage) |

==== auth.usp_rate_limit_record ====

**Zweck:** Rate-Limit-Ereignis protokollieren. Der Gateway ruft diese SP nach jedem relevanten Auth-Ereignis auf (Fehlversuch, unbekannter User, Brute-Force-Indikator).

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_rate_limit_record
    @user_id   INT          = NULL,
    @device_id INT          = NULL,
    @ip_address VARCHAR(45),
    @event_type VARCHAR(30)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO auth.rate_limit_event (user_id, device_id, ip_address, event_type, created_at)
    VALUES (@user_id, @device_id, @ip_address, @event_type, SYSUTCDATETIME());
END;
</code>

| Tabellen | ''auth.rate_limit_event'' |
| Aufgerufen von | Rate-Limit-Middleware, Auth-Controller bei Fehlversuchen |

==== auth.usp_expired_cleanup ====

**Zweck:** Wartungsaufgabe — abgelaufene Nonces und MFA-Sessions bereinigen.
Wird vom Gateway periodisch aufgerufen (z. B. alle 5 Minuten via Timer-Thread).

<code sql>
CREATE OR ALTER PROCEDURE auth.usp_expired_cleanup
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cutoff DATETIME2 = SYSUTCDATETIME();

    DELETE FROM auth.nonce_cache   WHERE expires_at < @cutoff;
    DELETE FROM auth.mfa_session   WHERE expires_at < @cutoff AND is_verified = 0;
    DELETE FROM auth.rate_limit_event WHERE created_at < DATEADD(DAY, -7, @cutoff);
END;
</code>

| Tabellen | ''auth.nonce_cache'', ''auth.mfa_session'', ''auth.rate_limit_event'' |
| Aufgerufen von | Gateway-interner Maintenance-Timer |

===== Scalar Functions =====

==== auth.fn_is_user_locked ====

**Zweck:** Prüft ob der Benutzer wegen Passwort-Fehlversuchen gesperrt ist.

<code sql>
CREATE OR ALTER FUNCTION auth.fn_is_user_locked (@user_id INT)
RETURNS BIT
AS
BEGIN
    RETURN (
        SELECT CASE
            WHEN password_locked_until > SYSUTCDATETIME() THEN 1
            ELSE 0
        END
        FROM auth.[user]
        WHERE id = @user_id
    );
END;
</code>

==== auth.fn_is_totp_locked ====

**Zweck:** Prüft ob der TOTP-Kanal des Benutzers gesperrt ist.

<code sql>
CREATE OR ALTER FUNCTION auth.fn_is_totp_locked (@user_id INT)
RETURNS BIT
AS
BEGIN
    RETURN (
        SELECT CASE
            WHEN totp_locked_until > SYSUTCDATETIME() THEN 1
            ELSE 0
        END
        FROM auth.[user]
        WHERE id = @user_id
    );
END;
</code>

==== auth.fn_is_rate_limited ====

**Zweck:** Zählt Rate-Limit-Ereignisse in einem Zeitfenster und gibt 1 zurück wenn das Limit überschritten ist.

<code sql>
CREATE OR ALTER FUNCTION auth.fn_is_rate_limited (
    @user_id        INT,
    @ip_address     VARCHAR(45),
    @event_type     VARCHAR(30),
    @window_seconds INT,
    @max_count      INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @count INT;
    DECLARE @window_start DATETIME2 = DATEADD(SECOND, -@window_seconds, SYSUTCDATETIME());

    SELECT @count = COUNT(*)
    FROM   auth.rate_limit_event
    WHERE  event_type  = @event_type
      AND  created_at >= @window_start
      AND  (
               (@user_id    IS NOT NULL AND user_id    = @user_id)
            OR (@ip_address IS NOT NULL AND ip_address = @ip_address)
           );

    RETURN CASE WHEN @count >= @max_count THEN 1 ELSE 0 END;
END;
</code>

==== auth.fn_get_user_id ====

**Zweck:** Komfort-Wrapper um ''fn_user_lookup'' — gibt direkt die ''user_id'' zurück.
Vermeidet CROSS APPLY, wenn nur die ID benötigt wird (z. B. in Middleware-Checks vor dem eigentlichen Auth-Flow).
Gibt ''NULL'' zurück, wenn der Username nicht existiert.

<code sql>
CREATE OR ALTER FUNCTION auth.fn_get_user_id (@username VARCHAR(50))
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT user_id
        FROM   auth.fn_user_lookup(@username)
    );
END;
</code>

| Aufgerufen von | Middleware: Autorisierung, überall wo ''user_id'' aus ''username'' benötigt wird |

==== auth.fn_is_user_member_of ====

**Zweck:** Prüft ob ein Benutzer mindestens eine Rolle aus einer kommaseparierten Liste besitzt.
Wraps ''fn_user_roles'' und vergleicht ''role_code'' gegen alle Einträge in ''@csv''.
Gibt 1 zurück sobald ein Treffer gefunden wird, sonst 0.

Typische Verwendung in Middleware-Autorisierung:

<code sql>
-- Zugriff erlauben wenn der User 'admin' oder 'supervisor' ist
IF auth.fn_is_user_member_of(@user_id, 'admin,supervisor') = 1 ...
</code>

<code sql>
CREATE OR ALTER FUNCTION auth.fn_is_user_member_of (
    @user_id INT,
    @csv     VARCHAR(250)   -- kommaseparierte role_codes, z. B. 'admin,supervisor'
)
RETURNS BIT
AS
BEGIN
    -- CHARINDEX mit umgebenden Kommas verhindert Teilstring-Treffer
    -- (z. B. 'admin' matcht nicht 'administrator' in 'administrator,viewer')
    RETURN (
        SELECT CASE WHEN EXISTS (
            SELECT 1
            FROM   auth.fn_user_roles(@user_id, NULL) r
            WHERE  CHARINDEX(
                       ',' + LTRIM(RTRIM(r.role_code)) + ',',
                       ',' + @csv + ','
                   ) > 0
        ) THEN 1 ELSE 0 END
    );
END;
</code>

| Aufgerufen von | Middleware: Autorisierung |
| Kombinierbar mit | ''auth.fn_get_user_id'' für den typischen Pattern: ''fn_is_user_member_of(fn_get_user_id(@username), @csv)'' |

===== Table-Valued Functions =====

==== auth.fn_user_lookup ====

**Zweck:** User-Datensatz für Authentifizierung laden — reine Leseoperation, daher TVF.
Passwort-Hash und TOTP-Status werden zurückgegeben; der Gateway-Layer vergleicht den Hash selbst (CWE-256 — kein Krypto-Code in der DB).
Gibt 0 Zeilen zurück wenn der Username nicht existiert.

Verwendbar in ''FROM'', ''JOIN'' und ''CROSS APPLY'' — kombinierbar mit ''fn_user_roles'':

<code sql>
-- Beispiel: User-Daten + Rollen in einem Query
SELECT u.*, r.role_code
FROM   auth.fn_user_lookup('max.muster') u
CROSS APPLY auth.fn_user_roles(u.user_id, NULL) r;
</code>

<code sql>
CREATE OR ALTER FUNCTION auth.fn_user_lookup (@username VARCHAR(50))
RETURNS TABLE
AS
RETURN (
    -- Passwort-Hash + Salt werden im Gateway per PBKDF2 verglichen (CWE-256).
    -- Niemals Klartext-Passwort zurückgeben oder in der DB vergleichen.
    SELECT
        u.id                      AS user_id,
        u.person_id,
        p.first_name,
        p.last_name,
        u.is_active,
        u.password_hash,
        u.password_salt,
        u.password_changed_at,
        u.password_failed_attempts,
        u.password_locked_until,
        u.totp_enabled,
        u.totp_failed_attempts,
        u.totp_locked_until
    FROM auth.[user] u
    JOIN core.person p ON p.id = u.person_id
    WHERE u.username = @username COLLATE Latin1_General_CI_AS
);
</code>

| Tabellen | ''auth.[user]'', ''core.person'' |
| Aufgerufen von | POST ''/auth/kerberos'', POST ''/auth/external'' |

==== auth.fn_user_roles ====

**Zweck:** Gibt alle aktiven Rollen eines Benutzers zurück — optional gefiltert nach Site-Scope.
Wird im Gateway verwendet, um JWT-Role-Claims zu befüllen.

<code sql>
CREATE OR ALTER FUNCTION auth.fn_user_roles (
    @user_id INT,
    @site_id INT = NULL   -- NULL = globale + site-spezifische Rollen zurückgeben
)
RETURNS TABLE
AS
RETURN (
    SELECT
        r.id        AS role_id,
        r.code      AS role_code,
        r.name      AS role_name,
        ur.site_id,
        ur.granted_at
    FROM auth.user_role ur
    JOIN auth.role r ON r.id = ur.role_id
    WHERE ur.user_id = @user_id
      AND r.is_active = 1
      AND (
              @site_id IS NULL                          -- alle Rollen
           OR ur.site_id IS NULL                        -- globale Rollen immer einschließen
           OR ur.site_id = @site_id                     -- site-spezifische Rollen
          )
);
</code>

| Aufgerufen von | POST ''/auth/kerberos'', POST ''/auth/totp/verify'' (JWT-Claims befüllen) |

==== auth.fn_get_features ====

**Zweck:** Gibt die Feature-Keys zurück, die für eine gegebene ''install_id'' aktiv sind.
Liest aktuell aus ENIVERSCAFM (''auth.*''-Namespace), später aus ENIVERSASYS.

<code sql>
-- Platzhalter-Implementierung: aktuell Feature-Flags aus einer
-- einfachen Konfigurations-Tabelle. Schema wird in Phase 5 erweitert.
CREATE OR ALTER FUNCTION auth.fn_get_features (@install_id VARCHAR(64))
RETURNS TABLE
AS
RETURN (
    SELECT feature_key
    FROM   auth.feature_flag
    WHERE  is_active  = 1
      AND  (install_id = @install_id OR install_id IS NULL)
);
</code>

> **Hinweis:** ''auth.feature_flag'' ist eine zukünftige Tabelle — wird in Phase 5
> (ENIVERSASYS) definiert. Bis dahin kann ''fn_get_features'' eine statische Liste zurückgeben.

| Aufgerufen von | GET ''/features'' |

===== Views =====

==== auth.v_user_auth ====

**Zweck:** Kombinierte Sicht auf User + Person + berechnete Lockout-Flags.
Vereinfacht den häufigsten Auth-Lesezugriff im Gateway.

<code sql>
CREATE OR ALTER VIEW auth.v_user_auth
AS
SELECT
    u.id                      AS user_id,
    u.person_id,
    u.username,
    p.first_name,
    p.last_name,
    u.is_active,
    u.totp_enabled,
    u.totp_failed_attempts,
    -- Berechnete Lockout-Flags (live, kein Cache-Risiko)
    CASE
        WHEN u.password_locked_until > SYSUTCDATETIME() THEN 1
        ELSE 0
    END                       AS is_password_locked,
    u.password_locked_until,
    u.password_failed_attempts,
    CASE
        WHEN u.totp_locked_until > SYSUTCDATETIME() THEN 1
        ELSE 0
    END                       AS is_totp_locked,
    u.totp_locked_until,
    u.last_login_at,
    u.created_at
FROM auth.[user] u
JOIN core.person p ON p.id = u.person_id;
</code>

| Tabellen | ''auth.[user]'', ''core.person'' |
| Aufgerufen von | Direkter SELECT statt ''usp_user_lookup'' für einfache Lesefälle |