Wie man die Überwachungsdatenbankdaten auf AWS S3 auslastet und sie mit dem AWS Athena Service liest

Audit Archiving ist eine optionale Funktion der Audit Database Cleanup Task in DataSunrise Database Security. Diese Funktion ermöglicht es einem DataSunrise-Installationsadministrator, ältere Auditing-Daten zu entfernen und sie im AWS S3-Dienst zu speichern, um eine bessere, kosteneffizientere Methode zur Speicherung der abgelaufenen Daten zu bieten. Mit Hilfe von AWS Athena Service können die Sicherheitsteams und externe Prüfer die historischen Daten untersuchen, die für Audits und Zwischenfalluntersuchungen benötigt werden. Darüber hinaus ermöglicht die Verwendung von Audit Archiving es DataSunrise-Kunden, größere Datensätze von geprüften Ereignissen zu verwalten, ohne alles in der einzigen Audit Storage Database zu speichern und die erhöhte Berichtsdauer zu erfahren. Außerdem ist die Verwendung von S3 für Cold Data eine kosteneffizientere Lösung, die helfen kann, das Budget für das Projekt zu optimieren, indem die Audit-Datenbankgröße unter Kontrolle gehalten wird.

Für Audit Archiving stellt das DataSunrise-Team das dedizierte Skript für Linux-Bereitstellungen zur Verfügung, das angepasst werden kann, um die entfernten Daten an einen anpassbaren S3-Ort zu verschieben. Es ist Teil des standardmäßigen DataSunrise-Installationspakets, so dass Sie es nicht zusätzlich irgendwo herunterladen müssen.

In diesem Artikel führen wir Sie durch den Prozess der Einrichtung und der Audit Cleanup-Aufgabe, das Entladen der entfernten Daten an den S3-Bucket-Ort Ihrer Wahl und das Einrichten der Umgebung in der AWS Athena für die Forensik.

Konfigurieren Sie eine Aufgabe zum Reinigen von Überwachungsdaten mit der Option Audit Archiving

1. Öffnen Sie die DataSunrise Web-UI und navigieren Sie zu Konfiguration → Periodische Aufgaben. Klicken Sie auf die Schaltfläche Neue Aufgabe und geben Sie die allgemeinen Informationen wie Name, Aufgabentyp (z. B. Clean Audit Data) ein, und wählen Sie den Server aus, auf dem die Aufgabe im Falle einer Cluster von DataSunrise-Knoten ausgeführt werden soll.


Bild 1. Allgemeine Einstellungen


2. Stellen Sie die Archivierungsoptionen im Abschnitt Auditdaten bereinigen ein:
• Aktivieren Sie die Option Archivierte Daten vor dem Bereinigen.
• Geben Sie den Archivierungsordnerpfad an, in dem die Auditing-Daten vor dem Verschieben auf S3 vorübergehend gespeichert werden sollen.


Bild 2. Periode für Reinigung der Auditdaten Aufgabe

• Geben Sie den Pfad zum Skript an, das die Daten auf AWS S3 hochlädt, im Eingabefeld “Befehl nach dem Archivieren ausführen”. Standardpfad – /opt/datasunrise/scripts/aws/cf_upload_ds_audit_to_aws_s3.sh (erforderlich)

Anmerkung: Wenn Sie DataSunrise auf AWS ECS Fargate ausführen, verwenden Sie stattdessen den ecs_upload_ds_audit_to_aws_s3.sh, der sich im gleichen Verzeichnis befindet.

• Geben Sie zusätzliche Parameter für das Skript ein, um das Verhalten anzupassen (siehe unten für die Option Skriptargumente)


Bild 3. Extra-Befehle für die Reinigung der Audit-Aufgabe

