Stand: 2026-03-05

Übergeordnet: Auth-Architektur — Gesamtübersicht Verwandt: 4. Datenschicht — DB-Schemas

Vor der Migration existierten Benutzer und Rollen in zwei vollständig getrennten Systemen. ENIVERSCAFM verwaltete Auth-Daten in eigenen Tabellen (auth.*), die aus der historischen ENIVERS-Entwicklung stammten. Das LD-System führte parallel dazu tblPerson und tblRolle — eine eigene Benutzer- und Rollenverwaltung für die Elektrikerseite, mit eigenem Passwort-Mechanismus und eigener Admin-Oberfläche.

Ein Benutzer, der in beiden Systemen aktiv war, hatte in der Praxis zwei Identitäten: eine für AMED-Funktionen, eine für LD-Funktionen. Änderungen an Rollen oder Passwörtern mussten doppelt gepflegt werden. Es gab keine gemeinsame Audit-Spur — ob ein Benutzer in System A oder System B tätig war, ließ sich nicht zusammenhängend nachvollziehen.

Die Migration ist deshalb kein rein technischer Vorgang, sondern die Umsetzung einer konzeptuellen Entscheidung: Es gibt eine Benutzeridentität, nicht zwei. Phasen 1 und 2 haben diesen Zustand hergestellt — 558 Benutzer aus zwei Quellsystemen, konsolidiert in ENIVERSSIAM, ohne Anpassungen am bestehenden Anwendungscode, da ENIVERSCAFM die Tabellen weiterhin über Synonyme erreicht. Ab Phase 3 folgt die inhaltliche Migration: Assets, Prüfprozesse, Legacy-Rohrdaten.

Die Auth-Tabellen (auth.*) und die Personen-Tabelle (hr.person) lagen ursprünglich in ENIVERSCAFM. Ziel war die Konsolidierung: Auth-Daten gehören in die dedizierte Identity-Datenbank ENIVERSSIAM. ENIVERSCAFM erhält danach Synonyme — bestehender Code funktioniert transparent weiter ohne Änderungen.

Hinweis: Cross-DB Foreign Keys können in SQL Server nicht erzwungen werden.
Referenzielle Integrität zwischen ENIVERSCAFM und ENIVERSSIAM wird app-seitig
(Gateway.Service) sichergestellt.

Ausführungsreihenfolge:

1. siam_create_tables.sql — ENIVERSSIAM: Schema auth + 13 Tabellen + core Schema anlegen
2. siam_copy_data.sql — Daten von ENIVERSCAFM.auth.* nach ENIVERSSIAM.auth.* kopieren
3. siam_person.sql — core.person anlegen, Daten kopieren, FK wiederherstellen, Synonym in ENIVERSCAFM
4. siam_drop_and_synonyms.sql — ENIVERSCAFM: auth-Tabellen droppen, 13 Synonyme erstellen

/* ================================================================
   siam_create_tables.sql
   Ziel:    ENIVERSSIAM
   Zweck:   Schema 'auth' anlegen, alle 13 Tabellen erstellen
   Hinweis: Idempotent — IF NOT EXISTS-Guards für alle Objekte
   ================================================================ */
USE ENIVERSSIAM;
GO
 
-- Schema 'auth' anlegen (falls nicht vorhanden)
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'auth')
    EXEC('CREATE SCHEMA auth');
PRINT 'Schema auth: OK';
GO
 
-- ------------------------------------------------------------------
-- 1. auth.permission — Berechtigungs-Objekte (Resource / Action / Scope)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'permission'
)
BEGIN
    CREATE TABLE auth.permission (
        id          INT           NOT NULL IDENTITY(1,1),
        resource    VARCHAR(50)   NOT NULL,
        action      VARCHAR(20)   NOT NULL,
        scope       VARCHAR(20)   NOT NULL CONSTRAINT df_permission_scope DEFAULT ('all'),
        description NVARCHAR(256)     NULL,
        CONSTRAINT pk_permission PRIMARY KEY (id),
        CONSTRAINT uq_permission UNIQUE (resource, action, scope)
    );
    PRINT 'auth.permission: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 2. auth.role — Rollen-Definitionen
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'role'
)
BEGIN
    CREATE TABLE auth.role (
        id             INT           NOT NULL IDENTITY(1,1),
        code           VARCHAR(50)   NOT NULL,
        name           NVARCHAR(100) NOT NULL,
        description    NVARCHAR(MAX)     NULL,
        is_system_role BIT           NOT NULL CONSTRAINT df_role_system  DEFAULT (0),
        is_active      BIT           NOT NULL CONSTRAINT df_role_active  DEFAULT (1),
        created_at     DATETIME2     NOT NULL CONSTRAINT df_role_created DEFAULT (SYSUTCDATETIME()),
        updated_at     DATETIME2     NOT NULL CONSTRAINT df_role_updated DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_role      PRIMARY KEY (id),
        CONSTRAINT uq_role_code UNIQUE (code)
    );
    PRINT 'auth.role: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 3. auth.[user] — Anmeldedaten, TOTP, Passwort-Hash
