Cómo Descargar los Datos de la Base de Datos de Auditoría a AWS S3 y Leerlos Usando el Servicio AWS Athena

Archivado de Auditorías es una función opcional de la Tarea de Limpieza de la Base de Datos de Auditoría en DataSunrise Database Security. Esta función permite a un administrador de la instalación de DataSunrise eliminar datos auditados antiguos y almacenarlos en el servicio AWS S3 para proporcionar una forma de almacenamiento de datos caducados más rentable y eficiente. Con el uso del Servicio AWS Athena, el Equipo de Seguridad y Auditores Externos pueden examinar los datos históricos necesarios para auditorías e investigaciones de incidentes. Además, el uso de Archivado de Auditorías permite a los clientes de DataSunrise mantener conjuntos de datos más grandes de eventos auditados sin tener que almacenarlo todo en una única base de datos de Almacenamiento de Auditoría y evitando tiempos de reporte incrementados. Asimismo, utilizar S3 para datos poco utilizados es una solución más económica, que puede ayudar a optimizar el presupuesto del proyecto manteniendo el tamaño de la Base de Datos de Auditoría bajo control.

Para el Archivado de Auditorías, el equipo de DataSunrise proporciona el script dedicado para implementaciones en Linux, el cual puede ajustarse para colocar los datos eliminados en una ubicación S3 personalizable. Este se incluye como parte del paquete de instalación predeterminado de DataSunrise, por lo que no es necesario descargarlo adicionalmente.

Este artículo te guiará a través del proceso de configurar la tarea de Limpieza de Auditoría, descargar los datos eliminados a la ubicación del Bucket S3 de tu elección y configurar el entorno en AWS Athena para el análisis forense.

Configurar una Tarea de Limpieza de Datos de Auditoría con la Opción de Archivado de Auditorías

1. Abre la interfaz web de DataSunrise y navega a Configuration → Periodic Tasks. Haz clic en el botón New Task y proporciona la información general como Nombre, Tipo de Tarea (por ejemplo, Clean Audit Data), y selecciona el servidor en el que se ejecutará la tarea en caso de que estés utilizando un Clúster de nodos DataSunrise.


Imagen 1. Configuración General


2. Configura las Opciones de Archivo en la sección Clean Audit Data:
   • Marca la opción Archivo de Datos Eliminados antes de la Limpieza.
   • Especifica la ruta de la Carpeta de Archivo donde se deben almacenar temporalmente los datos de auditoría antes de moverlos a S3.
   • Especifica la ruta al script que sube los datos a AWS S3 usando el campo “Execute Command After Archiving”. Ruta predeterminada – /opt/datasunrise/scripts/aws/cf_upload_ds_audit_to_aws_s3.sh <tu nombre de bucket S3> (requerido)

   Nota: si estás ejecutando DataSunrise en AWS ECS Fargate, utiliza el script ecs_upload_ds_audit_to_aws_s3.sh en su lugar, ubicado en el mismo directorio.

   Nota: puedes proporcionar rutas relativas a la carpeta de instalación de DataSunrise (por ejemplo, ./scripts/aws/cf_upload_ds_audit_to_aws_s3.sh <bucket_name> –[banderas opcionales]).

   Nota: si proporcionas el nombre predeterminado de la Carpeta de Archivo que el script espera (por ejemplo, /ds-audit-folder), entonces no necesitas especificarlo en los argumentos del script.

   • Proporciona parámetros adicionales para el script para ajustar el comportamiento (ver abajo para los argumentos opcionales del script).
   

   Imagen 2. Comandos Extra para la Tarea de Limpieza de Auditoría

3. Ajusta la Frecuencia de la Tarea en la sección “Startup Frequency”. Puedes definir con qué frecuencia debe ejecutarse la tarea (por ejemplo, diariamente, semanalmente, mensualmente) según las necesidades de la organización para la retención y el archivado de los datos de auditoría.
4. Guarda la tarea después de configurar todos los ajustes necesarios.
5. Inicia la tarea manualmente o de forma automática. Si el usuario configuró la tarea para que se inicie manualmente, puede iniciarla seleccionándola y haciendo clic en Start Now. Si está configurada para iniciarse según un horario, se ejecutará automáticamente en los momentos especificados.


Imagen 3. Iniciar la Tarea

6. Después de ejecutar la tarea, se creará una carpeta de archivo en el sistema de archivos del Servidor DataSunrise donde se ejecutó la tarea (en distribuciones Linux, la ruta predeterminada será /opt/datasunrise/).

Nota: Los pasos anteriores también se pueden utilizar para gestionar la retención de datos de auditoría en DataSunrise. Los usuarios pueden utilizar la función “Periodic Clean Audit” para eliminar regularmente los datos de auditoría desactualizados. Esto garantiza la eficiencia del servidor DataSunrise al evitar el desbordamiento del almacenamiento con datos obsoletos.

El Script de Subida de Datos de Auditoría Archivados

Para subir la carpeta de archivo a un bucket de AWS S3, utiliza el script proporcionado por DataSunrise ubicado en la carpeta <instalación de datasunrise>/scripts/aws, el cual se encarga de subir la carpeta de archivo al bucket de AWS S3. El script requiere que AWS CLI esté instalado en el servidor de DataSunrise y que el Perfil de Instancia IAM adecuado, con los permisos para la ubicación del bucket S3 en la que deseas subir los datos archivados, esté adjunto. Si estás utilizando una implementación de DataSunrise en Linux, desplegada desde AWS Marketplace o mediante la Plantilla CloudFormation de la Cuenta de GitHub pública, solo necesitas asegurarte de que el Perfil de Instancia IAM asociado esté configurado correctamente.

