Database Audit Logging

Overview

This integration provides a unified approach to database audit logging across MySQL, Microsoft SQL Server, and PostgreSQL. Database audit logging is a legal requirement under Korean personal data protection law (PIPA) and ISMS-P sections 2.5.4 and 2.5.6. KYRA MDR collects and monitors all five mandatory access record fields required for compliance.

Mandatory access record fields (PIPA): User ID, access timestamp, source IP, personal data accessed, operation performed (CRUD)

---

Prerequisites

• A KYRA MDR Collector installed and running (Installation Guide)
• DBA-level access on the database server
• Network connectivity from the database server to the collector on port 514

---

Configuration

MySQL / MariaDB

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_handler = 'SYSLOG';

For MariaDB, use the built-in server_audit plugin with server_audit_output_type = 'SYSLOG'.

Microsoft SQL Server

CREATE SERVER AUDIT KyraAudit TO FILE (FILEPATH = 'C:\AuditLogs\');
ALTER SERVER AUDIT KyraAudit WITH (STATE = ON);

CREATE DATABASE AUDIT SPECIFICATION KyraDBSpec
    FOR SERVER AUDIT KyraAudit
    ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public);
ALTER DATABASE AUDIT SPECIFICATION KyraDBSpec WITH (STATE = ON);

Forward audit files to syslog using NXLog or a SQL Server Agent Job.

PostgreSQL

CREATE EXTENSION pgaudit;
SET pgaudit.log = 'read, write, ddl';

Enable syslog output in postgresql.conf:

log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

---

Collected Log Types

Log Type	Security Use	Priority
Authentication success/failure	Brute force detection	High
DML queries (SELECT, INSERT, UPDATE, DELETE)	Personal data access auditing (ISMS-P)	Critical
DDL queries (CREATE, ALTER, DROP)	Schema change monitoring	High
Privilege changes (GRANT, REVOKE)	Privilege escalation detection	Critical
Connection events	Access pattern analysis	Medium
Failed operations	Unauthorized access attempts	High

---

Data Retention Requirements

Category	Minimum Retention
General	1 year
Large-scale (50,000+ personal records)	2 years

---

Troubleshooting

No Audit Logs

1. Verify the audit plugin or extension is installed and active
2. Confirm audit policies cover the target schemas and tables
3. Check syslog forwarding configuration on the database server
4. Ensure the collector is receiving data on port 514

Performance Considerations

• Full query auditing can impact database performance; audit only tables containing personal data
• Use targeted audit policies rather than auditing all operations on all tables

For additional help, contact kyra@seekerslab.com.