--    Hinweis: person_id referenziert core.person (Intra-DB-FK,
--             wird nach Daten-Migration in siam_person.sql gesetzt)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'user'
)
BEGIN
    CREATE TABLE auth.[USER] (
        id                      INT            NOT NULL IDENTITY(1,1),
        person_id               INT            NOT NULL,
        username                VARCHAR(50)    NOT NULL,
        email                   NVARCHAR(256)      NULL,
        -- Passwort-Felder: Hash + Salt, nie Klartext (CWE-256)
        password_hash           VARBINARY(512)     NULL,
        password_salt           VARBINARY(32)      NULL,
        password_changed_at     DATETIME2          NULL,
        password_failed_attempts INT           NOT NULL CONSTRAINT df_user_pw_fails   DEFAULT (0),
        password_locked_until   DATETIME2          NULL,
        -- TOTP-Felder: Secret AES-verschlüsselt gespeichert (CWE-256)
        totp_secret             VARBINARY(256)     NULL,
        totp_enabled            BIT            NOT NULL CONSTRAINT df_user_totp_en    DEFAULT (0),
        totp_verified_at        DATETIME2          NULL,
        totp_failed_attempts    INT            NOT NULL CONSTRAINT df_user_totp_fails DEFAULT (0),
        totp_locked_until       DATETIME2          NULL,
        totp_pending_secret     VARBINARY(256)     NULL,
        totp_pending_expires_at DATETIME2          NULL,
        last_mfa_at             DATETIME2          NULL,
        last_mfa_method         VARCHAR(20)        NULL,
        is_active               BIT            NOT NULL CONSTRAINT df_user_active     DEFAULT (1),
        last_login_at           DATETIME2          NULL,
        created_at              DATETIME2      NOT NULL CONSTRAINT df_user_created    DEFAULT (SYSUTCDATETIME()),
        updated_at              DATETIME2      NOT NULL CONSTRAINT df_user_updated    DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_user          PRIMARY KEY (id),
        CONSTRAINT uq_user_person   UNIQUE (person_id),
        CONSTRAINT uq_user_username UNIQUE (username)
    );
    CREATE INDEX ix_user_person       ON auth.[USER] (person_id);
    CREATE INDEX ix_user_username     ON auth.[USER] (username);
    CREATE INDEX ix_user_active       ON auth.[USER] (is_active);
    CREATE INDEX ix_user_totp_enabled ON auth.[USER] (totp_enabled);
    PRINT 'auth.[user]: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 4. auth.device — Geräte-Sessions, PQ-Keys, Refresh-Token-Hash
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'device'
)
BEGIN
    CREATE TABLE auth.device (
        id                       INT            NOT NULL IDENTITY(1,1),
        device_id                VARCHAR(64)    NOT NULL,
        user_id                  INT            NOT NULL,
        name                     NVARCHAR(100)      NULL,
        passkey_credential_id    VARCHAR(256)       NULL,
        passkey_public_key       VARBINARY(MAX)     NULL,
        pq_dsa_public_key        VARBINARY(MAX)     NULL,
        pq_dsa_key_id            VARCHAR(64)        NULL,
        pq_kem_public_key        VARBINARY(MAX)     NULL,
        pq_kem_key_id            VARCHAR(64)        NULL,
        pq_key_created_at        DATETIME2          NULL,
        pq_dsa_public_key_prev   VARBINARY(MAX)     NULL,
        pq_dsa_key_id_prev       VARCHAR(64)        NULL,
        pq_kem_public_key_prev   VARBINARY(MAX)     NULL,
        pq_kem_key_id_prev       VARCHAR(64)        NULL,
        pq_key_prev_expires_at   DATETIME2          NULL,
        refresh_token_hash       VARBINARY(64)      NULL,
        refresh_token_expires_at DATETIME2          NULL,
        refresh_family_id        VARCHAR(64)        NULL,
        refresh_generation       INT            NOT NULL CONSTRAINT df_device_regen  DEFAULT (0),
        last_ip                  VARCHAR(45)        NULL,
        last_geo_country         CHAR(2)            NULL,
        last_geo_region          VARCHAR(50)        NULL,
        last_asn                 INT                NULL,
        last_asn_type            VARCHAR(20)        NULL,
        last_user_agent          NVARCHAR(512)      NULL,
        session_state            VARCHAR(20)    NOT NULL CONSTRAINT df_device_state  DEFAULT ('ACTIVE'),
        session_hold_until       DATETIME2          NULL,
        session_hold_reason      VARCHAR(50)        NULL,
        session_revoked_at       DATETIME2          NULL,
        session_revoked_reason   VARCHAR(50)        NULL,
        is_active                BIT            NOT NULL CONSTRAINT df_device_active DEFAULT (1),
        last_used_at             DATETIME2          NULL,
        created_at               DATETIME2      NOT NULL CONSTRAINT df_device_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_device      PRIMARY KEY (id),
        CONSTRAINT uq_device_id   UNIQUE (device_id),
        CONSTRAINT fk_device_user FOREIGN KEY (user_id) REFERENCES auth.[USER](id)
    );
    CREATE INDEX ix_device_user          ON auth.device (user_id);
    CREATE INDEX ix_device_user_active   ON auth.device (user_id, is_active);
    CREATE INDEX ix_device_session_state ON auth.device (session_state, user_id);
    CREATE INDEX ix_device_refresh_family ON auth.device (refresh_family_id);
    PRINT 'auth.device: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 5. auth.backup_code — MFA Backup-Codes (gehashed, CWE-256)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'backup_code'
)
BEGIN
    CREATE TABLE auth.backup_code (
        id           INT           NOT NULL IDENTITY(1,1),
        user_id      INT           NOT NULL,
        code_hash    VARBINARY(64) NOT NULL,
        code_prefix  CHAR(4)       NOT NULL,
        is_used      BIT           NOT NULL CONSTRAINT df_bc_used    DEFAULT (0),
        used_at      DATETIME2         NULL,
        used_from_ip VARCHAR(45)       NULL,
        created_at   DATETIME2     NOT NULL CONSTRAINT df_bc_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_backup_code PRIMARY KEY (id),
        CONSTRAINT fk_bc_user     FOREIGN KEY (user_id) REFERENCES auth.[USER](id)
    );
    CREATE INDEX ix_backup_code_user ON auth.backup_code (user_id, is_used);
    PRINT 'auth.backup_code: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 6. auth.known_location — Bekannte Standorte (Geo-IP, MFA-verifiziert)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'known_location'
)
BEGIN
    CREATE TABLE auth.known_location (
        id               INT        NOT NULL IDENTITY(1,1),
        user_id          INT        NOT NULL,
        geo_country      CHAR(2)    NOT NULL,
        geo_region       VARCHAR(50)    NULL,
        asn              INT            NULL,
        first_seen_at    DATETIME2  NOT NULL CONSTRAINT df_kl_first DEFAULT (SYSUTCDATETIME()),
        last_seen_at     DATETIME2  NOT NULL CONSTRAINT df_kl_last  DEFAULT (SYSUTCDATETIME()),
        expires_at       DATETIME2  NOT NULL,
        verified_via_mfa BIT        NOT NULL CONSTRAINT df_kl_mfa   DEFAULT (1),
        CONSTRAINT pk_known_location PRIMARY KEY (id),
        CONSTRAINT uq_known_location UNIQUE (user_id, geo_country, geo_region, asn),
        CONSTRAINT fk_kl_user        FOREIGN KEY (user_id) REFERENCES auth.[USER](id)
    );
    CREATE INDEX ix_known_location_user ON auth.known_location (user_id, expires_at);
    PRINT 'auth.known_location: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 7. auth.mfa_session — TOTP Challenge-Sessions (In-Memory-Äquivalent in DB)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'mfa_session'
)
BEGIN
    CREATE TABLE auth.mfa_session (
        id             INT         NOT NULL IDENTITY(1,1),
        mfa_session_id VARCHAR(64) NOT NULL,
        user_id        INT         NOT NULL,
        device_id      INT             NULL,
        reason         VARCHAR(50) NOT NULL,
        original_action VARCHAR(50)    NULL,
        is_verified    BIT         NOT NULL CONSTRAINT df_mfas_verified DEFAULT (0),
        verified_method VARCHAR(20)    NULL,
        attempts       INT         NOT NULL CONSTRAINT df_mfas_attempts DEFAULT (0),
        created_at     DATETIME2   NOT NULL CONSTRAINT df_mfas_created  DEFAULT (SYSUTCDATETIME()),
        expires_at     DATETIME2   NOT NULL,
        verified_at    DATETIME2       NULL,
        CONSTRAINT pk_mfa_session    PRIMARY KEY (id),
        CONSTRAINT uq_mfa_session_id UNIQUE (mfa_session_id),
        CONSTRAINT fk_mfas_user      FOREIGN KEY (user_id)   REFERENCES auth.[USER](id),
        CONSTRAINT fk_mfas_device    FOREIGN KEY (device_id) REFERENCES auth.device(id)
    );
    CREATE INDEX ix_mfa_session_user ON auth.mfa_session (user_id, created_at);
    PRINT 'auth.mfa_session: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 8. auth.nonce_cache — Replay-Protection (jti-Blacklist)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'nonce_cache'
)
BEGIN
    CREATE TABLE auth.nonce_cache (
        nonce       VARCHAR(64) NOT NULL,
        device_id   INT         NOT NULL,
        received_at DATETIME2   NOT NULL CONSTRAINT df_nc_received DEFAULT (SYSUTCDATETIME()),
        expires_at  DATETIME2   NOT NULL,
        CONSTRAINT pk_nonce_cache PRIMARY KEY (nonce)
    );
    CREATE INDEX ix_nonce_expires ON auth.nonce_cache (expires_at);
    PRINT 'auth.nonce_cache: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 9. auth.rate_limit_event — Rate-Limit Protokoll
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'rate_limit_event'
)
BEGIN
    CREATE TABLE auth.rate_limit_event (
        id         INT         NOT NULL IDENTITY(1,1),
        user_id    INT             NULL,
        device_id  INT             NULL,
        ip_address VARCHAR(45) NOT NULL,
        event_type VARCHAR(30) NOT NULL,
        created_at DATETIME2   NOT NULL CONSTRAINT df_rle_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_rate_limit_event PRIMARY KEY (id),
        CONSTRAINT fk_rle_user         FOREIGN KEY (user_id)   REFERENCES auth.[USER](id),
        CONSTRAINT fk_rle_device       FOREIGN KEY (device_id) REFERENCES auth.device(id)
    );
    CREATE INDEX ix_rate_limit_user_time ON auth.rate_limit_event (user_id, created_at);
    CREATE INDEX ix_rate_limit_ip_time   ON auth.rate_limit_event (ip_address, created_at);
    CREATE INDEX ix_rate_limit_cleanup   ON auth.rate_limit_event (created_at);
    PRINT 'auth.rate_limit_event: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 10. auth.refresh_token_family — Refresh Token Rotation Tracking
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'refresh_token_family'
)
BEGIN
    CREATE TABLE auth.refresh_token_family (
        id                 INT         NOT NULL IDENTITY(1,1),
        family_id          VARCHAR(64) NOT NULL,
        user_id            INT         NOT NULL,
        device_id          INT         NOT NULL,
        current_generation INT         NOT NULL CONSTRAINT df_rtf_gen     DEFAULT (0),
        is_revoked         BIT         NOT NULL CONSTRAINT df_rtf_revoked DEFAULT (0),
        revoked_reason     VARCHAR(50)     NULL,
        created_at         DATETIME2   NOT NULL CONSTRAINT df_rtf_created DEFAULT (SYSUTCDATETIME()),
        last_used_at       DATETIME2       NULL,
        revoked_at         DATETIME2       NULL,
        CONSTRAINT pk_refresh_token_family PRIMARY KEY (id),
        CONSTRAINT uq_rtf_family_id        UNIQUE (family_id),
        CONSTRAINT fk_rtf_user             FOREIGN KEY (user_id)   REFERENCES auth.[USER](id),
        CONSTRAINT fk_rtf_device           FOREIGN KEY (device_id) REFERENCES auth.device(id)
    );
    CREATE INDEX ix_refresh_family_user ON auth.refresh_token_family (user_id, is_revoked);
    PRINT 'auth.refresh_token_family: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 11. auth.role_permission — Rollen-Berechtigungs-Zuordnung (Junction)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'role_permission'
)
BEGIN
    CREATE TABLE auth.role_permission (
        role_id       INT NOT NULL,
        permission_id INT NOT NULL,
        CONSTRAINT pk_role_permission PRIMARY KEY (role_id, permission_id),
        CONSTRAINT fk_rp_role         FOREIGN KEY (role_id)       REFERENCES auth.role(id),
        CONSTRAINT fk_rp_permission   FOREIGN KEY (permission_id) REFERENCES auth.permission(id)
    );
    PRINT 'auth.role_permission: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 12. auth.server_key — Server-seitige ML-DSA/ML-KEM Schlüssel
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'server_key'
)
BEGIN
    CREATE TABLE auth.server_key (
        id                    INT            NOT NULL IDENTITY(1,1),
        key_id                VARCHAR(64)    NOT NULL,
        key_type              VARCHAR(20)    NOT NULL,
        algorithm             VARCHAR(30)    NOT NULL,
        public_key            VARBINARY(MAX) NOT NULL,
        -- Private Key verschlüsselt gespeichert (CWE-256)
        private_key_encrypted VARBINARY(MAX) NOT NULL,
        is_active             BIT            NOT NULL CONSTRAINT df_sk_active  DEFAULT (1),
        not_before            DATETIME2      NOT NULL CONSTRAINT df_sk_before  DEFAULT (SYSUTCDATETIME()),
        not_after             DATETIME2      NOT NULL,
        created_at            DATETIME2      NOT NULL CONSTRAINT df_sk_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_server_key    PRIMARY KEY (id),
        CONSTRAINT uq_server_key_id UNIQUE (key_id)
    );
    CREATE INDEX ix_server_key_type_active ON auth.server_key (key_type, is_active, not_after);
    PRINT 'auth.server_key: erstellt';
