Database Auditing: Techniques, Tools & Compliance

TAKEAWAYS:

• Database auditing records who accessed, modified, or deleted data and when, ensuring accountability and compliance.
• Audit trails (logs) must be tamper-proof, immutable, and time-stamped to be legally admissible.
• Automated tools (e.g., Oracle Audit Vault, SQL Server Audit) reduce manual effort but require proper configuration.
• Regulations (e.g., GDPR, PCI-DSS, Nepali IT Act) mandate auditing for sensitive data like financial records or medical history.
• Performance impact must be balanced: granular auditing slows queries but improves security.
• Real-world use: Banks audit loan transactions, eSewa logs user payments, and NEPSE tracks stock trades for compliance.

1. What is Database Auditing?

Database auditing is the continuous monitoring and recording of database activities to ensure:

• Security: Detect unauthorized access or fraud.
• Compliance: Meet legal/regulatory requirements (e.g., GDPR, PCI-DSS).
• Accountability: Track changes for troubleshooting or investigations.

Key Components of Auditing

2. Types of Database Auditing

Auditing can be classified based on scope and granularity:

A. By Scope

mindmap
  root((Database Auditing Types))
    Scope
      **Database-Level**
        Logs all activities across the entire DBMS.
        *Example*: Oracle Unified Auditing.
      **Schema-Level**
        Tracks events in a specific schema (e.g., `hr` schema).
        *Example*: SQL Server schema audits.
      **Object-Level**
        Monitors individual tables/views (e.g., `audit SELECT on employees`).
        *Example*: PostgreSQL `pgAudit`.
      **User-Level**
        Logs actions by specific users (e.g., `admin` or `guest`).
        *Example*: MySQL `general_log` for user queries.

B. By Granularity

3. How Auditing Works: Step-by-Step

1. Event Capture: The DBMS intercepts actions (e.g., INSERT, GRANT).
2. Filtering: Only events matching audit policies are logged (e.g., AUDIT ALL ON accounts).
3. Logging: Details (user, timestamp, SQL, affected rows) are written to an audit trail.
4. Storage: Logs are stored securely (often encrypted and compressed).
5. Review: Admins or tools analyze logs for anomalies (e.g., SELECT * FROM passwords at 3 AM).

Worked Example: Auditing a Bank Loan System

Scenario: A bank uses Oracle to track loan applications. They need to audit:

• Who approves/rejects loans?
• When were loan terms modified?
• Any unauthorized access to sensitive data.

Solution:

-- Enable unified auditing for loan tables
AUDIT ALL ON loan_applications BY ACCESS;
AUDIT ALL ON loan_terms BY USER;

Audit Trail Sample:

Real-World Tie-In:

• Nabil Bank audits loan modifications to comply with Nepal Rastra Bank (NRB) regulations.
• eSewa logs all payment transactions to prevent fraud (e.g., duplicate deductions).

4. Audit Tools and Technologies

5. Regulatory Requirements for Auditing

Databases handling sensitive data must comply with local and international laws:

Example Compliance Scenario:

• Daraz must audit:
  • Order cancellations (to prevent chargebacks).
  • Inventory updates (to detect theft).
  • Payment gateways (to catch fraudulent refunds).

6. Challenges and Best Practices

Challenges

• Performance Overhead: Fine-grained auditing can slow queries by 10–30%.
• Storage Costs: Logs grow exponentially (e.g., 1M transactions/day = ~1GB/day).
• False Positives: Legitimate actions (e.g., backup jobs) may trigger alerts.
• Tampering Risks: Audit logs can be deleted or altered if not secured.

Best Practices

1. Audit Only What’s Necessary:
   • Avoid auditing SELECT on non-sensitive tables (e.g., products in an e-commerce DB).
2. Secure Audit Logs:
   • Store logs in a write-once-read-many (WORM) storage (e.g., immutable AWS S3 buckets).
3. Automate Alerts:
   • Use tools like Splunk or ELK Stack to trigger alerts for suspicious patterns (e.g., multiple failed logins).
4. Regularly Review Logs:
   • Schedule monthly audits to check for anomalies (e.g., DROP TABLE at odd hours).
