Cómo descargar los datos de la base de datos de auditoría a AWS S3 y leerlos usando el servicio AWS Athena

El archivo de auditoría 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 al administrador de la instalación de DataSunrise eliminar datos auditados más antiguos para almacenarlos en el servicio AWS S3, proporcionando una forma mejor y más rentable de almacenar los datos caducados. Con el uso del Servicio de seguridad de AWS Athena Team y los auditores externos son capaces de examinar los datos históricos necesarios para las auditorías e investigaciones de incidentes. Además de eso, usar el archivado de auditoría permite a los clientes de DataSunrise mantener conjuntos de datos más grandes de eventos auditados sin almacenar todo en la única base de datos de almacenamiento de auditoría y experimentar los tiempos de informe aumentado. Además, usar S3 para datos fríos es una solución más rentable, 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 archivo de auditoría, el equipo de DataSunrise proporciona el script dedicado para las implementaciones de Linux que se puede ajustar para poner los datos eliminados en una ubicación S3 personalizable. Viene como parte del paquete de instalación predeterminado de DataSunrise, por lo que no necesitas descargarlo de ningún lugar adicionalmente.

Este artículo te guiará a través del proceso de configuración y la tarea de limpieza de auditoría, descargando los datos eliminados en la ubicación del cubo S3 de tu elección y configurando el entorno en AWS Athena para las pruebas forenses.

Configura una tarea de limpieza de datos de auditoría con opción de archivado de auditoría

1. Abre la interfaz web de DataSunrise y navega a Configuración → Tareas periódicas . Haz clic en el botón Nueva tarea y proporciona la información general como Nombre, Tipo de tarea (por ejemplo, Limpiar datos de auditoría), y selecciona el servidor para ejecutar la tarea en caso de que estés utilizando un clúster de nodos DataSunrise.


Imagen 1. Configuraciones generales


2. Establece las Opciones de archivo en la sección Limpiar datos de auditoría :
• Marca la opción Archivar datos eliminados antes de la limpieza.
• Especifica la ruta de la carpeta de archivo donde se debe almacenar temporalmente los datos de la auditoría antes de moverlos a S3.


Imagen 2. Tarea Periódica de Limpieza de Auditoría

• Especifica la ruta del script que sube los datos a AWS S3 usando el campo de entrada “Ejecutar comando después de archivar”. Ruta por defecto – /opt/datasunrise/scripts/aws/cf_upload_ds_audit_to_aws_s3.sh (requerido)

Nota: si estás ejecutando DataSunrise en AWS ECS Fargate, usa el ecs_upload_ds_audit_to_aws_s3.sh que se encuentra en el mismo directorio.

• Proporciona parámetros extras para el script para ajustar el comportamiento (ver a continuación para los argumentos de opción del script)


Imagen 3. Comandos extra para la Tarea de Limpieza de Auditoría

3. Ajusta la Frecuencia de tarea en la sección “Frecuencia de inicio”, puedes establecer cada cuánto debe ejecutarse la tarea (por ejemplo, diario, semanal, mensual) según las necesidades de la organización para la retención y archivo de datos de auditoría.
4. Guarda la tarea después de configurar todas las configuraciones necesarias.
5. Comienza la tarea de forma manual o automática, si el usuario configuró la tarea para comenzar manualmente, el usuario puede iniciarla seleccionando la tarea y haciendo clic en Iniciar ahora . Si se configura para comenzar según un horario, se ejecutará automáticamente a las horas especificadas.