END
GO
 
-- ------------------------------------------------------------------
-- 13. auth.user_role — User-Rollen-Zuordnung (mit optionalem Site-Scope)
--     Hinweis: site_id referenziert org.site (Cross-DB → kein echtes FK,
--              Integrität app-seitig sichergestellt)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'user_role'
)
BEGIN
    CREATE TABLE auth.user_role (
        id         INT       NOT NULL IDENTITY(1,1),
        user_id    INT       NOT NULL,
        role_id    INT       NOT NULL,
        site_id    INT           NULL,   -- NULL = global, sonst Standort-Scope
        granted_at DATETIME2 NOT NULL CONSTRAINT df_ur_granted DEFAULT (SYSUTCDATETIME()),
        granted_by INT           NULL,
        CONSTRAINT pk_user_role      PRIMARY KEY (id),
        CONSTRAINT uq_user_role_site UNIQUE (user_id, role_id, site_id),
        CONSTRAINT fk_ur_user        FOREIGN KEY (user_id)    REFERENCES auth.[USER](id),
        CONSTRAINT fk_ur_role        FOREIGN KEY (role_id)    REFERENCES auth.role(id),
        CONSTRAINT fk_ur_granted_by  FOREIGN KEY (granted_by) REFERENCES auth.[USER](id)
    );
    PRINT 'auth.user_role: erstellt';