Para definir un bucket al que enviar los datos de auditoría archivados, proporciona el nombre de tu bucket S3 de AWS sin el prefijo s3://:

./scripts/aws/cf_upload_ds_audit_to_aws_s3.sh datasunrise-audit-archive

Para personalizar el proceso de archivado, puedes usar las siguientes banderas opcionales:

1. –-archive-folder: proporciona tu propia ruta en el Servidor DataSunrise para colocar los archivos de datos de auditoría archivados. Por defecto, la carpeta ds-audit-archive se creará en la ubicación /opt/datasunrise/. No es necesario proporcionar esta opción si defines la carpeta de Archivo de Auditoría como ds-audit-archive dentro del directorio raíz de instalación de DataSunrise.
2. –folder-in-bucket: proporciona tu propio prefijo para colocar los datos auditados. Por defecto, el script descarga los datos en el prefijo <EC2-instance-id>/ds-audit-archive.
3. –predefined-credentials: en caso de que estés ejecutando DataSunrise fuera de AWS, para poder subir datos a S3 necesitarás un archivo de credenciales o el par de claves ACCESS/SECRET para el Usuario IAM autorizado a acceder al bucket S3 deseado. No requiere entradas adicionales.

Nota: El tamaño de la carpeta se monitoriza durante la descarga de auditoría, y cuando supera cierto umbral se ejecuta el comando. Si no se especifica ningún script, se produce un error al superar el umbral. El umbral se establece utilizando el parámetro adicional “AuditArchiveFolderSizeLimit”, con un valor predeterminado de 1 GB. El usuario puede limpiar previamente una carpeta de archivo utilizando la opción “Clear an archive folder before archiving”.

Estructura de la Carpeta de Archivo de Auditoría

La estructura de la carpeta de archivo donde DataSunrise almacena los datos de auditoría sigue generalmente un formato jerárquico organizado por fecha. Esta organización ayuda a gestionar los datos de forma eficiente y facilita la localización de registros de auditoría específicos según la fecha. A continuación, se presenta un esquema general de cómo podría estructurarse:

Estructura Generalizada de la Carpeta (Plantilla)

Directorio Base: /opt/datasunrise/ds-audit-archive/
	└── Año: {YYYY}/
    		└── Mes: {MM}/
        			└── Día: {DD}/
            				└── Archivos de Auditoría: audit_data_{YYYY}-{MM}-{DD}.csv.gz

Una vez que los datos de auditoría se suben a S3, la estructura se conserva de la misma forma en que se organiza en el Servidor DataSunrise:

Imagen 4. Datos en el Bucket de Amazon S3

Usar AWS Athena para leer Datos de Archivo de Auditoría desde S3

Una vez que los datos de auditoría se suben a S3, puedes crear el esquema de la Base de Datos de Auditoría en el Servicio AWS Athena para un análisis posterior. Procede a AWS Athena en la Consola de Administración de AWS para configurar una Base de Datos y Tablas que te permitan leer tus datos archivados. Para poder utilizar completamente el servicio AWS Athena, recomendamos utilizar la Política Administrada IAM AmazonAthenaFullAccess para el Usuario IAM creado para trabajar con los datos de auditoría archivados.

Crear Tablas de Archivo de Auditoría en AWS Athena

Los scripts SQL asumen lo siguiente para la cláusula LOCATION de las consultas CREATE EXTERNAL TABLE:

– El nombre del Bucket S3 es datasunrise-audit

El archivo DDL SQL para las tablas de Archivo de Auditoría en AWS Athena también está disponible en la distribución de DataSunrise con la ruta predeterminada: /opt/datasunrise/scripts/aws/aws-athena-create-audit-archive-tables.sql.

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/'
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');
-- La siguiente consulta carga las particiones para poder consultar los datos.
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/'
TBLPROPERTIES ('has_encrypted_data'='false','skip.header.line.count'='1');
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/'
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

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/'
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

MSCK REPAIR TABLE datasunrise_audit.operation_sub_query;
----------------------------------------------------------------------------
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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

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/'
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

MSCK REPAIR TABLE datasunrise_audit.tbl_objects;

Consulta los datos en la Consola de AWS Athena utilizando consultas SQL estándar:

-- Ejecutar SELECT contra la tabla audit_archive con filtros por año, mes y día
SELECT * FROM audit_archive WHERE year = '2024' and month = '05' and day = '16';
-- Seleccionar datos de múltiples tablas mediante la cláusula 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;
-- Extraer 100 filas sin aplicar filtros
select * from audit_archive LIMIT 100;

Imagen 5. Archivo de Auditoría

Conclusión

Un largo período de retención de datos sensibles, como los eventos auditados, puede representar un verdadero desafío y una carga adicional para el presupuesto dedicado a mantener grandes conjuntos de datos en los archivos de la Base de Datos. El Archivado de Auditoría de DataSunrise ofrece una solución eficiente y segura para mantener accesibles los datos antiguos, descargar la capa de Almacenamiento de la Base de Datos y proporcionar a nuestros clientes una solución resiliente y rentable basada en los Servicios AWS S3 y Athena para conservar los datos históricos en su organización y mantenerlos accesibles para auditorías y cumplimiento normativo.