3. Passen Sie die Aufgabenhäufigkeit im Abschnitt “Startfrequenz” an. Sie können einstellen, wie oft die Aufgabe ausgeführt werden soll (z. B. täglich, wöchentlich, monatlich) basierend auf den Bedürfnissen der Organisation für die Aufbewahrung und Archivierung von Auditing-Daten.
4. Speichern Sie die Aufgabe nach der Konfiguration aller notwendigen Einstellungen.
5. Starten Sie die Aufgabe manuell oder automatisch. Wenn der Benutzer die Aufgabe so konfiguriert hat, dass sie manuell gestartet wird, kann der Benutzer sie starten, indem er die Aufgabe auswählt und auf Jetzt starten klickt. Wenn sie so eingestellt ist, dass sie nach einem Zeitplan startet, wird sie automatisch zu den angegebenen Zeiten ausgeführt.


Bild 4. Starten Sie die Aufgabe

6. Nachdem die Aufgabe ausgeführt wurde, wird ein Archivordner auf dem Dateisystem des DataSunrise-Servers erstellt, auf dem die Aufgabe ausgeführt wurde (bei Linux-Distributionen ist der Standardpfad /opt/datasunrise/).

Anmerkung: Die obigen Schritte können auch verwendet werden, um die Aufbewahrungsdauer der Überwachungsdaten in DataSunrise zu verwalten, Benutzer können die Funktion “Periodic Clean Audit” nutzen, um regelmäßig veraltete Überwachungsdaten zu entfernen. Dies stellt die Effizienz des DataSunrise-Servers sicher, indem es ein Überlaufen des Speichers mit veralteten Daten verhindert.

Das Skript zum Hochladen archivierter Überwachungsdaten

Zum Hochladen des Archivordners in einen AWS S3-Bucket verwenden Sie das von DataSunrise bereitgestellte Skript, das sich im Ordner /scripts/aws befindet, um den Archivordner in den AWS S3-Bucket hochzuladen. Das Skript erfordert die AWS CLI, die auf dem DataSunrise-Server installiert ist, und das entsprechend angehängte IAM Instance Profile, das die Berechtigungen für den S3-Bucket enthält, an den Sie die archivierten Daten hochladen möchten. Wenn Sie DataSunrise auf Linux hosten, das aus dem AWS Marketplace oder der CloudFormation-Vorlage vom öffentlichen GitHub-Konto bereitgestellt wurde, müssen Sie nur sicherstellen, dass das zugehörige IAM Instance Profile korrekt konfiguriert ist.

Um das Verfahren zu personalisieren, können Sie die folgenden optionalen Flags verwenden:

1. –archive-folder: Überschreiben Sie den Standardordner auf dem DataSunrise-Server, in dem die archivierten Überwachungsdaten abgelegt werden. Standardmäßig wird der Ordner ds-audit-archive im Pfad /opt/datasunrise/ erstellt.
2. –folder-in-bucket: Geben Sie Ihren eigenen Präfix an, um die geprüften Daten abzulegen. Standardmäßig lädt das Skript Daten in den /ds-audit-archive-Präfix hoch.
3. –predefined-credentials: Falls Sie DataSunrise außerhalb von AWS ausführen, benötigen Sie entweder eine Credentials-Datei oder das ACCESS/SECRET-Key-Paar für den IAM-Benutzer, der berechtigt ist, auf den gewünschten S3-Bucket zuzugreifen, um Daten auf S3 hochzuladen. Benötigt keine Eingaben.

Anmerkung: Die Ordnergröße wird während des Audit-Unload überwacht, und wenn sie einen bestimmten Schwellenwert überschreitet, wird der Befehl ausgeführt. Wenn kein Skript angegeben ist, tritt ein Fehler auf, wenn der Schwellenwert überschritten wird. Der Schwellenwert wird mit dem “AuditArchiveFolderSizeLimit” Zusätzlichen Parameter festgelegt, mit einem Standardwert von 1 GB. Ein Benutzer kann einen Archivordner vorab bereinigen, indem er die Option “Einen Archivordner vor dem Archivieren löschen” verwendet.

Überlegungen

• Der Bucket-Name, an den die archivierten Daten hochgeladen werden sollen, muss ohne das S3://-Schema bereitgestellt werden

