Vier-Augen-Prinzip (MSSQL)
Diese Seite ist eine Anleitung, mit welcher das Vier-Augen-Prizip in der DMC implementiert werden kann. Da die Implementierung aufgrund der Rollenverwaltung und den angebundenen Folgeprozessen sehr individuell ist, werden Codes/Skripte bereitgestellt, welche nur als Fundament zu sehen sind. An manchen Stellen müssen diese individuell angepasst werden.
Ziel dabei ist folgende Funktionalität: Es wird im Vorhinein festgelegt, welche Tabellen von zwei verschiedenen Personen freigegeben werden müssen. Wenn in einer der Tabellen ein neuer Eintrag existiert, muss die Tabelle nach dem Vier-Augen-Prinzip freigegeben werden. Diese Funktionalität wird durch eine robuste, QS-geprüfte Hilfstabelle realisiert.
Die Freigabetabelle
Die Grundlage der Datenfreigabe ist die Freigabetabelle. Diese hat die Funktion, die Freigaben zu historisieren und als "Ampel" zu fungieren. Sie stellt also die Information bereit, welche Tabellen zur Beladung freigegeben wurden und welche nicht.
Um diese zu erstellen, wird die Funktion Tabelle manuell erstellen genutzt. Es wird empfohlen, die Tabelle wie folgt zu konfigurieren, da alle Prozeduren und Funktionen auf dieser Tabellenstruktur zugreifen:
Schritt 1: Informationen zu Tabelle
DB-Tabellenname: ADM_DATENFREIGABE
DMC-Anzeigename: Datenfreigabe
Beschreibung: Zur Freigabe von Tabellen
DMC-Kategorie: Muss vom Administrator festgelegt werden
Schritt 2: Spalten hinzufügen
Hier sind DB-Spaltenname und DMC-Anzeigename identisch
Spalte 1: SOURCE_TABLE, Datentyp Zeichenkette, Verpflichtend
Spalte 2: TARGET_SYSTEM, Datentyp Zeichenkette
Spalte 3: STATUS, Datentyp Zeichenkette
Spalte 4: RELEASE_FOR_EXPORT, Datentyp Checkbox, Verpflichtend
Spalte 5: DMC_NAME, Datentyp Zeichenkette, Verpflichtend
Nun bitte überpfügen, ob die Tabelle in der richtigen Kategorie zu sehen ist.
Die CHANGEDATES View
Später werden über die DMC mehrere QS-Regeln in der Freigabetabelle sowie eine Prozedur implementiert, welche auf diese View zurückgreifen. Ein QS-Check überprüft, wann die letzte Änderung an einer freizugebenden Tabelle stattgefunden hat. Die Logik hinter dieser View ist die Akkumulierung aller Historisierungseinträge der zu exportierenden Tabellen. Hier sollen die Tabellen eingetragen werden, welche in der Freigabetabelle später gelistet werden sollen:
Klicke hier, um den SQL-Code anzuzeigen
-- Check if the view already exists in the dbo schema and, if so, delete it.
IF OBJECT_ID('dbo.ADM_TAB_CHANGEDATES_V', 'V') IS NOT NULL
DROP VIEW dbo.ADM_TAB_CHANGEDATES_V;
GO
-- THESE ARE ONLY SAMPLE TABLES FOR TESTING! PLEASE UPDATE
CREATE VIEW dbo.ADM_TAB_CHANGEDATES_V
(TABLE_NAME, LAST_CHANGE)
AS
SELECT
'golden_records' AS TABLE_NAME,
MAX(HIST_FROM) AS LAST_CHANGE
FROM
dbo.golden_records
UNION ALL
SELECT
'mitarbeiter' AS TABLE_NAME,
MAX(HIST_FROM) AS LAST_CHANGE
FROM
dbo.mitarbeiter
UNION ALL
SELECT
'plandaten' AS TABLE_NAME,
MAX(HIST_FROM) AS LAST_CHANGE
FROM
dbo.plandaten
GO
Die FREIGABE_STATUS View
Nun folgt das Skript für eine Ansicht, die nur in der Datenbank zu sehen ist und nicht in der DMC verwaltet wird. Die View liefert Informationen für die Exportprozesse. Dabei werden nur die Tabellen angezeigt, welche den Freigabeprozess erfolgreich abgeschlossen haben. Die View muss an die benutzten Exportprozesse angepasst werden.
Klicke hier, um den SQL-Code anzuzeigen
IF OBJECT_ID('dbo.ADM_FREIGABE_STATUS_V', 'V') IS NOT NULL
BEGIN
DROP VIEW dbo.ADM_FREIGABE_STATUS_V;
END
GO
CREATE VIEW dbo.ADM_FREIGABE_STATUS_V
AS
SELECT
ADF.HIST_UNTIL,
ADF.SOURCE_TABLE
FROM
dbo.ADM_DATENFREIGABE AS ADF
JOIN
dbo.ADM_TAB_CHANGEDATES_V AS ATC
ON
ADF.SOURCE_TABLE = ATC.TABLE_NAME
WHERE
ADF.RELEASE_FOR_EXPORT = 1
AND ADF.HIST_UNTIL = ATC.LAST_CHANGE
GROUP BY
ADF.HIST_UNTIL,
ADF.SOURCE_TABLE;
Die Prozedur zur Umsetzung des Vier-Augen-Prinzips
Die nachfolgende Prozedur überprüft, ob es zwei freigegebene Einträge gibt, und historisiert diese anschließend Diese Prozedur muss in der Datenbank gespeichert werden, damit diese in der DMC genutzt werden kann:
Klicke hier, um den SQL-Code anzuzeigen
CREATE PROCEDURE dbo.vier_augen_prinzip
@SourceTableParam VARCHAR(50),
@HistEditorParam VARCHAR(100) = 'admin'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentProcessingTime DATETIME2(3) = SYSDATETIME();
DECLARE @entryCount INT;
-- Temporary Table
CREATE TABLE #AffectedOldEntries (
SOURCE_TABLE VARCHAR(30),
TARGET_SYSTEM VARCHAR(20),
HIST_BASE VARCHAR(100),
DMC_NAME VARCHAR(50),
HIST_FROM_PK DATETIME2(3),
NewHistUntil DATETIME2(3) NULL
);
--Collecting Data LAST_CHANGE → NewHistUntil
INSERT INTO #AffectedOldEntries
SELECT
df.SOURCE_TABLE,
df.TARGET_SYSTEM,
df.HIST_BASE,
df.DMC_NAME,
df.HIST_FROM,
tc.LAST_CHANGE
FROM dbo.ADM_DATENFREIGABE AS df
LEFT JOIN dbo.ADM_TAB_CHANGEDATES_V AS tc
ON df.SOURCE_TABLE = tc.TABLE_NAME
WHERE df.SOURCE_TABLE = @SourceTableParam
AND df.RELEASE_FOR_EXPORT = 1
AND df.HIST_UNTIL = '9999-12-31 00:00:00.000';
SELECT @entryCount = COUNT(*) FROM #AffectedOldEntries;
IF @entryCount = 2
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
-- Neuen Datensatz einfügen
INSERT INTO dbo.ADM_DATENFREIGABE (
SOURCE_TABLE, TARGET_SYSTEM, RELEASE_FOR_EXPORT,
DMC_NAME, HIST_FROM, HIST_UNTIL,
HIST_EDITOR, HIST_BASE, STATUS, EXPORT_ID
)
SELECT
SOURCE_TABLE,
TARGET_SYSTEM,
0,
DMC_NAME,
@CurrentProcessingTime,
'9999-12-31 00:00:00.000',
@HistEditorParam,
HIST_BASE,
NULL, NULL
FROM #AffectedOldEntries;
UPDATE ad
SET ad.HIST_UNTIL = CASE
WHEN ad.RELEASE_FOR_EXPORT = '1' THEN ae.NewHistUntil
ELSE ad.HIST_UNTIL
END
FROM dbo.ADM_DATENFREIGABE ad
JOIN #AffectedOldEntries ae
ON ad.SOURCE_TABLE = ae.SOURCE_TABLE
AND ad.HIST_BASE = ae.HIST_BASE
AND ad.HIST_UNTIL = '9999-12-31 00:00:00.000';
COMMIT TRANSACTION;
PRINT 'Prozedur erfolgreich ausgeführt. Betroffene Einträge wurden verarbeitet.';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
PRINT 'Fehler während der Prozedurausführung. Änderungen wurden zurückgerollt.';
THROW;
END CATCH
END
ELSE
BEGIN
PRINT 'Weniger oder mehr als zwei gültige Einträge gefunden. Keine Aktion durchgeführt.';
END
DROP TABLE IF EXISTS #AffectedOldEntries
SET NOCOUNT OFF
END;
GO
Prozedur zum Zurücksetzen der Freigaben
Die folgende Prozedur setzt eine Freigabe zurück, wenn es in einer Source Tabelle eine Änderung gab, welche aktueller als eine Freigabe ist.Diese Prozedur muss in der Datenbank gespeichert werden, damit diese in der DMC genutzt werden kann:
Klicke hier, um den SQL-Code anzuzeigen
CREATE PROCEDURE dbo.UpdateReleaseStatusBasedOnChanges
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE adm
SET
adm.release_for_export = '0'
FROM
dbo.adm_datenfreigabe AS adm
INNER JOIN
dbo.adm_tab_changedates_v AS tab ON adm.SOURCE_TABLE = tab.TABLE_NAME
WHERE
adm.hist_from < tab.Last_change -- Condition 1: hist_from is older than the last change
AND adm.release_for_export = '1' -- Condition 2: The record is currently marked for release
AND adm.hist_until = '9999-12-31 00:00:00.000'; -- Condition 3: The record is the currently valid (historically open) one
END TRY
BEGIN CATCH
PRINT 'Fehler während der Prozedurausführung. Es wurden keine Änderungen vorgenommen.';
THROW;
END CATCH
SET NOCOUNT OFF;
END;
GO
Implementierung der QS-Checks
Nun können in der DMC QS-Regeln für die Freigabetabelle angelegt werden. Es wird empfohlen, folgende drei QS-Regeln in der Freigabetabelle zu implementieren. Der Fehlertyp aller QS-Checks ist Fehler und der Regeltyp ist Datensatzebene
- Freigabe in der Vergangenheit: Die Daten wurden seit der letzten Freigabe nochmal bearbeitet:
RELEASE_FOR_EXPORT = 1
AND HIST_FROM < (SELECT LAST_CHANGE
FROM ADM_TAB_CHANGEDATES_V v_change
WHERE v_change.TABLE_NAME = SOURCE_TABLE)
- Vier Augen Prozess: Tabelle ADM_DATENFREIGABE - Verletzung des 4-Augen-Prozesses. Derselbe User darf nicht beide Freigaben durchführen:
EXISTS (
SELECT 1
FROM (
SELECT
source_table,
hist_editor,
release_for_export
FROM
dbo.ADM_DATENFREIGABE
WHERE
release_for_export = 1
AND HIST_UNTIL = CONVERT(DATETIME2(3), '31.12.9999', 104)
GROUP BY
source_table,
hist_editor,
release_for_export
HAVING
COUNT(*) > 1
) AS DuplikatKombinationen
WHERE
DuplikatKombinationen.source_table = source_table
AND DuplikatKombinationen.hist_editor = hist_editor
AND DuplikatKombinationen.release_for_export = release_for_export
)
- Show QS: Prüft die Datenfreigabe auf Status = ERROR und zeigt die QS-Markierung:
status = 'ERROR'
Einrichten der Commands
Nachdem die QS-Regeln angelegt wurden, muss der Command, welcher die vorher angelegte Prozedur durchführt, angelegt werden. Dazu soll die Commandverwaltung aufgerufen und wie folgt konfiguriert werden:
Tabelle: Freigabe
Name: Start Freigabeprozess
Command-Typ: DB-Prozedur
Befehl:
exec vier_augen_prinzip @SourceTableParam = 'Tabelle1';
exec vier_augen_prinzip @SourceTableParam = 'Tabelle2'; --Insert table names here that are referenced in ADM_TAB_CHANGEDATES_V
Anzahl der Sekunden, die Command auf Abschluss einer QS-Prüfung wartet: eigenständig aussuchen, 10 ist ein guter Richtwert
QS-Prüfung vor Ausführung: Bei Fehlern abbrechen
QS-Prüfung nach Ausführung: Keine Prüfung (Dies sollte geändert werden, falls QS-Checks nach der Ausführung aufgerufen werden sollen)
Nun muss die Prozedur zum Zurücksetzen der Freigabe als Command hinterlegt werden:
Tabelle: Freigabe
Name: Reset Freigabe
Command-Typ: DB-Prozedur
Befehl:
exec UpdateReleaseStatusBasedOnChanges;
Anzahl der Sekunden, die Command auf Abschluss einer QS-Prüfung wartet: eigenständig aussuchen, 10 ist ein guter Richtwert.
QS-Prüfung vor Ausführung: Keine Prüfung
QS-Prüfung nach Ausführung: Keine Prüfung (Dies sollte geändert werden, falls QS-Checks nach der Ausführung aufgerufen werden sollen)
Einrichten der Tasks
Nun muss auf die Tasks Verwalten navigiert werden. Hier werden zwei Tasks erstellt: einer, welcher die Prozedur ausführt und der andere führt die QS-Checks der Freigabetabelle aus.
Zunächst fügen wir die Prozedur hinzu. Der Task soll mit folgenden Parametern erstellt werden:
Typ: Command ausführen
Bezeichnung: Task Freigabeprozess
Optionen: Start Freigabeprozess
Nun muss der QS Check hinzugefügt werden mit folgenden Parametern:
Typ: QS-Regeln prüfen
Bezeichnung: QS-Checks Freigabe
Tabellen: Freigabe
Außerdem muss die Freigabenresetprozedur hinzugefügt werden:
Typ: Command ausführen
Bezeichnung: Task Freigabenreset
Optionen: Reset Freigabe
Einrichten der Trigger
Nun haben wir beide Tasks angelegt. Damit diese automatisch ausgeführt werden, müssen die Trigger konfiguriert werden. Um einen Trigger zu verwalten, muss bei dem Task auf die Uhr gedrückt werden, welche in der Spalte 'Trigger' zu sehen ist.
Der Trigger für die Task 'Task Freigabe' sollte wie folgt konfiguriert werden:
Typ: Zeitgesteuert
Bezeichnung: Trigger für Task Freigabe
Cron-Ausdruck: 0 */5 * * * * (damit wird der Cronjob alle 5 Minuten ausgeführt)
Der Trigger für die Task 'Task QS-Checks Freigabe' soll wie folgt konfiguriert werden:
Typ: Zeigesteuert
Bezeichnung: Trigger für Task QS-Checks Freigabe
Cron-Ausdruck: */30 * * * * * (damit wird der Cronjob alle 30 Sekunden ausgeführt)
Der Trigger für die Task 'Task Freigabenreset' soll wie folgt konfiguriert werden:
Typ: Zeigesteuert
Bezeichnung: Trigger für Task Freigabenreset
Cron-Ausdruck: */30 * * * * * (damit wird der Cronjob alle 30 Sekunden ausgeführt)
Einfügen der Einträge in die Freigabetabelle
Damit das Prinzip funktioniert, müssen zwei Einträge pro Tabelle, welche Freigegeben werden soll, in die Freigabetabelle hinzugefügt werden. Dafür müssen zwei Datensätze hinzugefügt werden. Dabei sollten die Eintäge wie folgt befüllt werden:
Source_table: Hier den Tabellennamen eintragen, welcher in der DB vergeben wurde.
Release_for_export: Nein
Dmc_name: Hier soll der Name der freizugebenden Tabelle eingetragen werden, welcher in der DMC vergeben wurde.
Bevor der Datensatz hinzugefügt wird, soll der Knopf 'Weiterer Datensatz (mit Werten)' genutzt werden. Damit werden gleich die zwei benötigten Datensätze erstellt, ohne die Maske erneut ausfüllen zu müssen.
Berechtigungskonzept
Damit nur berechtigte Personen Einträge in der Freigabetabelle sehen und bearbeiten können, kann der Administrator unter 'Berechtigung' über die 'Datenberechtigung' die Zugriffsberechtigung uaf Datensatzebene für bestimmte User einstellen. Weitere Informationen sind in der Dokumentation zu finden.
Veränderungen in den Tabellen nachvollziehen
Um Veränderungen in den Einträgen der Tabelle nachvollziehen zu können, kann die "Historie" genutzt werden.