Imagen 4. Iniciar la Tarea [/caption>

Después de realizar la tarea, una carpeta de archivo se creará en el sistema de archivos del servidor DataSunrise donde se ejecutó la tarea (en las distribuciones de 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 “Limpieza periódica de auditoría” para eliminar regularmente los datos de auditoría obsoletos. Esto asegura la eficiencia del servidor DataSunrise al prevenir el desbordamiento de almacenamiento con datos desactualizados.

El script de carga de datos de auditoría archivados

Para cargar la carpeta de archivo a un cubo AWS S3, utiliza el script proporcionado por DataSunrise ubicado en la carpeta / scripts/aws para cargar la carpeta de archivo en el cubo AWS S3. El script requiere que la CLI de AWS esté instalada en el servidor DataSunrise y que el perfil de instancia IAM apropiado esté adjunto, conteniendo los permisos para la ubicación del cubo S3 donde pretendes cargar los datos archivados. Si estás usando un DataSunrise alojado en Linux desplegado desde AWS Marketplace o la plantilla de CloudFormation del cuenta pública de GitHub, entonces solo necesitas asegurarte de que el perfil de instancia IAM asociado esté correctamente configurado.

Para personalizar el procedimiento, puedes aprovechar las siguientes banderas opcionales:

1. – -archive-folder: override the default folder on the DataSunrise Server to put the archived audit data files. By default the ds-audit-archive folder will be created at the /opt/datasunrise/ location.
2. –folder-in-bucket: proporciona tu propio prefijo para poner los datos auditados. Por defecto, el script descarga los datos al prefijo /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 para acceder al cubo S3 deseado. No requiere ninguna entrada.

Nota: El tamaño de la carpeta se supervisa durante la descarga de la auditoría, y cuando excede un cierto umbral, se ejecuta el comando. Si no se especifica ningún script, se produce un error cuando se excede el umbral. El umbral se establece usando el parámetro adicional “AuditArchiveFolderSizeLimit”, con un valor predeterminado de 1 GB. Un usuario puede pre-limpiar una carpeta de archivo utilizando la opción “Borrar una carpeta de archivo antes de archivar”.

Consideraciones

• El nombre del cubo para cargar los datos archivados debe proporcionarse sin el esquema s3://

Estructura de la carpeta de archivo de auditoría

La estructura de la carpeta de archivo donde DataSunrise almacena los datos de auditoría generalmente sigue un formato jerárquico que está organizado por fecha. Esta organización ayuda a gestionar los datos de manera eficiente y facilita la localización de registros de auditoría específicos en función de la fecha. Aquí hay un esquema general de cómo podría verse la estructura:

Plantilla de estructura de carpetas generalizadas

 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

Nota: Los archivos estarán en forma comprimida y necesitan ser descomprimidos para leer la información (por ejemplo, los usuarios pueden utilizar el comando gunzip).

Una vez que los datos de la auditoría se suben a S3, la estructura se conserva de la misma manera que se almacenó en el servidor DataSunrise:

[caption id="attachment_19654" align="alignnone" width="871"] Imagen 5. Datos en el cubo de Amazon S3

Utilizando AWS Athena para leer datos de archivo de auditoría desde S3

Una vez que los datos de la 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 futuro. Procede a AWS Athena en la consola de administración de AWS para configurar los objetos de base de datos y tabla externa para leer tus datos 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 cubo S3 es datasunrise-audit

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

-- La próxima consulta crea una base de datos en 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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La próxima consulta carga las particiones para poder consultar 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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false','skip.header.line.count'='1');

-- La próxima consulta carga las particiones para poder consultar datos.
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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La próxima consulta carga las particiones para poder consultar datos.
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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La próxima consulta carga las particiones para poder consultar datos.
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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La próxima consulta carga las particiones para poder consultar datos.
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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La próxima consulta carga las particiones para poder consultar datos.
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/' -- ruta a la carpeta S3
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

-- La próxima consulta carga particiones para poder consultar datos.
MSCK REPAIR TABLE datasunrise_audit.tbl_objects;

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

--ejecutando SELECT contra la tabla audit_archive con los filtros por año, mes y día
SELECT * FROM audit_archive WHERE year = '2024' and month = '05' and day = '16';
--seleccionando datos de varias tablas a través de 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;
--ejecutando SELECT simple consultando toda la tabla sin filtros aplicados
select * from audit_archive;

Imagen 6. Archivo de auditoría

Conclusión

Un largo periodo de retención de datos para datos sensibles como eventos auditados puede ser un verdadero desafío y una carga adicional en el presupuesto para mantener conjuntos de datos grandes dentro de los archivos de la base de datos. El archivado de auditoría de DataSunrise proporciona una solución eficiente y segura para mantener los datos antiguos legibles, descargando la capa de almacenamiento de la base de datos y permitiendo que nuestros clientes tengan una solución resiliente y rentable basada en los servicios AWS S3 Athena para mantener los datos antiguos dentro de su organización y accesibles para la auditoría y el cumplimiento.