Struktur des Audit-Archivordners

Die Struktur des Archivordners, in dem DataSunrise Audit-Daten speichert, folgt in der Regel einem hierarchischen Format, das nach Datum organisiert ist. Diese Organisation hilft bei der effizienten Datenverwaltung und erleichtert es, bestimmte Audit-Aufzeichnungen basierend auf dem Datum zu lokalisieren. Hier ist eine allgemeine Übersicht, wie die Struktur aussehen könnte:

Allgemeine Vorlage der Ordnerstruktur

Basisverzeichnis: /opt/datasunrise/ds-audit-archive/
	└── Jahr: {JJJJ}/
    		└── Monat: {MM}/
        			└── Tag: {TT}/
            				└── Audit-Dateien: audit_data_{JJJJ}-{MM}-{TT}.csv.gz

Anmerkung: Die Dateien sind in komprimierter Form und müssen entpackt werden, um die Informationen zu lesen (z. B. Benutzer können den Befehl gunzip verwenden).

Nachdem die Auditing-Daten auf S3 hochgeladen wurden, wird die Struktur auf die gleiche Weise wie auf dem DataSunrise-Server erhalten:

Bild 5. Daten im Amazon S3-Bucket

Nutzung von AWS Athena zum Lesen von Audit-Archivdaten aus S3

Sobald die Auditing-Daten auf S3 hochgeladen sind, können Sie das Audit-Datenbankschema in AWS Athena Service für weitere Analysen erstellen. Gehen Sie zu AWS Athena in AWS Management Console, um eine Datenbank und ExternalTable-Objekte einzurichten, um Ihre archivierten Daten zu lesen.

Erstellen von Audit-Archivtabellen in AWS Athena

Die SQL-Skripte setzen folgendes voraus für die LOCATION-Klausel der CREATE EXTERNAL TABLE-Abfragen:

• Der Name des S3-Buckets ist datasunrise-audit

Die DDL-SQL-Datei für AWS Athena Audit Archive Tabellen ist ebenfalls verfügbar in der DataSunrise-Distribution mit dem Standardpfad /opt/datasunrise/scripts/aws/aws-athena-create-audit-archive-tables.sql.

