Come Delegare I Dati Dell’Audit Del Database In AWS S3 E Leggerli Usando Il Servizio AWS Athena

Archiviazione degli Audit è una funzione opzionale del Task di Pulizia dell’Audit del Database in DataSunrise Sicurezza nel Database. Questa funzione consente a un amministratore dell’installazione di DataSunrise di rimuovere i dati di audit più vecchi per archiviarli nel servizio AWS S3, fornendo un modo migliore e più economico di archiviare i dati scaduti. Con l’uso del Servizio AWS Athena, il Team di Sicurezza e gli Auditori Esterni possono esaminare i dati storici necessari per audit e indagini sugli incidenti. Oltre a ciò, l’uso dell’Archiviazione degli Audit consente ai clienti di DataSunrise di mantenere set di dati più grandi di eventi di audit senza immagazzinare tutto in un unico database di Archiviazione Audit e subire tempi di report aumentati. Inoltre, utilizzare S3 per i dati freddi è una soluzione più economica, che può aiutare a ottimizzare il bilancio del progetto mantenendo sotto controllo le Dimensioni del Database di Audit.

Per l’Archiviazione degli Audit, il team di DataSunrise fornisce uno script dedicato per distribuzioni Linux che può essere adattato per inserire i dati rimossi in una posizione S3 personalizzabile. Viene fornito come parte del pacchetto di installazione predefinito di DataSunrise, quindi non è necessario scaricarlo da altre fonti.

Questo articolo vi guiderà attraverso il processo di Configurazione e il Task di Pulizia dell’Audit, delegando i dati rimossi alla posizione del Bucket S3 di vostra scelta e impostando l’ambiente in AWS Athena per le indagini forensi.

Configurare un Task di Pulizia dei Dati di Audit con l’opzione di Archiviazione Audit

1. Aprire la Web-UI di DataSunrise e navigare su Configurazione → Task Periodici. Fare clic sul pulsante Nuovo Task e fornire le informazioni generali come Nome, Tipo di Task (es. Pulire i Dati di Audit) e selezionare il server su cui eseguire il task nel caso in cui si stia utilizzando un Cluster di nodi DataSunrise.


Immagine 1. Impostazioni Generali


2. Impostare le Opzioni di Archiviazione nella sezione Pulire i Dati di Audit:
• Selezionare l’opzione Archiviare i Dati Rimossi prima della Pulizia.
• Specificare il percorso della Cartella di Archiviazione dove i dati di audit devono essere temporaneamente archiviati prima di spostarli in S3.


Immagine 2. Task Periodico di Pulizia dei Dati di Audit

• Specificare il percorso dello script che carica i dati su AWS S3 utilizzando il campo di input “Esegui Comando Dopo l’Archiviazione”. Percorso predefinito – /opt/datasunrise/scripts/aws/cf_upload_ds_audit_to_aws_s3.sh (richiesto)

Nota: se si sta eseguendo DataSunrise su AWS ECS Fargate, utilizzare lo script ecs_upload_ds_audit_to_aws_s3.sh invece, situato nella stessa directory.

• Fornire parametri extra per lo script per regolare il comportamento (vedi sotto per gli argomenti dello script di opzione)


Immagine 3. Comandi Extra per il Task Pulizia Audit

3. Regolare la Frequenza del Task nella sezione “Frequenza di Avvio”, è possibile impostare la frequenza con cui il task deve essere eseguito (es. giornaliera, settimanale, mensile) in base alle esigenze dell’organizzazione per la conservazione e l’archiviazione dei dati di audit.
4. Salvare il task dopo aver configurato tutte le impostazioni necessarie.
5. Avviare il task manualmente o automaticamente. Se l’utente ha configurato il task per l’avvio manuale, può avviarlo selezionandolo e facendo clic su Avvia Ora. Se è impostato per partire in base a un programma, verrà eseguito automaticamente agli orari specificati.


Immagine 4. Avvia il Task

6. Dopo l’esecuzione del task, una cartella di archiviazione verrà creata nel filesystem del Server DataSunrise dove il task è stato eseguito (nelle distribuzioni Linux, il percorso predefinito sarà /opt/datasunrise/).

Nota: I passaggi sopra possono anche essere utilizzati per gestire la conservazione dei dati di audit in DataSunrise, gli utenti possono utilizzare la funzione “Pulizia Periodica dell’Audit” per rimuovere regolarmente i dati di audit obsoleti. Questo garantisce l’efficienza del server DataSunrise prevenendo l’esubero dello spazio di archiviazione con dati obsoleti.

Lo Script per il Caricamento dei Dati di Audit Archiviati

Per caricare la cartella di archiviazione in un bucket AWS S3 usare lo script fornito da DataSunrise situato nella cartella /scripts/aws per caricare la cartella di archiviazione nel bucket AWS S3. Lo script richiede che AWS CLI sia installato sul server DataSunrise e che sia allegato il giusto IAM Instance Profile contenente i permessi per la posizione del Bucket S3 a cui si intende caricare i dati archiviati. Se si utilizza un DataSunrise ospitato su Linux distribuito dal AWS Marketplace o dal Template CloudFormation dall’ Account GitHub pubblico, è sufficiente assicurarsi che l’associato IAM Instance Profile sia configurato correttamente.