END
GO
 
PRINT '=== siam_create_tables: 13 Tabellen in ENIVERSSIAM.auth bereit ===';

/* ================================================================
   siam_copy_data.sql
   Quelle: ENIVERSCAFM.auth.*
   Ziel:   ENIVERSSIAM.auth.*
   Zweck:  Bestehende Auth-Daten in neue Identity-Datenbank übertragen
   Reihenfolge: Parents vor Children (FK-Abhängigkeiten beachten)
   ================================================================ */
 
-- ------------------------------------------------------------------
-- 1. auth.permission — 15 Zeilen, keine Abhängigkeiten
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.permission ON;
 
INSERT INTO ENIVERSSIAM.auth.permission (id, resource, action, scope, description)
SELECT id, resource, action, scope, description
FROM   ENIVERSCAFM.auth.permission;
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.permission OFF;
PRINT 'auth.permission: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
-- ------------------------------------------------------------------
-- 2. auth.role — 26 Zeilen, keine Abhängigkeiten
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.role ON;
 
INSERT INTO ENIVERSSIAM.auth.role
    (id, code, name, description, is_system_role, is_active, created_at, updated_at)
SELECT id, code, name, description, is_system_role, is_active, created_at, updated_at
FROM   ENIVERSCAFM.auth.role;
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.role OFF;
PRINT 'auth.role: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
-- ------------------------------------------------------------------
-- 3. auth.[user] — 241 Zeilen, hängt von core.person ab (person_id)
--    FK auth.[user] → core.person wird nach siam_person.sql gesetzt
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.[USER] ON;
 
INSERT INTO ENIVERSSIAM.auth.[USER] (
    id, person_id, username, email,
    password_hash, password_salt, password_changed_at,
    password_failed_attempts, password_locked_until,
    totp_secret, totp_enabled, totp_verified_at,
    totp_failed_attempts, totp_locked_until,
    totp_pending_secret, totp_pending_expires_at,
    last_mfa_at, last_mfa_method,
    is_active, last_login_at, created_at, updated_at
)
SELECT
    id, person_id, username, email,
    password_hash, password_salt, password_changed_at,
    password_failed_attempts, password_locked_until,
    totp_secret, totp_enabled, totp_verified_at,
    totp_failed_attempts, totp_locked_until,
    totp_pending_secret, totp_pending_expires_at,
    last_mfa_at, last_mfa_method,
    is_active, last_login_at, created_at, updated_at
FROM ENIVERSCAFM.auth.[USER];
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.[USER] OFF;
PRINT 'auth.[user]: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
-- ------------------------------------------------------------------
-- 4–8. Tabellen ohne Daten (0 Zeilen) — Synonyme genügen
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.device ON;
INSERT INTO ENIVERSSIAM.auth.device (
    id, device_id, user_id, name,
    passkey_credential_id, passkey_public_key,
    pq_dsa_public_key, pq_dsa_key_id, pq_kem_public_key, pq_kem_key_id, pq_key_created_at,
    pq_dsa_public_key_prev, pq_dsa_key_id_prev, pq_kem_public_key_prev, pq_kem_key_id_prev, pq_key_prev_expires_at,
    refresh_token_hash, refresh_token_expires_at, refresh_family_id, refresh_generation,
    last_ip, last_geo_country, last_geo_region, last_asn, last_asn_type, last_user_agent,
    session_state, session_hold_until, session_hold_reason,
    session_revoked_at, session_revoked_reason,
    is_active, last_used_at, created_at
)
SELECT
    id, device_id, user_id, name,
    passkey_credential_id, passkey_public_key,
    pq_dsa_public_key, pq_dsa_key_id, pq_kem_public_key, pq_kem_key_id, pq_key_created_at,
    pq_dsa_public_key_prev, pq_dsa_key_id_prev, pq_kem_public_key_prev, pq_kem_key_id_prev, pq_key_prev_expires_at,
    refresh_token_hash, refresh_token_expires_at, refresh_family_id, refresh_generation,
    last_ip, last_geo_country, last_geo_region, last_asn, last_asn_type, last_user_agent,
    session_state, session_hold_until, session_hold_reason,
    session_revoked_at, session_revoked_reason,
    is_active, last_used_at, created_at
FROM ENIVERSCAFM.auth.device;
SET IDENTITY_INSERT ENIVERSSIAM.auth.device OFF;
PRINT 'auth.device: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.backup_code ON;
INSERT INTO ENIVERSSIAM.auth.backup_code
    (id, user_id, code_hash, code_prefix, is_used, used_at, used_from_ip, created_at)
SELECT id, user_id, code_hash, code_prefix, is_used, used_at, used_from_ip, created_at
FROM   ENIVERSCAFM.auth.backup_code;
SET IDENTITY_INSERT ENIVERSSIAM.auth.backup_code OFF;
PRINT 'auth.backup_code: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.known_location ON;
INSERT INTO ENIVERSSIAM.auth.known_location
    (id, user_id, geo_country, geo_region, asn, first_seen_at, last_seen_at, expires_at, verified_via_mfa)
