What is Database Audit?

Example: An employee keeps legacy access permissions after moving to a different department and later exports sensitive HR records. Without proper audit logging, the activity could remain unnoticed for a long time. With DataSunrise, the event would trigger a real-time alert and be logged with full session information to support investigation and response efforts.

Traditional security strategies tend to focus on external threats. In reality, internal risks from employees or contractors often remain unnoticed. Sensitive data can leak through messaging apps, cloud storage synchronization, or social platforms—channels that bypass standard DLP controls. In many situations, users expose data unintentionally by exporting reports to personal devices, syncing database backups to consumer cloud services, or sharing screenshots that contain confidential information. These actions are subtle and typically fall outside the visibility of perimeter-based defenses.

Conventional DLP tools are designed to track file transfers and email activity. However, the most critical data resides in databases, which operate over protocols these tools cannot interpret. As a result, risky behavior such as unauthorized queries, misuse of privileges, or large-scale data extraction via SQL often goes undetected. This gap highlights the need for database-focused security with full audit coverage. Dedicated monitoring solutions capture query-level activity, link actions to user identities, and identify abnormal access patterns—delivering a level of visibility that generic DLP systems simply cannot provide.

Building Database Audit Foundations

PostgreSQL Trigger: Establishing Basic Logging

Development teams can implement basic database logging with PostgreSQL triggers. This approach captures INSERT, UPDATE, and DELETE operations. It provides a practical foundation for comprehensive audit workflows:

-- PostgreSQL: Comprehensive audit trail for data operations
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  operation TEXT NOT NULL,
  user_name TEXT NOT NULL,
  table_name TEXT NOT NULL,
  old_row JSONB,
  new_row JSONB,
  executed_at TIMESTAMP DEFAULT current_timestamp,
  session_id TEXT,
  client_ip INET
);

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(operation, user_name, table_name, old_row, new_row, session_id, client_ip)
  VALUES (
    TG_OP,
    session_user,
    TG_TABLE_NAME,
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
    CASE WHEN TG_OP = 'INSERT' THEN row_to_json(NEW) ELSE row_to_json(NEW) END,
    current_setting('application_name', true),
    inet_client_addr()
  );
  
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply trigger to sensitive tables
CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

This approach works well for development environments and smaller deployments. However, enterprise-scale infrastructures usually require dedicated platforms like DataSunrise to provide centralized compliance management and advanced security controls.

Heads-up Full-fidelity logging on a 5 TB OLTP cluster can add ≈30 % I/O and 150 GB/day of storage. Scope audits to high-risk schemas or budget accordingly.

Enterprise Database Security and Monitoring

Real-Time Monitoring with DataSunrise

DataSunrise Security Suite performs deep packet inspection across multiple database platforms. Its activity monitoring engine tracks user behavior patterns, while audit logs can be exported to SIEM systems via Syslog for real-time correlation and alerting.

Beyond breach detection, audit logs help determine incident scope. Unusual query patterns, access spikes, or privilege abuse get flagged and traced. The Database Audit module uses pattern recognition to identify anomalies. It classifies them for detailed analysis.

Regulatory Compliance Through Audit Logging

SOX Compliance Through Audit Management

The Sarbanes-Oxley Act requires precise auditing of data access and privilege changes in financial systems. DataSunrise addresses these requirements by capturing:

• 1.1 Events from privileged users and unauthorized access attempts
• 1.2 Role escalations and access level changes
• 1.3 Failed authentication attempts and access denials

• 1.4 Database schema changes and data definition modifications
• 1.5 Unauthorized attempts to access protected financial data

PCI DSS Logging and Compliance

PCI DSS requires continuous audit logging for systems processing cardholder data. DataSunrise streamlines compliance with customizable rules and secure log storage.

• 10.1 Link each database operation to authenticated users

• 10.2 Automatically record access to sensitive tables and fields
• 10.3 Attach timestamps, usernames, source IPs, and result status

• 10.5 Ensure logs are securely stored and protected from tampering

• 10.6 Continuously monitor for suspicious activity in audit records

Important Note: Additional PCI subclauses may require organization-specific configurations beyond this overview.

Native Audit Cheatsheet (SQL Server & MySQL)

If you’re still running native-only, here are minimal, production-ready snippets to capture database activity history without third-party tooling.

SQL Server: Audit to File + Quick Read

-- Create a server audit writing to disk
CREATE SERVER AUDIT Audit_File
TO FILE (FILEPATH = 'C:\SQLAudits\', MAXSIZE = 1 GB, MAX_ROLLOVER_FILES = 20)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT Audit_File WITH (STATE = ON);

-- Track SELECT/WRITE on a critical DB (replace MyDB)
CREATE DATABASE AUDIT SPECIFICATION DbSpec_MyDB
FOR SERVER AUDIT Audit_File
    ADD (SELECT, INSERT, UPDATE, DELETE ON DATABASE::MyDB BY PUBLIC),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);