-- Die nächste Abfrage erstellt eine Datenbank in Athena
CREATE DATABASE IF NOT EXISTS datasunrise_audit;
---------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.audit_archive (
    operations__id STRING,
    operations__session_id STRING,
    operations__begin_time STRING,
    operations__end_time STRING,
    operations__type_name STRING,
    operations__sql_query STRING,
    operations__exec_count STRING,
    sessions__user_name STRING,
    sessions__db_name STRING,
    sessions__service_name STRING,
    sessions__os_user STRING,
    sessions__application STRING,
    sessions__begin_time STRING,
    sessions__end_time STRING,
    connections__client_host_name STRING,
    connections__client_port STRING,
    connections__server_port STRING,
    connections__sniffer_id STRING,
    connections__proxy_id STRING,
    connections__db_type_name STRING,
    connections__client_host STRING,
    connections__server_host STRING,
    connections__instance_id STRING,
    connections__instance_name STRING,
    operation_rules__rule_id STRING,
    operation_rules__rule_name STRING,
    operation_rules__chain STRING,
    operation_rules__action_type STRING,
    operation_exec__row_count STRING,
    operation_exec__error STRING,
    operation_exec__error_code STRING,
    operation_exec__error_text STRING,
    operation_group__query_str STRING,
    operations__operation_group_id STRING,
    operations__all_exec_have_err STRING,
    operations__total_affected_rows STRING,
    operations__duration STRING,
    operations__type_id STRING,
    connections__db_type_id STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/audit-archive/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.audit_archive;
---------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.sessions (
    partition_id STRING,
    id STRING,
    connection_id STRING,
    host_name STRING,
    user_name STRING,
    scheme STRING,
    application STRING,
    thread_id STRING,
    process_id STRING,
    begin_time STRING,
    end_time STRING,
    error_str STRING,
    params STRING,
    db_name STRING,
    service_name STRING,
    os_user STRING,
    external_user STRING,
    domain STRING,
    realm STRING,
    sql_state STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/sessions/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false','skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.sessions;
---------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.connections (
    partition_id STRING,
    id STRING,
    interface_id STRING,
    client_host STRING,
    client_port STRING,
    begin_time STRING,
    end_time STRING,
    client_host_name STRING,
    instance_id STRING,
    instance_name STRING,
    proxy_id STRING,
    sniffer_id STRING,
    server_host STRING,
    server_port STRING,
    db_type_id STRING,
    db_type_name STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/connections/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.connections;
--------------------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.operation_sub_query (
    operation_sub_query__operation_id STRING,
    operation_sub_query__session_id STRING,
    operation_sub_query__type_name STRING,
    operations__begin_time STRING,
    operation_sub_query__type_id STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/operation-sub-query/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.operation_sub_query;
---------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.session_rules (
    session_id STRING,
    rule_id STRING,
    rule_name STRING,
    chain STRING,
    action_type STRING,
    sessions__begin_time STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/session-rules/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.session_rules;
--------------------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.col_objects (
    operation_id STRING,
    session_id STRING,
    obj_id STRING,
    name STRING,
    tbl_id STRING,
    operations__begin_time STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/col-objects/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.col_objects;
---------------------------------------------------------------------
CREATE EXTERNAL TABLE IF NOT EXISTS datasunrise_audit.tbl_objects (
    tbl_objects__operation_id STRING,
    tbl_objects__session_id STRING,
    tbl_objects__obj_id STRING,
    tbl_objects__sch_id STRING,
    tbl_objects__db_id STRING,
    tbl_objects__tbl_name STRING,
    tbl_objects__sch_name STRING,
    tbl_objects__db_name STRING,
    operations__begin_time STRING
  )
  PARTITIONED BY (
    `year` STRING,
    `month` STRING,
    `day` STRING
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '\"',
    'escapeChar' = '\\'
  )
LOCATION 's3://datasunrise-audit/tbl-objects/' -- Pfad zum S3 Ordner
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- Die nächste Abfrage lädt Partitionen, um Daten abfragen zu können.
MSCK REPAIR TABLE datasunrise_audit.tbl_objects;

Abfrage der Daten in der AWS Athena Console mit Standard-SQL-Abfragen:

--SELECT gegen die Tabelle audit_archive ausführen mit den Filtern nach Jahr, Monat und Tag
SELECT * FROM audit_archive WHERE year = '2024' and month = '05' and day = '16';
--Daten aus mehreren Tabellen auswählen durch JOIN-Klausel
SELECT
      r.operations__type_name,
      s.operation_sub_query__type_name,
      r.operations__sql_query
    FROM audit_archive AS r
    JOIN operation_sub_query AS s
      ON
        r.operations__id = s.operation_sub_query__operation_id
        AND
        r.operations__session_id = s.operation_sub_query__session_id;
–-einfaches SELECT ausführen, das alle Tabellen abfragt, ohne dass Filter angewendet werden
select * from audit_archive;

Bild 6. Audit-Archiv

Schlussfolgerung

Eine lange Aufbewahrungsfrist für sensible Daten wie geprüfte Ereignisse kann eine echte Herausforderung und eine zusätzliche Belastung für das Budget bei der Verwaltung großer Datensätze innerhalb der Datenbankdateien sein. DataSunrise Audit Archiving bietet eine effiziente und sichere Lösung zur Aufbewahrung älterer Daten in lesbarer Form, entlastet die Datenbankspeicherschicht und ermöglicht unseren Kunden eine resiliente und kosteneffiziente Lösung basierend auf AWS S3 Athena Services, um die alten Daten in Ihrer Organisation zugänglich für Audits und Compliance aufzubewahren.