Database Security & Admin: Roles, Threats, Controls & Backup

TAKEAWAYS:

• Database security involves authentication, authorization, encryption, and auditing to prevent unauthorized access and data leaks.
• A Database Administrator (DBA) manages security policies, performance tuning, and disaster recovery, acting as the guardian of data integrity.
• Backup and recovery strategies (full, incremental, differential) ensure minimal data loss during failures or cyberattacks.
• SQL injection and malicious insiders are common threats; defense mechanisms include input validation and role-based access control (RBAC).
• Encryption (at rest and in transit) and firewalls are critical for protecting sensitive data like customer records in eSewa or Ncell.
• Disaster recovery plans (DRPs) outline step-by-step procedures to restore databases after cyberattacks or hardware failures.

1. Introduction to Database Security

Databases store critical information—customer transactions (eSewa, Khalti), employee records (NTC, Ncell), and financial data (NEPSE). Without security, this data is vulnerable to theft, corruption, or unauthorized modification. Database security ensures:

• Confidentiality: Only authorized users access data.
• Integrity: Data remains accurate and unaltered.
• Availability: Data is accessible when needed.

Key Security Objectives

mindmap
  root((Database Security Objectives))
    Confidentiality
      Authentication
      Authorization
    Integrity
      Checksums
      Cryptographic hashes
    Availability
      Redundancy
      Backup strategies

2. Database Security Threats

Threats can come from external hackers (e.g., SQL injection) or internal employees (e.g., malicious insiders). Common threats include:

Worked Example: SQL Injection Attack Suppose a login form in a university database has weak validation. An attacker submits:

' OR '1'='1'

Instead of authenticating, the query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'anything'

Result: All users are logged in without credentials.

3. Database Security Mechanisms

To counter threats, databases use:

A. Authentication & Authorization

• Authentication: Verifies user identity (e.g., username/password, biometrics).
• Authorization: Grants permissions (e.g., read-only vs. full access).

  stateDiagram-v2
    [*] --> User: Logs in
    User --> Auth: Submits credentials
    Auth --> Valid: Checks database
    Valid --> Grant: Assigns roles (e.g., "DBA", "Customer")
    Grant --> [*]

B. Encryption

• At Rest: Data encrypted when stored (e.g., Ncell’s customer data).
• In Transit: Encrypted during transfer (e.g., HTTPS for eSewa transactions).

  sequenceDiagram
    participant Client
    participant Server
    Client->>Server: Sends data (unencrypted)
    Server-->>Client: Returns encrypted response (TLS/SSL)

C. Firewalls & Intrusion Detection

• Firewalls: Block unauthorized network access.
• ID Systems: Monitor for suspicious activity (e.g., repeated failed logins).

D. Auditing & Logging

• Tracks who accessed data and when (e.g., NEPSE logs trades for compliance).

4. Database Administration (DBA) Roles

The Database Administrator (DBA) ensures security, performance, and reliability. Key responsibilities:

Worked Example: DBA’s Backup Strategy for Ncell Ncell stores millions of customer records. The DBA implements:

• Full backup: Weekly (Sunday nights).
• Incremental backup: Daily (only new/changed data).
• Disaster recovery test: Quarterly to ensure quick recovery.

5. Backup and Recovery Strategies

Backups protect against hardware failures, human errors, and cyberattacks. Types:

Recovery Process:

1. Identify failure (e.g., server crash).
2. Restore latest backup.
3. Apply incremental changes (if any).
4. Test restored data.

6. Database Security Policies

Organizations like NTC, Ncell, and eSewa enforce policies:

• Least Privilege: Users get only necessary access (e.g., a cashier in a bank cannot alter loan records).
• Regular Audits: Check for unauthorized access (e.g., NEPSE audits trade logs).
• Employee Training: Teach staff to spot phishing (e.g., Pathao drivers avoiding fake payment links).

7. Real-World Applications

In the Real World

1. eSewa’s Transaction Security

   • Uses TLS encryption for secure payments and tokenization to mask card details.
   • DBA role: Ensures no one accesses raw payment data without authorization.

2. Ncell’s Customer Data Protection

   • Role-Based Access Control (RBAC): Only billing teams access call records.
   • Regular backups: Stored in geographically separate data centers.

3. NEPSE’s Trade Data Integrity

   • Audit logs: Track every stock trade for compliance.
   • Disaster recovery: If servers fail, trades resume within 15 minutes.

8. Exam Tip

• Focus on definitions: Know DBA roles, backup types, and security threats (e.g., SQL injection).
• Practical questions: Expect SQL queries for grant/revoke permissions or backup commands.
• Case studies: Relate concepts to Nepalese companies (e.g., Khalti’s encryption, Daraz’s order security).
• Diagrams: Draw RBAC flowcharts or backup cycles—they score high!

Common Exam Questions:

• "Explain how a DBA ensures data integrity in Ncell’s database." Answer: Uses checksums, transaction logs, and regular audits.
• "Write SQL to grant a user ‘read-only’ access to a table."

  GRANT SELECT ON customers TO analyst;
  

• "Describe two backup strategies for a bank’s loan database." Answer:
  1. Full backup every Sunday.
  2. Incremental backup daily to reduce restore time.