SELECT id, user_id, geo_country, geo_region, asn, first_seen_at, last_seen_at, expires_at, verified_via_mfa
FROM   ENIVERSCAFM.auth.known_location;
SET IDENTITY_INSERT ENIVERSSIAM.auth.known_location OFF;
PRINT 'auth.known_location: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.mfa_session ON;
INSERT INTO ENIVERSSIAM.auth.mfa_session
    (id, mfa_session_id, user_id, device_id, reason, original_action, is_verified, verified_method, attempts, created_at, expires_at, verified_at)
SELECT id, mfa_session_id, user_id, device_id, reason, original_action, is_verified, verified_method, attempts, created_at, expires_at, verified_at
FROM   ENIVERSCAFM.auth.mfa_session;
SET IDENTITY_INSERT ENIVERSSIAM.auth.mfa_session OFF;
PRINT 'auth.mfa_session: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
INSERT INTO ENIVERSSIAM.auth.nonce_cache (nonce, device_id, received_at, expires_at)
SELECT nonce, device_id, received_at, expires_at
FROM   ENIVERSCAFM.auth.nonce_cache;
PRINT 'auth.nonce_cache: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.rate_limit_event ON;
INSERT INTO ENIVERSSIAM.auth.rate_limit_event (id, user_id, device_id, ip_address, event_type, created_at)
SELECT id, user_id, device_id, ip_address, event_type, created_at
FROM   ENIVERSCAFM.auth.rate_limit_event;
SET IDENTITY_INSERT ENIVERSSIAM.auth.rate_limit_event OFF;
PRINT 'auth.rate_limit_event: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.refresh_token_family ON;
INSERT INTO ENIVERSSIAM.auth.refresh_token_family
    (id, family_id, user_id, device_id, current_generation, is_revoked, revoked_reason, created_at, last_used_at, revoked_at)
SELECT id, family_id, user_id, device_id, current_generation, is_revoked, revoked_reason, created_at, last_used_at, revoked_at
FROM   ENIVERSCAFM.auth.refresh_token_family;
SET IDENTITY_INSERT ENIVERSSIAM.auth.refresh_token_family OFF;
PRINT 'auth.refresh_token_family: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
-- ------------------------------------------------------------------
-- 9. auth.role_permission — 33 Zeilen, hängt von role + permission ab
-- ------------------------------------------------------------------
INSERT INTO ENIVERSSIAM.auth.role_permission (role_id, permission_id)
SELECT role_id, permission_id
FROM   ENIVERSCAFM.auth.role_permission;
PRINT 'auth.role_permission: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.server_key ON;
INSERT INTO ENIVERSSIAM.auth.server_key
    (id, key_id, key_type, algorithm, public_key, private_key_encrypted, is_active, not_before, not_after, created_at)
SELECT id, key_id, key_type, algorithm, public_key, private_key_encrypted, is_active, not_before, not_after, created_at
FROM   ENIVERSCAFM.auth.server_key;
SET IDENTITY_INSERT ENIVERSSIAM.auth.server_key OFF;
PRINT 'auth.server_key: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
-- ------------------------------------------------------------------
-- 10. auth.user_role — 9.445 Zeilen, zuletzt (FK auf user + role)
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.user_role ON;
 
INSERT INTO ENIVERSSIAM.auth.user_role (id, user_id, role_id, site_id, granted_at, granted_by)
SELECT id, user_id, role_id, site_id, granted_at, granted_by
FROM   ENIVERSCAFM.auth.user_role;
 
SET IDENTITY_INSERT ENIVERSSIAM.auth.user_role OFF;
PRINT 'auth.user_role: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
 
PRINT '=== siam_copy_data: Datenmigration ENIVERSCAFM → ENIVERSSIAM abgeschlossen ===';

/* ================================================================
   siam_person.sql
   Zweck:  core.person in ENIVERSSIAM erstellen, Daten aus
           ENIVERSCAFM.hr.person übertragen, FK von auth.[user]
           wiederherstellen, Synonym in ENIVERSCAFM anlegen
   ================================================================ */
 
-- ------------------------------------------------------------------
-- Schritt 1: Schema 'core' und Tabelle 'core.person' in ENIVERSSIAM
-- ------------------------------------------------------------------
USE ENIVERSSIAM;
GO
 
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'core')
    EXEC('CREATE SCHEMA core');
GO
 
CREATE TABLE core.person (
    id             INT           NOT NULL IDENTITY(1,1),
    first_name     NVARCHAR(50)  NOT NULL,
    last_name      NVARCHAR(50)  NOT NULL,
    POSITION       NVARCHAR(100)     NULL,
    notes          NVARCHAR(MAX)     NULL,
    is_active      BIT           NOT NULL CONSTRAINT df_person_active  DEFAULT (1),
    is_system_user BIT           NOT NULL CONSTRAINT df_person_sysuser DEFAULT (0),
    created_at     DATETIME2     NOT NULL CONSTRAINT df_person_created DEFAULT (SYSUTCDATETIME()),
    updated_at     DATETIME2     NOT NULL CONSTRAINT df_person_updated DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT pk_person PRIMARY KEY (id)
);
PRINT 'core.person: erstellt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 2: Daten aus ENIVERSCAFM.hr.person übertragen (241 Zeilen)
--            IDENTITY_INSERT ON, damit Original-IDs erhalten bleiben
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.core.person ON;
 
INSERT INTO ENIVERSSIAM.core.person
    (id, first_name, last_name, POSITION, notes, is_active, is_system_user, created_at, updated_at)
SELECT id, first_name, last_name, POSITION, notes, is_active, is_system_user, created_at, updated_at
FROM   ENIVERSCAFM.hr.person;
 
SET IDENTITY_INSERT ENIVERSSIAM.core.person OFF;
PRINT 'core.person: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
GO
 
-- ------------------------------------------------------------------
-- Schritt 3: FK auth.[user].person_id → core.person(id) wiederherstellen
--            (jetzt echter Intra-DB-FK innerhalb ENIVERSSIAM)
-- ------------------------------------------------------------------
ALTER TABLE auth.[USER]
    ADD CONSTRAINT fk_user_person
    FOREIGN KEY (person_id) REFERENCES core.person(id);
PRINT 'FK auth.[user].person_id → core.person(id): wiederhergestellt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 4: hr.person in ENIVERSCAFM droppen und Synonym anlegen
-- ------------------------------------------------------------------
USE ENIVERSCAFM;
GO
 