-- Read last hour of events
SELECT event_time, server_principal_name, database_name, statement
FROM sys.fn_get_audit_file('C:\SQLAudits\*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time > DATEADD(HOUR, -1, GETDATE())
ORDER BY event_time DESC;

MySQL 8: Enterprise Audit (JSON)

-- Enable the audit plugin (paths vary by distro)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- JSON format; log everything but you should scope in prod
SET PERSIST audit_log_format = JSON;
SET PERSIST audit_log_policy = ALL;

-- Optional: rotate & size controls
SET PERSIST audit_log_rotate_on_size = 104857600; -- 100 MB
SET PERSIST audit_log_file = 'audit.log';

-- Example: verify plugin status
SHOW PLUGINS LIKE 'audit%';

Native audit works—but every engine has different knobs, file formats, and blind spots. Correlation becomes a full-time job.

Tamper-Evident Audit Store (PostgreSQL Hash Chain)

Compliance loves immutability. This pattern writes a hash chain over your audit events so any tampering is obvious. Pair it with WORM storage (e.g., S3 Object Lock) for the belt-and-suspenders approach.

-- Requirements: pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Append-only audit table
CREATE TABLE audit_events (
  id           BIGSERIAL PRIMARY KEY,
  event_time   TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor        TEXT NOT NULL,
  action       TEXT NOT NULL,
  object       TEXT,
  ip           INET,
  payload      JSONB,
  prev_hash    BYTEA,        -- previous row hash
  row_hash     BYTEA         -- current row hash
);

-- Prevent updates/deletes
ALTER TABLE audit_events
  ALTER COLUMN actor SET NOT NULL,
  ALTER COLUMN action SET NOT NULL;
REVOKE UPDATE, DELETE ON audit_events FROM PUBLIC;

-- Insert wrapper to compute the hash chain
CREATE OR REPLACE FUNCTION audit_events_append()
RETURNS TRIGGER AS $$
DECLARE
  v_prev BYTEA;
BEGIN
  SELECT row_hash INTO v_prev
  FROM audit_events
  ORDER BY id DESC
  LIMIT 1;

  NEW.prev_hash := v_prev;
  NEW.row_hash  := digest(
      coalesce(NEW.actor,'') || '|' ||
      coalesce(NEW.action,'') || '|' ||
      coalesce(NEW.object,'') || '|' ||
      coalesce(NEW.ip::text,'') || '|' ||
      coalesce(NEW.payload::text,'') || '|' ||
      coalesce(NEW.event_time::text,'') || '|' ||
      encode(coalesce(NEW.prev_hash, '\\x'), 'hex'),
    'sha256');

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_chain
BEFORE INSERT ON audit_events
FOR EACH ROW EXECUTE FUNCTION audit_events_append();

-- Verification query: detect breaks in the chain
WITH ordered AS (
  SELECT id, row_hash, prev_hash,
         lag(row_hash) OVER (ORDER BY id) AS expected_prev
  FROM audit_events
)
SELECT *
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev;

Result set must be empty. Any row returned indicates tampering or a broken chain.

Key Components of a Modern Data Audit Architecture

Modern database auditing depends on a secure and scalable architecture capable of supporting real-time monitoring, centralized log management, and automated compliance reporting. Strong audit frameworks typically include the following core components:

• Audit Collection Layer: Captures SQL activity, schema modifications, and authentication events while remaining transparent to applications. Solutions such as DataSunrise provide centralized monitoring across Oracle, PostgreSQL, MySQL, and other database platforms.
• Event Standardization Engine: Processes raw audit records and transforms them into a unified format with normalized attributes such as user name, IP address, timestamp, session identifier, and accessed objects, regardless of the underlying database technology.
• Protected Audit Repository: Stores audit information in immutable and tamper-resistant formats. Common options include append-only PostgreSQL repositories, dedicated S3 storage, or secure integrations with SIEM platforms.
• Monitoring and Correlation Layer: Identifies unusual activity through predefined rules or machine learning models. For example, repeated attempts to access sensitive data by inactive accounts can automatically trigger alerts and investigation procedures.
• Reporting and Compliance Layer: Produces audit evidence in formats such as PDF, CSV, and JSON. These reports help organizations demonstrate compliance with regulations including SOX, GDPR, HIPAA, and PCI DSS.

These components enable organizations to detect breaches early, enforce least-privilege principles, and produce defensible logs during investigations. A modern data audit architecture doesn’t just record—it responds, correlates, and proves compliance.

Conclusion: Advancing Audit Visibility and Data Governance

DataSunrise brings together automated compliance controls, continuous activity monitoring, and centralized audit management within a single platform. Instead of depending on occasional manual reviews, organizations gain real-time visibility into database operations, including user activity, executed queries, configuration modifications, and authentication events. Each audit record contains rich contextual information such as user identity, originating IP address, application details, and SQL statements, making investigations more efficient and accurate.

In addition to comprehensive logging, the platform provides granular auditing capabilities that enable organizations to monitor specific users, database objects, schemas, or categories of activity. Security teams can define custom rules, configure alerts, automate response workflows, and use centralized dashboards to identify suspicious behavior and policy violations more quickly. This proactive approach improves operational oversight and strengthens overall governance.

Designed for modern enterprise environments, DataSunrise supports major database platforms including Oracle, Microsoft SQL Server, Amazon Redshift, PostgreSQL, and MySQL. The platform operates across both on-premises and cloud-based infrastructures, making it suitable for hybrid and multi-cloud deployments. Organizations can deploy the solution locally through the download package or obtain it through the AWS Marketplace and Microsoft Azure Marketplace for streamlined implementation.

By automating audit collection and policy enforcement, DataSunrise helps organizations satisfy regulatory obligations, accelerate incident investigations, and generate reliable evidence for audits and compliance reviews. Rather than serving as simple records of activity, audit logs become valuable sources of operational and security intelligence. As regulatory requirements and data environments continue to evolve, DataSunrise provides the visibility, control, and scalability needed to support long-term governance and data protection initiatives.