Database Security & System Logs: Threats, Controls, and Audit Trails

TAKEAWAYS:

• Database security protects data from unauthorized access, corruption, and theft using authentication, encryption, and role-based access control (RBAC).
• System logs record all database operations (logins, updates, failures) for audit trails and recovery after crashes or breaches.
• Shadow paging is a recovery technique that maintains a backup copy of database pages to roll back to a consistent state.
• NoSQL databases (e.g., MongoDB) use different security models than SQL databases, often relying on schema-less access control and encryption at rest.
• Concurrent transactions require locking mechanisms (pessimistic vs. optimistic) to prevent dirty reads, lost updates, and deadlocks.
• Nepal’s Ncell and NEPSE use multi-factor authentication (MFA) and transaction logs to secure user data and financial records.

1. Introduction to Database Security

Databases store sensitive data (customer records, financial transactions, health records). Without security, attackers can:

• Steal data (e.g., Ncell customer call logs).
• Alter data (e.g., Daraz order quantities).
• Deny access (e.g., NEPSE trading system downtime).

Key Security Objectives

mindmap
  root((Database Security Goals))
    Security((Confidentiality))
      Security/Only authorized users access data.
    Integrity((Data Integrity))
      Integrity/Data must not be altered without permission.
    Availability((Availability))
      Availability/Data must be accessible when needed.
    Authentication((Authentication))
      Authentication/Verify user identity (e.g., passwords, biometrics).
    Authorization((Authorization))
      Authorization/Define user permissions (e.g., read-only vs. admin).

Example:

• Ncell uses SIM-based authentication to verify mobile users before granting access to their call logs.
• NEPSE enforces two-factor authentication (2FA) for trading accounts to prevent unauthorized trades.

2. Database Security Mechanisms

A. Authentication

Verifies user identity before granting access.

• Methods:
  • Passwords (weak if reused; e.g., eSewa requires strong passwords).
  • Biometrics (fingerprint, retina scan; used in Khalti for fraud prevention).
  • Tokens (one-time passwords sent via SMS; e.g., Ncell for account recovery).

Worked Example: A Daraz seller logs in using:

1. Username + password (basic auth).
2. SMS OTP (second factor).
3. Fingerprint (optional, for high-value accounts).

B. Authorization (Access Control)

Restricts what authenticated users can do.

• Role-Based Access Control (RBAC):
  • Admin: Full CRUD (Create, Read, Update, Delete) + user management.
  • Manager: Read + update own records.
  • User: Read-only access.
• Example in Nepal:
  • NEPSE brokers have read/write access to their portfolios.
  • Ncell customer support agents have read-only access to call logs.

Visualization:

C. Encryption

Protects data in transit (TLS) and at rest (AES).

• Types:
  • Symmetric Encryption (AES-256; used by Ncell for call data).
  • Asymmetric Encryption (RSA; used by NEPSE for secure API keys).
  • Column-Level Encryption (sensitive fields like credit cards in Khalti).

Example:

• eSewa encrypts transaction data before sending it to banks using TLS 1.3.

3. Database Security Threats

Prevention Tips:

• Use parameterized queries (never concatenate SQL strings).
• Implement firewalls and intrusion detection systems (IDS).
• Regularly audit logs for suspicious activity.

4. System Logs and Audit Trails

System logs record all database activities for:

• Audit trails (who did what, when).
• Recovery (undoing unauthorized changes).
• Compliance (meeting legal requirements like Nepal Rastra Bank rules).

Types of Log Entries

mindmap
  root((System Log Entries))
    Login((Login/Logout))
      Login/Username, timestamp, IP
      Login/Device fingerprint (optional)
    Query((SQL Operations))
      Query/SELECT, INSERT, UPDATE, DELETE
      Query/Parameters used (sanitized)
    Error((Failures))
      Error/Timeouts, deadlocks, syntax errors
      Error/Error codes (e.g., 1054: Unknown column)
    Admin((Administrative))
      Admin/User creation/deletion
      Admin/Backup/restore events
      Admin/Role changes (e.g., 'admin' → 'auditor')

Example Log Entry:

2024-05-20 14:30:45 | USER: admin123 | ACTION: GRANT UPDATE ON orders TO seller456 | IP: 192.168.1.100

Why Logs Matter

• Recovery: If a hacker alters order data in Daraz, logs help revert to the last good state.
• Forensics: Logs prove Ncell was breached via a weak password (not a system flaw).
• Compliance: Banks like NEPSE must log all trades for regulatory audits.

5. Database Recovery: Shadow Paging

Shadow paging is a write-ahead logging (WAL) technique where:

1. A shadow copy of the database is maintained.
2. All updates are first written to a log file.
3. Only after the log is committed does the system update the primary database.

How It Works:

sequenceDiagram
    participant User
    participant DBMS
    participant ShadowCopy
    participant LogFile

    User->>DBMS: Update Record (e.g., change Daraz order status)
    DBMS->>LogFile: Write update to log (before any change)
    DBMS->>ShadowCopy: Apply update to shadow copy
    DBMS->>DBMS: Verify log commit
    DBMS->>DBMS: Update primary database (if log is valid)

Example:

• If Ncell’s billing system crashes mid-update, shadow paging allows rolling back to the last consistent state.

6. NoSQL Security (Brief Comparison)

Example:

• MongoDB (used by Daraz for inventory) encrypts sensitive fields like customer addresses before storing them.

7. Concurrency Control and Security

When multiple users access the database simultaneously, concurrency control prevents:

• Dirty reads (reading uncommitted data).
• Lost updates (two users overwrite each other’s changes).
• Deadlocks (circular wait for locks).

Locking Mechanisms

Deadlock Example:

8. Real-World Applications in Nepal

1. Ncell: Mobile Data Security

• Problem: SIM card cloning steals user data.
• Solution:
  • MFA via SMS OTP for account recovery.
  • Encrypted call logs (AES-256).
  • System logs track all login attempts.

2. NEPSE: Trading System Security

• Problem: Unauthorized trades due to weak authentication.
• Solution:
  • 2FA (SMS + biometric) for broker logins.
  • Shadow paging to recover from crashes.
  • Audit logs for regulatory compliance.

3. Daraz: E-Commerce Data Protection

• Problem: Supplier data leaks.
• Solution:
  • Role-based access (suppliers can only edit their own listings).
  • Encrypted payment details (PCI-DSS compliant).
  • Concurrency control during peak sales (e.g., Diwali).

9. Exam Tips

1. For SQL Questions:

   • Always include GRANT/REVOKE statements for access control.
   • Example:

     CREATE USER 'seller'@'%' IDENTIFIED BY 'SecurePass123';
     GRANT SELECT, UPDATE ON orders TO 'seller'@'%';
     

2. For Logs & Recovery:

   • Explain shadow paging with a sequence diagram (like above).
   • Mention WAL (Write-Ahead Logging) as a prerequisite.

3. For NoSQL vs. SQL:

   • Compare authentication methods (RBAC vs. certificates).
   • Example:

     SQL	NoSQL
     Uses GRANT	Uses db.grantRolesToUser()

4. For Concurrency:

   • Draw a deadlock scenario (like the Mermaid diagram above).
   • Explain pessimistic vs. optimistic locking with real examples.

5. Common Pitfalls:

   • ❌ Forgetting to mention encryption in security answers.
   • ❌ Not linking real-world examples (Ncell, NEPSE, Daraz).
   • ❌ Overlooking audit logs in recovery questions.

Final Note: Security is not optional—it’s critical for databases handling money (NEPSE), personal data (Ncell), or transactions (Khalti). Always assume an attacker is watching, and design defenses accordingly.