DROP TABLE hr.person;
PRINT 'hr.person: aus ENIVERSCAFM entfernt';
GO
 
CREATE SYNONYM hr.person FOR ENIVERSSIAM.core.person;
PRINT 'Synonym hr.person → ENIVERSSIAM.core.person: erstellt';
GO
 
PRINT '=== siam_person: core.person Migration abgeschlossen ===';

/* ================================================================
   siam_drop_and_synonyms.sql
   Ziel:   ENIVERSCAFM
   Zweck:  Cross-Schema FKs und auth-Tabellen entfernen,
           13 Synonyme auf ENIVERSSIAM erstellen
   Hinweis: Reihenfolge zwingend — Children vor Parents droppen
   ================================================================ */
USE ENIVERSCAFM;
GO
 
-- ------------------------------------------------------------------
-- Schritt 1: Cross-Schema FKs droppen
--            (audit→auth, settings→auth, auth→hr, auth→org)
--            Diese können nach DB-Move nicht als echte FKs wieder-
--            hergestellt werden — Integrität wird app-seitig gewährleistet
-- ------------------------------------------------------------------
IF OBJECT_ID('audit.event_log') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_el_user'
                  AND parent_object_id = OBJECT_ID('audit.event_log'))
    ALTER TABLE audit.event_log DROP CONSTRAINT fk_el_user;
 
IF OBJECT_ID('audit.event_log') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_el_device'
                  AND parent_object_id = OBJECT_ID('audit.event_log'))
    ALTER TABLE audit.event_log DROP CONSTRAINT fk_el_device;
 
IF OBJECT_ID('audit.pq_signature') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_pqs_user'
                  AND parent_object_id = OBJECT_ID('audit.pq_signature'))
    ALTER TABLE audit.pq_signature DROP CONSTRAINT fk_pqs_user;
 
IF OBJECT_ID('audit.pq_signature') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_pqs_device'
                  AND parent_object_id = OBJECT_ID('audit.pq_signature'))
    ALTER TABLE audit.pq_signature DROP CONSTRAINT fk_pqs_device;
 
IF OBJECT_ID('settings.user_preference') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_user_preference_user'
                  AND parent_object_id = OBJECT_ID('settings.user_preference'))
    ALTER TABLE settings.user_preference DROP CONSTRAINT fk_user_preference_user;
 
IF EXISTS (SELECT 1 FROM sys.foreign_keys
           WHERE name = 'fk_user_person'
             AND parent_object_id = OBJECT_ID('auth.user'))
    ALTER TABLE auth.[USER] DROP CONSTRAINT fk_user_person;
 
IF EXISTS (SELECT 1 FROM sys.foreign_keys
           WHERE name = 'fk_ur_site'
             AND parent_object_id = OBJECT_ID('auth.user_role'))
    ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_site;
 
PRINT 'Cross-Schema FKs: entfernt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 2: Interne auth-FKs droppen (Children zuerst)
-- ------------------------------------------------------------------
 
-- auth.user_role hängt von auth.[user] + auth.role ab
ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_granted_by;
ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_user;
ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_role;
 
-- auth.role_permission
ALTER TABLE auth.role_permission DROP CONSTRAINT fk_rp_permission;
ALTER TABLE auth.role_permission DROP CONSTRAINT fk_rp_role;
 
-- auth.refresh_token_family
ALTER TABLE auth.refresh_token_family DROP CONSTRAINT fk_rtf_device;
ALTER TABLE auth.refresh_token_family DROP CONSTRAINT fk_rtf_user;
 
-- auth.rate_limit_event
ALTER TABLE auth.rate_limit_event DROP CONSTRAINT fk_rle_device;
ALTER TABLE auth.rate_limit_event DROP CONSTRAINT fk_rle_user;
 
-- auth.mfa_session
ALTER TABLE auth.mfa_session DROP CONSTRAINT fk_mfas_device;
ALTER TABLE auth.mfa_session DROP CONSTRAINT fk_mfas_user;
 
-- auth.known_location
ALTER TABLE auth.known_location DROP CONSTRAINT fk_kl_user;
 
-- auth.backup_code
ALTER TABLE auth.backup_code DROP CONSTRAINT fk_bc_user;
 
-- auth.device (letzte Child-Tabelle von auth.[user])
ALTER TABLE auth.device DROP CONSTRAINT fk_device_user;
 
PRINT 'Interne auth-FKs: entfernt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 3: auth-Tabellen droppen (Children vor Parents)
-- ------------------------------------------------------------------
DROP TABLE auth.nonce_cache;
DROP TABLE auth.mfa_session;
DROP TABLE auth.rate_limit_event;
DROP TABLE auth.refresh_token_family;
DROP TABLE auth.backup_code;
DROP TABLE auth.known_location;
DROP TABLE auth.role_permission;
DROP TABLE auth.user_role;
DROP TABLE auth.device;
DROP TABLE auth.[USER];
DROP TABLE auth.role;
DROP TABLE auth.permission;
DROP TABLE auth.server_key;
PRINT 'auth-Tabellen: aus ENIVERSCAFM entfernt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 4: 13 Synonyme auf ENIVERSSIAM.auth.* erstellen
--            Bestehender Code kann weiterhin ENIVERSCAFM.auth.*
--            ansprechen — transparent umgeleitet
-- ------------------------------------------------------------------
CREATE SYNONYM auth.backup_code          FOR ENIVERSSIAM.auth.backup_code;
CREATE SYNONYM auth.device               FOR ENIVERSSIAM.auth.device;
CREATE SYNONYM auth.known_location       FOR ENIVERSSIAM.auth.known_location;
CREATE SYNONYM auth.mfa_session          FOR ENIVERSSIAM.auth.mfa_session;
CREATE SYNONYM auth.nonce_cache          FOR ENIVERSSIAM.auth.nonce_cache;
CREATE SYNONYM auth.permission           FOR ENIVERSSIAM.auth.permission;
CREATE SYNONYM auth.rate_limit_event     FOR ENIVERSSIAM.auth.rate_limit_event;
CREATE SYNONYM auth.refresh_token_family FOR ENIVERSSIAM.auth.refresh_token_family;
CREATE SYNONYM auth.role                 FOR ENIVERSSIAM.auth.role;
CREATE SYNONYM auth.role_permission      FOR ENIVERSSIAM.auth.role_permission;
CREATE SYNONYM auth.server_key           FOR ENIVERSSIAM.auth.server_key;
CREATE SYNONYM auth.[USER]               FOR ENIVERSSIAM.auth.[USER];
CREATE SYNONYM auth.user_role            FOR ENIVERSSIAM.auth.user_role;
PRINT '13 Synonyme in ENIVERSCAFM.auth: erstellt';
GO
 