5. Combine with Other Controls:
   • Pair auditing with role-based access control (RBAC) and encryption.

7. Performance vs. Security Trade-offs

Worked Example: NEPSE Stock Trading Audit

• Requirement: Track all trades to prevent insider trading.
• Solution:
  • Audit all INSERT/UPDATE on trades table (fine-grained).
  • Store logs in a separate, encrypted database with WORM protection.
  • Trade-off: Query performance drops by 25%, but compliance is mandatory.

8. Hands-On: Configuring Auditing in SQL Server

Step 1: Enable Server Audit

-- Create an audit to log to a file
CREATE SERVER AUDIT NEPSE_Audit
TO FILE (FILEPATH = 'C:\Audits\NEPSE_Audit');
GO

Step 2: Audit a Specific Table

-- Audit all DML on the 'trades' table
CREATE SERVER AUDIT SPECIFICATION NEPSE_Audit_Spec
FOR SERVER AUDIT NEPSE_Audit
ADD (SELECT ON [dbo].[trades] BY public),
ADD (INSERT ON [dbo].[trades] BY public),
ADD (UPDATE ON [dbo].[trades] BY public);
GO

Step 3: Start the Audit

ALTER SERVER AUDIT NEPSE_Audit WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION NEPSE_Audit_Spec WITH (STATE = ON);
GO

Expected Output:

• Log file (NEPSE_Audit_*.sqlaudit) will record:

  2024-05-20 16:45:20, User: trader123, Action: INSERT, Table: trades, Stock: NEPSE:1, Quantity: 100
  

9. Real-World Applications

A. eSewa: Payment Transaction Auditing

• What’s Audited:
  • Every TRANSFER or PAYMENT SQL statement.
  • Failed transactions (e.g., insufficient balance).
• Tools Used: Custom PostgreSQL triggers + AWS CloudTrail.
• Why:
  • Compliance with Nepal Rastra Bank (NRB) for financial transactions.
  • Fraud detection (e.g., duplicate payments).

B. NEPSE: Stock Market Auditing

• What’s Audited:
  • All BUY/SELL orders, price changes, and admin modifications.
• Tools Used: Oracle Audit Vault + real-time alerts.
• Why:
  • Prevent insider trading (e.g., a broker modifying prices before selling).
  • Legal requirement under the Securities Board of Nepal Act.

C. Pathao: Ride Data Auditing

• What’s Audited:
  • Driver location updates (to prevent fake rides).
  • Payment processing (to catch refund fraud).
• Tools Used: MongoDB change streams + custom logging.
• Why:
  • GDPR compliance for user location data.
  • Internal fraud prevention (e.g., drivers inflating distances).

Exam Tip

1. Define Auditing Clearly:

   • Examiners often ask: "What is database auditing?" Answer with purpose (security/compliance), components (logs, triggers), and examples (Oracle Audit Vault).

2. Compare Tools:

   • Be ready to contrast Oracle Audit Vault (enterprise) vs. pgAudit (open-source) in terms of features and use cases.

3. Regulatory Focus:

   • For Nepali exams, emphasize IT Act 2006 and NRB guidelines for financial databases.
   • For global exams, mention GDPR (EU), PCI-DSS (payments), and HIPAA (healthcare).

4. Worked Examples:

   • Always tie answers to real scenarios (e.g., "How would you audit a bank’s loan system?").
   • Use SQL snippets (like the SQL Server example above) to show practical knowledge.

5. Trade-offs:

   • Expect questions on performance vs. security (e.g., "Why not audit every SELECT?").
   • Answer with granularity levels and best practices (e.g., "Audit only sensitive tables").

6. Diagrams:

   • Draw audit log formats (fields: timestamp, user, action, table).
   • Sketch compliance workflows (e.g., "How GDPR auditing integrates with a DB").

Final Note: Database auditing is not just logging—it’s a strategic layer of security that bridges technical implementation and legal compliance. Master the tools, regulations, and trade-offs, and you’ll ace this unit. For practice, audit a sample database (e.g., Sakila MySQL) and generate logs for a scenario like "a hacker tries to delete all customer records."