Stand: 2026-03-05

Übergeordnet: 4. Datenschicht — DB-Schemas Verwandt: 3. Gateway.Service

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

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).

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

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;

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

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;

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

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;

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.

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;

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

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;

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.

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;

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

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;

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

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;

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

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;

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.

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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.

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;

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:

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

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;

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:

-- 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;

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
);

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.

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
          )
);

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.

-- 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)
);

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.

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

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;