PRINT '=== siam_drop_and_synonyms: Phase 1 vollständig abgeschlossen ===';

Verbindung: localhost, Datenbank ENIVERS, Schema dbo

Pfad: D:\Workspace\AMED\LdUsr_ACCDB\LdUsr.accdb Export-Stand: 2026-01-31 (288 Formulare, 95 Berichte, 76 Module, 19 Makros)

Die Access-Datei enthält primär die Benutzeroberfläche (Formulare, Berichte, VBA) und nutzt verknüpfte Tabellen (Linked Tables) aus der ENIVERS SQL-Server-Datenbank. Es gibt keine eigenständigen Access-nativen Tabellen — alle Daten kommen aus Quelle 1.

Relevant als Referenz für Geschäftsregeln:

• Formular-Logik: fpopLogin, fpopMandant
• Rollen-/Rechte-Zuordnung: sfrRolleRechtObjekt, sfrPersonRolle
• Scheduling-Logik: ufrmAgent_Schedule, ufrmAgent_Log

Sicherheitshinweis: strPeAccPasswort enthält Klartext-Passwörter aus dem alten
Access-System. Diese Spalte darf niemals übernommen werden (CWE-256).
Benutzer erhalten beim ersten Login einen Passwort-Reset-Link.

ENIVERS.dbo.tblRolle enthält idRolle = 4 — in ENIVERSSIAM ist auth.role.id = 4 bereits durch eine System-Rolle belegt. Kein IDENTITY_INSERT für auth.role möglich.

Lösung: Code-basiertes Mapping über temporäre #role_map-Tabelle:

• Matching via LOWER(ar.code) = LOWER(r.strRolAbk)
• Für bestehende Rollen (z. B. entwickler, admins) → existing id übernehmen
• Für neue LD-Rollen → auto-generierte IDs aus IDENTITY

3 Duplikat-Paare in tblPerson (gleicher strPeWinUser, verschiedene idPerson):

Lösung: WITH dedup AS (SELECT MIN(idPerson) … GROUP BY strPeWinUser) Ergebnis: 73 neue auth.user-Einträge statt 76 (3 Duplikate zusammengeführt).

/* ================================================================
   ld_migration.sql
   Quelle:  ENIVERS.dbo (tblPerson, tblRolle, tblPersonRolle,
                          tblPersonRolleBetrieb)
   Ziel:    ENIVERSSIAM (core.person, auth.[user], auth.role,
                          auth.user_role)
   Strategie:
     core.person  — IDENTITY_INSERT ON (IDs 1–179 kollidieren nicht
                    mit bestehenden 591–2896)
     auth.[user]  — auto-ID (kein IDENTITY_INSERT)
     auth.role    — auto-ID; Code-basiertes Mapping alter → neuer IDs
     auth.user_role — nur fehlende Einträge einfügen (NOT EXISTS)
     Passwortfelder — nicht übernommen (CWE-256, Klartext)
   ================================================================ */
 
USE ENIVERSSIAM;
GO
 
-- ------------------------------------------------------------------
-- Schritt 1: Neue Personen einfügen (76 Zeilen, IDENTITY_INSERT)
--            Nur Personen, die noch keinen übereinstimmenden
--            auth.[user]-Eintrag haben (Collation-sicherer Vergleich)
-- ------------------------------------------------------------------
SET IDENTITY_INSERT core.person ON;
 
INSERT INTO core.person
    (id, first_name, last_name, POSITION, notes,
     is_active, is_system_user, created_at, updated_at)
SELECT
    p.idPerson,
    ISNULL(p.strPeVorname,  ''),
    ISNULL(p.strPeNachname, ''),
    p.strPePosition,
    p.memPeNotiz,
    ISNULL(p.bitPeAktiv,   1),
    ISNULL(p.bitPeSysUser, 0),
    p.dtCreated,
    ISNULL(p.dtUpdated, p.dtCreated)
FROM ENIVERS.dbo.tblPerson p
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.[USER] u
    WHERE  LOWER(u.username   COLLATE Latin1_General_CI_AS)
         = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS)
);
 
SET IDENTITY_INSERT core.person OFF;
PRINT 'core.person: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' neue Zeilen eingefügt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 2: Neue auth.[user] einfügen (auto-ID)
--            person_id = idPerson aus tblPerson (soeben eingefügt)
--            username  = strPeWinUser (Windows-Login für Kerberos)
--            Kein Passwort — Legacy-Passwörter nicht übernehmen (CWE-256)
-- ------------------------------------------------------------------
INSERT INTO auth.[USER]
    (person_id, username, is_active, created_at, updated_at)
SELECT
    p.idPerson,
    p.strPeWinUser,
    ISNULL(p.bitPeAktiv, 1),
    p.dtCreated,
    ISNULL(p.dtUpdated, p.dtCreated)
FROM ENIVERS.dbo.tblPerson p
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.[USER] u
    WHERE  LOWER(u.username   COLLATE Latin1_General_CI_AS)
         = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS)
);
PRINT 'auth.[user]: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' neue Zeilen eingefügt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 3: Neue Rollen einfügen (auto-ID)
--            Kein IDENTITY_INSERT — idRolle=4 kollidiert mit
--            bestehendem System-Role (viewer). Code-basiertes Matching.
--            12 neue LD-spezifische Rollen werden eingefügt.
-- ------------------------------------------------------------------
INSERT INTO auth.role
    (code, name, description, is_system_role, is_active, created_at, updated_at)
SELECT
    r.strRolAbk,
    r.strRolName,
    r.memRolNotiz,
    ISNULL(r.bitRolSysRole, 0),
    ISNULL(r.bitRolAktiv,   1),
    r.dtCreated,
    ISNULL(r.dtEdited, r.dtCreated)
