Database Auditing: Techniques, Tools & Compliance

TAKEAWAYS:

• Database auditing tracks user actions, access patterns, and data changes to ensure compliance and security.
• Audit trails must be tamper-proof, immutable, and stored separately from production databases.
• Regulatory frameworks like GDPR (EU), SOX (US), and Nepal’s Data Privacy Act mandate specific auditing requirements.
• Tools like Oracle Audit Vault, SQL Server Audit, and pgAudit automate logging and analysis.
• Performance overhead is a trade-off: auditing slows down queries but is critical for security.
• Real-world example: Banks like NMB Bank use auditing to detect fraudulent transactions in real-time.

What is Database Auditing?

Database auditing is the continuous monitoring and recording of database activities to ensure security, compliance, and accountability. It answers critical questions:

• Who accessed the data?
• What changes were made?
• When and from where were these actions performed?

Why is Auditing Important?

1. Security: Detects unauthorized access or malicious activities (e.g., SQL injection, data leaks).
2. Compliance: Meets legal/regulatory requirements (e.g., SOX for financial records, GDPR for personal data).
3. Forensics: Helps investigate breaches (e.g., Khalti’s 2021 data leak investigation).
4. Performance Insights: Identifies inefficient queries or bottlenecks.

Key Components of Database Auditing

1. Audit Trail

An immutable log of all database activities, including:

• User actions (login, query execution, DML operations).
• Schema changes (CREATE, ALTER, DROP).
• Privilege changes (GRANT, REVOKE).
• Failed login attempts (security alerts).

Example: If a user finance_admin runs UPDATE accounts SET balance = 0 WHERE user_id = 101, the audit trail records:

Timestamp: 2024-05-20 14:30:45
User: finance_admin
Action: UPDATE
Table: accounts
Query: UPDATE accounts SET balance = 0 WHERE user_id = 101
IP: 192.168.1.100

2. Audit Policies

Rules defining what to audit and how often. Common policies:

Real-World Tie-In: Nepal Rastra Bank (NRB) enforces strict auditing on all financial transactions to prevent money laundering. If an employee at NMB Bank tries to transfer ₹10M without approval, the audit trail flags it instantly.

How Auditing Works: Step-by-Step

1. Audit Trigger Mechanism

When an event occurs (e.g., a query execution), the DBMS:

1. Captures the event (who, what, when).
2. Logs it to an audit table (or external file).
3. Optionally triggers alerts (e.g., email for suspicious activity).

sequenceDiagram
    participant User
    participant DBMS
    participant AuditLog
    User->>DBMS: Executes: UPDATE accounts SET balance = 1000000
    DBMS->>AuditLog: Logs: {User: admin, Action: UPDATE, Table: accounts, Timestamp: ...}
    DBMS-->>User: Returns success
    Note right of AuditLog: Audit log stored in a separate, read-only table.

2. Storage of Audit Data

Audit logs must be:

• Immutable (cannot be altered).
• Secure (encrypted, access-controlled).
• Separate from production data (prevents tampering).

Best Practices:

• Store logs in a dedicated audit database (not the main DB).
• Use write-once-read-many (WORM) storage (e.g., AWS S3 with versioning).
• Retention policy: Keep logs for 7 years (GDPR requirement).

Tools for Database Auditing

Example: Khalti uses Oracle Audit Vault to monitor all payment transactions. If a user tries to reverse a ₹50,000 transaction after 30 days (against Khalti’s policy), the system flags it via audit logs.

Regulatory Compliance in Auditing

Different industries have mandatory auditing requirements:

Worked Example: Nepal Stock Exchange (NEPSE) must audit all trading activities to prevent insider trading. If a broker tries to buy ₹100M worth of shares before an announcement, the audit trail must capture:

• User ID: broker123
• Action: BUY 100000 shares of NMB
• Timestamp: 2024-05-20 15:30:00
• Alert: "Suspicious activity detected (pre-announcement trade)"

Challenges in Database Auditing

In the Real World

1. Khalti (Digital Payment System)

   • What it uses: Real-time transaction auditing with Oracle Audit Vault.
   • How it works: Every payment (e.g., ₹5,000 from User A to Merchant B) is logged with:
     • Sender/Receiver details
     • Transaction ID
     • Timestamp
     • IP address
   • Why? Detects fraud (e.g., duplicate payments, unauthorized reversals).