Per personalizzare la procedura, è possibile utilizzare le seguenti bandiere opzionali:

1. –-archive-folder: sovrascrivere la cartella predefinita sul Server DataSunrise per posizionare i file di dati di audit archiviati. Di default, la cartella ds-audit-archive verrà creata nella posizione /opt/datasunrise/.
2. –folder-in-bucket: fornire il proprio prefisso per inserire i dati di audit. Di default, lo script scarica i dati nel prefisso /ds-audit-archive.
3. –predefined-credentials: Nel caso in cui si esegua DataSunrise fuori da AWS, per poter caricare i dati su S3, sarà necessario un file delle credenziali o la coppia di chiavi ACCESS/SECRET per l’IAM User autorizzato ad accedere al desiderato Bucket S3. Non richiede input.

Nota: La dimensione della cartella viene monitorata durante il caricamento dell’audit, e quando supera una certa soglia, il comando viene eseguito. Se non viene specificato alcun script, si verifica un errore quando la soglia viene superata. La soglia è impostata utilizzando il Parametro Aggiuntivo “AuditArchiveFolderSizeLimit”, con un valore predefinito di 1 GB. Un utente può pre-pulire una cartella di archiviazione utilizzando l’opzione “Pulisci una cartella di archiviazione prima dell’archiviazione”.

Considerazioni

• Il nome del bucket per caricare i dati archiviati deve essere fornito senza lo schema s3://

Struttura della Cartella di Archiviazione degli Audit

La struttura della cartella di archiviazione dove DataSunrise archivia i dati di audit solitamente segue un formato gerarchico organizzato per data. Questa organizzazione aiuta a gestire i dati in modo efficace e rende più facile la localizzazione di specifici record di audit in base alla data. Ecco un modello generale della struttura che potrebbe apparire:

Modello Generale della Struttura della Cartella

Directory Base: /opt/datasunrise/ds-audit-archive/
	└── Anno: {YYYY}/
    		└── Mese: {MM}/
        			└── Giorno: {DD}/
            				└── File di Audit: audit_data_{YYYY}-{MM}-{DD}.csv.gz

Nota: I file saranno in forma compressa e sarà necessario decomprimerli per leggere le informazioni (es. gli utenti possono utilizzare il comando gunzip).

Una volta che i dati di audit sono caricati su S3, la struttura viene mantenuta esattamente come era archiviata sul Server DataSunrise:

Immagine 5. Dati nel Amazon S3 Bucket

Utilizzo di AWS Athena per leggere i Dati di Archive Audit Da S3

Una volta che i dati di audit sono caricati su S3, è possibile creare lo schema del Audit del Database nel Servizio AWS Athena per ulteriori analisi. Procedere su AWS Athena nel Console di Gestione AWS per impostare un Database e oggetti di Tabella Esterna per leggere i dati archiviati.

Crea le tabelle del Archive Audit in AWS Athena

Gli script SQL presuppongono quanto segue per la clausola LOCATION delle query CREATE EXTERNAL TABLE:

• Il nome del Bucket S3 è datasunrise-audit

Il file SQL DDL per le tabelle del Archive Audit di AWS Athena è disponibile anche nella distribuzione DataSunrise con il percorso predefinito /opt/datasunrise/scripts/aws/aws-athena-create-audit-archive-tables.sql.

-- La seguente query crea un database 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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false','skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
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/' -- percorso fino alla cartella S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La seguente query carica le partizioni per poter interrogare i dati.
MSCK REPAIR TABLE datasunrise_audit.tbl_objects;

Interrogare i dati nella Console di AWS Athena usando le query SQL standard:

-- eseguire SELECT contro la tabella audit_archive con i filtri per anno, mese e giorno
SELECT * FROM audit_archive WHERE year = '2024' and month = '05' and day = '16';
-- selezionare dati da più tabelle tramite la clausola JOIN
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;
–- eseguire un semplice SELECT interpellando tutte le tabelle senza filtri applicati
select * from audit_archive;

Immagine 6. Archivio Audit

Conclusione

Un lungo periodo di conservazione dei dati sensibili come gli eventi di Audit può diventare una vera sfida e un ulteriore carico sul bilancio per la manutenzione di grandi set di dati all’interno dei file del Database. L’Archiviazione degli Audit di DataSunrise fornisce una soluzione efficiente e sicura per mantenere i dati più vecchi leggibili, alleggerire lo strato di Archiviazione del Database e offrire ai nostri clienti una soluzione resiliente ed economica basata su Amazon AWS S3 Athena Services per mantenere i dati vecchi all’interno della vostra Organizzazione e accessibili per audit e conformità.