FROM ENIVERS.dbo.tblRolle r
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.role ar
    WHERE  LOWER(ar.code COLLATE Latin1_General_CI_AS)
         = LOWER(r.strRolAbk COLLATE Latin1_General_CI_AS)
);
PRINT 'auth.role: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' neue Zeilen eingefügt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 4: Rollen-ID-Mapping aufbauen (#role_map, temporär)
--            Verbindet ENIVERS idRolle mit dem tatsächlichen
--            ENIVERSSIAM auth.role.id (via Code-Matching)
-- ------------------------------------------------------------------
IF OBJECT_ID('tempdb..#role_map') IS NOT NULL
    DROP TABLE #role_map;
 
CREATE TABLE #role_map (
    enivers_id INT NOT NULL,
    siam_id    INT NOT NULL
);
 
INSERT INTO #role_map (enivers_id, siam_id)
SELECT
    r.idRolle,
    ar.id
FROM ENIVERS.dbo.tblRolle r
JOIN auth.role ar
    ON LOWER(ar.code COLLATE Latin1_General_CI_AS)
     = LOWER(r.strRolAbk COLLATE Latin1_General_CI_AS);
 
PRINT '#role_map: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Mappings erstellt';
 
-- ------------------------------------------------------------------
-- Schritt 5: Personen-ID-Mapping aufbauen (#person_map, temporär)
--            Verbindet ENIVERS idPerson mit ENIVERSSIAM auth.[user].id
-- ------------------------------------------------------------------
IF OBJECT_ID('tempdb..#person_map') IS NOT NULL
    DROP TABLE #person_map;
 
CREATE TABLE #person_map (
    enivers_id   INT NOT NULL,
    siam_user_id INT NOT NULL
);
 
INSERT INTO #person_map (enivers_id, siam_user_id)
SELECT
    p.idPerson,
    u.id
FROM ENIVERS.dbo.tblPerson p
JOIN auth.[USER] u
    ON LOWER(u.username   COLLATE Latin1_General_CI_AS)
     = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS);
 
PRINT '#person_map: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Mappings erstellt';
 
-- ------------------------------------------------------------------
-- Schritt 6a: auth.user_role aus tblPersonRolle (kein Site-Scope)
--             Nur einfügen wenn Kombination noch nicht vorhanden
-- ------------------------------------------------------------------
INSERT INTO auth.user_role (user_id, role_id, granted_at, granted_by)
SELECT
    pm.siam_user_id,
    rm.siam_id,
    pr.dtCreated,
    NULL
FROM ENIVERS.dbo.tblPersonRolle pr
JOIN #person_map pm ON pm.enivers_id = pr.fiPrPerson
JOIN #role_map   rm ON rm.enivers_id = pr.fiPrRolle
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.user_role ur
    WHERE  ur.user_id = pm.siam_user_id
      AND  ur.role_id = rm.siam_id
      AND  ur.site_id IS NULL
);
PRINT 'auth.user_role (ohne Site): ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen eingefügt';
 
-- ------------------------------------------------------------------
-- Schritt 6b: auth.user_role aus tblPersonRolleBetrieb (mit Site-Scope)
--             fiPrbBetrieb → site_id (FK auf org.site, Cross-DB,
--             app-seitig sichergestellt)
-- ------------------------------------------------------------------
INSERT INTO auth.user_role (user_id, role_id, site_id, granted_at, granted_by)
SELECT
    pm.siam_user_id,
    rm.siam_id,
    prb.fiPrbBetrieb,   -- site_id: direkte Übernahme des Betrieb-IDs
    prb.dtCreated,
    NULL
FROM ENIVERS.dbo.tblPersonRolleBetrieb prb
JOIN #person_map pm ON pm.enivers_id = prb.fiPrbPerson
JOIN #role_map   rm ON rm.enivers_id = prb.fiPrbRolle
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.user_role ur
    WHERE  ur.user_id = pm.siam_user_id
      AND  ur.role_id = rm.siam_id
      AND  ur.site_id = prb.fiPrbBetrieb
);
PRINT 'auth.user_role (mit Site): ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen eingefügt';
GO
 
-- ------------------------------------------------------------------
-- Schritt 7: Ergebnis-Übersicht
-- ------------------------------------------------------------------
SELECT 'core.person'    AS tabelle, COUNT(*) AS gesamt FROM core.person
UNION ALL
SELECT 'auth.[user]',                COUNT(*) FROM auth.[USER]
UNION ALL
SELECT 'auth.role',                  COUNT(*) FROM auth.role
UNION ALL
SELECT 'auth.user_role',             COUNT(*) FROM auth.user_role;
 
PRINT '=== ld_migration: Phase 2 abgeschlossen ===';
GO

/* ================================================================
   ld_migration_users_fix.sql
   Zweck:   auth.[user] für neue ENIVERS-Personen einfügen
            (Schritt 2 aus ld_migration.sql schlug fehl wegen
            3 Username-Duplikaten in tblPerson)
   Duplikate:
     uid1041442 (idPerson 150 + 151)
     EID0109386 (idPerson 171 + 172)
     pede0153   (idPerson 129 + 168)
   Lösung: Pro Username nur den ältesten Eintrag (MIN idPerson)
   ================================================================ */
 
USE ENIVERSSIAM;
GO
 
-- Deduplizierung: Pro Username nur MIN(idPerson) verwenden
WITH dedup AS (
    SELECT MIN(idPerson) AS idPerson
    FROM   ENIVERS.dbo.tblPerson
    GROUP BY strPeWinUser
)
INSERT INTO auth.[USER]
    (person_id, username, is_active, created_at, updated_at)
SELECT
    p.idPerson,
    p.strPeWinUser,
    ISNULL(p.bitPeAktiv, 1),
    p.dtCreated,
    ISNULL(p.dtUpdated, p.dtCreated)
FROM ENIVERS.dbo.tblPerson p
JOIN dedup d ON d.idPerson = p.idPerson
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.[USER] u
    WHERE  LOWER(u.username   COLLATE Latin1_General_CI_AS)
         = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS)
);
PRINT 'auth.[user]: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen eingefügt';
 
-- Ergebnis-Übersicht
SELECT 'core.person'    AS tabelle, COUNT(*) AS gesamt FROM core.person
UNION ALL
SELECT 'auth.[user]',                COUNT(*) FROM auth.[USER]
UNION ALL
SELECT 'auth.role',                  COUNT(*) FROM auth.role
UNION ALL
SELECT 'auth.user_role',             COUNT(*) FROM auth.user_role;
GO

Hinweis: 73 statt 76 neue User — 3 Username-Duplikate zusammengeführt (MIN idPerson).
Personen ohne gültigen strPeWinUser (NULL) erhalten keinen auth.[user]-Eintrag.