2. NMB Bank (Loan Processing)

   • What it uses: SQL Server Audit for loan approvals.
   • How it works: If a loan officer approves a ₹50L loan without proper documentation, the audit trail flags:
     • Officer ID: loan_officer_45
     • Action: UPDATE loans SET status = 'APPROVED' WHERE loan_id = 1001
     • Missing: document_verification = 'NO'
   • Outcome: Automated alert → manual review → fraud prevention.

3. Daraz (E-Commerce Order Fulfillment)

   • What it uses: PostgreSQL pgAudit for order processing.
   • How it works: If a warehouse manager cancels 100 orders without reason, the audit log shows:
     • User: warehouse_supervisor
     • Query: UPDATE orders SET status = 'CANCELLED' WHERE order_id IN (1001,1002,...)
     • Impact: Detects potential order manipulation for personal gain.

Worked Example: Auditing a Bank Loan System

Scenario: A bank’s loan processing system has tables:

• customers (customer_id, name, ssn)
• loans (loan_id, customer_id, amount, status)
• audit_log (log_id, user_id, action, table_name, timestamp)

Audit Policy:

• Log all INSERT, UPDATE, DELETE on loans.
• Alert if amount > ₹50L without manager approval.

Example Trace:

1. Loan Officer (user_id = 5) tries to approve a ₹60L loan:

   UPDATE loans SET status = 'APPROVED' WHERE loan_id = 2001;
   

2. Audit Log Entry:

   log_id: 1001
   user_id: 5
   action: UPDATE
   table_name: loans
   timestamp: 2024-05-20 16:00:00
   old_amount: 50000000
   new_amount: 60000000
   

3. Alert Triggered:
   • Rule: IF new_amount > 50000000 AND user_role != 'MANAGER' THEN ALERT.
   • Result: Email sent to compliance_team@bank.com:

     "Unauthorized loan approval detected: ₹60L by user_id=5 (Loan Officer)."

Visualization of the Audit Flow:

flowchart TD
    A["Loan Officer Executes UPDATE"] --> B["DBMS Checks Audit Policy"]
    B -->|"Amount > ₹50L?"| C{"No"}
    C -->|"Yes"| D["Trigger Alert"]
    C -->|"No"| E["Log to Audit Table"]
    D --> F["Email Compliance Team"]
    E --> G["Audit Log Stored Securely"]

Best Practices for Effective Auditing

1. Define Clear Audit Policies
   • Example: "Audit all DELETE operations on customer_data."
2. Use Automated Tools
   • Avoid manual logging (prone to errors).
3. Secure Audit Logs
   • Encrypt logs, restrict access to DBA + Compliance teams only.
4. Regularly Review Logs
   • Set up weekly reports for suspicious activities.
5. Test Audit Trails
   • Simulate attacks (e.g., SQL injection) to ensure logs capture everything.

Exam Tip

How This Unit is Examined (TU Pattern)

1. Short Questions (5 marks each)

   • Define audit trail, WORM storage, SOX compliance.
   • Example:

     "What is the difference between a standard audit and a privileged user audit?"

2. Long Questions (15-20 marks)

   • Scenario-based: Given a bank’s loan system, design an audit policy.
   • Tool comparison: Compare Oracle Audit Vault vs. SQL Server Audit.
   • Case study: "How would you audit Khalti’s payment system to detect fraud?"

3. Practical (Programming/Design)

   • Write SQL to create an audit table for a given schema.
   • Example:

     CREATE TABLE audit_log (
         log_id SERIAL PRIMARY KEY,
         user_id INT REFERENCES users(user_id),
         action VARCHAR(50),
         table_name VARCHAR(100),
         timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         old_value TEXT,
         new_value TEXT
     );
     

4. Common Pitfalls

   • ❌ Forgetting to mention immutability of audit logs.
   • ❌ Not linking auditing to real-world regulations (GDPR, SOX).
   • ❌ Ignoring performance trade-offs (e.g., auditing every query slows the system).

Pro Tip:

• Memorize the 4 Ws: Who, What, When, Where (every audit log must answer these).
• Relate to Nepal: Always connect answers to Nepal Rastra Bank, NEPSE, or Khalti for full marks.