Database Security & Integrity Constraints

TAKEAWAYS:

• Integrity constraints (e.g., primary keys, foreign keys) enforce data accuracy and consistency.
• Security mechanisms (authentication, encryption, authorization) protect data from breaches.
• Database auditing tracks unauthorized access and data changes.
• SQL constraints (NOT NULL, UNIQUE, CHECK) enforce rules at the database level.
• Role-based access control (RBAC) grants permissions based on user roles.
• Malware and SQL injection are real threats requiring defense strategies.

1. Introduction to Database Security

Databases store sensitive data (e.g., customer records, financial transactions). Security ensures:

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

Threats to Database Security

mindmap
  root((Database Security Threats))
    Unauthorized Access
    Malware & Viruses
    SQL Injection
    Data Leakage
    Denial-of-Service (DoS)

Example in Nepal:

• eSewa/Khalti use encryption to protect transaction data from hackers.
• NEPSE (Nepal Stock Exchange) enforces strict access controls to prevent stock manipulation.

2. Integrity Constraints

Integrity constraints ensure data accuracy and consistency. Types include:

A. Entity Integrity

• Primary Key Constraint: Ensures no duplicate or null values in a primary key.

  erDiagram
      EMPLOYEE ||--o{ DEPARTMENT : "works_in"
      EMPLOYEE {
          int EmpID PK
          string FirstName
          string LastName
          float Salary
          int DeptID FK
      }
      DEPARTMENT {
          int DeptID PK
          string DeptName
      }

  Example: In a bank database, AccountID cannot be null or repeated.

B. Referential Integrity

• Ensures foreign keys reference valid primary keys.

  erDiagram
      AUTHOR ||--o{ BOOK : "writes"
      AUTHOR {
          int AID PK
          string A_Name
      }
      BOOK {
          int BID PK
          string B_Name
          int AID FK
      }

  Example: If an author (AID=1) is deleted, all books by that author must be updated or flagged.

C. Domain Integrity

• Ensures data fits predefined domains (e.g., Salary > 0).

  ALTER TABLE employees
  ADD CONSTRAINT chk_salary CHECK (Salary > 0);
  

D. User-Defined Constraints

• Custom rules (e.g., Age > 18 for users).

  ALTER TABLE customers
  ADD CONSTRAINT chk_age CHECK (Age >= 18);
  

3. Security Mechanisms

A. Authentication

• Verifies user identity (e.g., username/password, biometrics).

  sequenceDiagram
      participant User
      participant DB
      User->>DB: Login Request (Username, Password)
      DB-->>User: Verify Credentials
      alt Valid
          DB-->>User: Grant Access
      else Invalid
          DB-->>User: Reject Access

B. Authorization

• Grants permissions (e.g., SELECT, INSERT, DELETE).

C. Encryption

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

  flowchart TD
      A["Client"] -->|"HTTPS"| B["Server"]
      B -->|"AES-256"| C["Database"]

D. Auditing

• Logs user actions (e.g., who modified a record?).

  CREATE TABLE audit_log (
      log_id INT PRIMARY KEY,
      user_id INT,
      action VARCHAR(50),
      timestamp DATETIME
  );
  

4. SQL Constraints

SQL enforces constraints via CREATE TABLE or ALTER TABLE:

Worked Example:

CREATE TABLE employees (
    EmpID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    DeptID INT FOREIGN KEY REFERENCES departments(DeptID)
);

5. Database Security Models

Example in Nepal:

• NTC/Ncell use distributed security to protect customer data across multiple towers.
• Daraz enforces centralized security for order processing.

6. Common Security Threats & Mitigations

Example:

• Pathao uses SQL injection protection to prevent ride-hailing fraud.

7. Real-World Applications

In the Real World

1. eSewa/Khalti:

   • Uses encryption (TLS) to secure transaction data.
   • Implements RBAC to restrict admin access to financial records.

2. NEPSE (Nepal Stock Exchange):

   • Enforces referential integrity to prevent invalid stock trades.
   • Uses auditing logs to track suspicious activities.

3. NTC/Ncell:

   • Applies domain integrity (e.g., phone numbers must be 10 digits).
   • Employs firewalls to block unauthorized network access.

Exam Tip

• Focus on:
  • Definitions of integrity constraints (entity, referential, domain).
  • SQL constraints (NOT NULL, CHECK, FOREIGN KEY).
  • Security models (RBAC, encryption, auditing).
  • Real-world examples (e.g., how banks use constraints to prevent fraud).
• Common mistakes:
  • Confusing primary key with unique key.
  • Forgetting to mention auditing in security discussions.
• Practice:
  • Draw an ER diagram with constraints (e.g., foreign key relationships).
  • Write SQL queries with CHECK constraints.
  • Explain how SQL injection is prevented in real systems.