Database Security & Integrity: Controls, Users, Recovery & 3-Schema Architecture

TAKEAWAYS:

• Database security protects data from unauthorized access via authentication (who you are), authorization (what you can do), and encryption (how data is stored/transmitted).
• Integrity constraints (domain, key, referential, entity) ensure data accuracy—e.g., a student’s SID cannot be null or duplicate in the student table.
• The three-schema architecture decouples user views (external), logical design (conceptual), and physical storage (internal) for flexibility and security.
• Recovery methods (checkpointing, transaction logs, shadow paging) restore databases after crashes—e.g., Ncell’s billing system recovers lost transactions in seconds.
• Users (administrators, designers, end-users) have distinct roles with privileges (e.g., INSERT, UPDATE) granted via SQL commands like GRANT SELECT ON student TO instructor.
• Normalization (2NF, 3NF) indirectly supports integrity by eliminating redundancy—e.g., a bank’s Account table avoids duplicate customer details.

1. Database Security: Protecting Data from Threats

Security ensures only authorized users access or modify data. Key components:

A. Authentication: Verifying User Identity

Authentication confirms a user’s identity before granting access. Common methods:

• Passwords: Simple but vulnerable to brute-force attacks (e.g., weak passwords like 1234).
• Biometrics: Fingerprint or facial recognition (used in eSewa for transactions).
• Multi-Factor Authentication (MFA): Combines password + OTP (e.g., Ncell’s login for MyNcell app).

Biometric authentication in eSewa for secure transactions (Image: Rachmaninoff, CC BY-SA 3.0, via Wikimedia Commons)

B. Authorization: Controlling Access

Once authenticated, users get privileges (permissions) to perform actions:

• GRANT: Assigns privileges (e.g., GRANT SELECT ON student TO instructor).
• REVOKE: Removes privileges (e.g., REVOKE DELETE ON course FROM TA).
• Roles: Groups of privileges (e.g., DBA role for database administrators).

Worked Example: Ncell’s Database Ncell’s customer service database has tables:

• Customer(cid, name, phone, balance)
• Transaction(tid, cid, amount, date)

SQL to restrict access:

-- Only allow customer service reps to update balances
GRANT UPDATE (balance) ON Customer TO 'ServiceRep';

-- Revoke delete access for junior staff
REVOKE DELETE ON Transaction FROM 'JuniorStaff';

C. Encryption: Securing Data in Transit/Storage

• Symmetric Encryption: Same key for encryption/decryption (fast, e.g., AES-256).
• Asymmetric Encryption: Public/private key pairs (secure, e.g., SSL/TLS for HTTPS).
• Hashing: Converts data to fixed-size strings (e.g., passwords stored as SHA-256 hashes).

Real-World Use:

• eSewa: Encrypts transaction data using TLS to prevent interception.
• NEPSE: Uses hashing to store investor passwords securely.

2. Database Integrity: Ensuring Data Accuracy

Integrity constraints prevent invalid or inconsistent data. Types:

Worked Example: University Database Tables:

• student(SID, SName, SAddress, SEmail)
• course(CID, CName, Credit_hours)
• studies(SID, CID, grade)

Constraints:

-- Entity integrity: SID cannot be null
ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY (SID);

-- Referential integrity: CID in studies must exist in course
ALTER TABLE studies ADD CONSTRAINT fk_course
FOREIGN KEY (CID) REFERENCES course(CID);

Visual: Referential Integrity

3. Three-Schema Architecture: Decoupling Data Views

This model separates:

1. External Schema: User-specific views (e.g., a student sees only their grades).
2. Conceptual Schema: Logical design (e.g., tables, relationships).
3. Internal Schema: Physical storage (e.g., indexes, file organization).

Why It Matters:

• Flexibility: Change one schema without affecting others (e.g., add a new user view without altering the database structure).
• Security: Users see only relevant data (e.g., a professor doesn’t see student addresses).
• Portability: Move data between systems without rewriting applications.

Real-World Example: NEPSE’s Investor Portal

• External Schema: Investor sees Portfolio, Transactions, Dividends.
• Conceptual Schema: Tables like Investor, Share, Trade.
• Internal Schema: Data stored in a clustered index for fast queries.

Visual: Three-Schema Architecture

flowchart TD
    A[External Schema
    (Student View: Portfolio, Transactions, Dividends)] -->|"Mapped via External Schema"| B[Conceptual Schema
    (Logical: Investor, Share, Trade)]
    B -->|"Mapped via Internal Schema"| C[Internal Schema
    (Physical: Clustered Index Storage)]

4. Database Recovery: Handling Failures

Databases use techniques to recover from crashes or corruption:

Worked Example: Daraz Order Recovery Daraz’s database tracks orders in Order(order_id, user_id, status, items).

• Scenario: A power outage corrupts the database at 3 PM.
• Recovery:
  1. Restore from the last checkpoint (2:55 PM).
  2. Replay transaction logs from 2:55 PM to 3:00 PM.
  3. Resolve any conflicts (e.g., pending payments).

Visual: Recovery Process

5. Database Users and Privileges

Users are categorized by roles and privileges:

Worked Example: NTC’s Traffic Management System Tables:

• Vehicle(license_no, type, owner)
• Violation(violation_id, license_no, fine_amount, date)

Privileges:

-- Allow traffic police to insert violations
GRANT INSERT ON Violation TO 'PoliceOfficer';

-- Allow only admins to update vehicle records
GRANT UPDATE ON Vehicle TO 'Admin';

## In the Real World

1. eSewa:

   • Security: Uses MFA (OTP + fingerprint) for transactions.
   • Integrity: Enforces referential integrity between User and Transaction tables to prevent orphaned records.
   • Recovery: Transaction logs ensure no money is lost if a payment fails.

2. Ncell’s MyNcell App:

   • Three-Schema Architecture: Customers see only their Balance and Usage (external schema), while Ncell’s billing team sees full Customer and Transaction data (conceptual schema).
   • Encryption: AES-256 encrypts customer data stored in the database.

3. NEPSE (Nepal Stock Exchange):

   • Integrity Constraints: Ensures InvestorID is unique and SharePrice cannot be negative.
   • Recovery: Shadow paging recovers trades if the primary database crashes during market hours.

## Exam Tip

1. Security Questions:

   • Expect SQL commands for GRANT/REVOKE (e.g., "Write SQL to allow a clerk to update the balance column").
   • Know real-world examples: eSewa’s MFA, Ncell’s encryption.

2. Integrity Constraints:

   • Define and give SQL examples for all 4 types (domain, key, referential, entity).
   • Trace errors: "What happens if referential integrity is violated in a studies table?"

3. Three-Schema Architecture:

   • Draw the diagram and explain how changes in one schema affect others.
   • Compare: "How does this differ from a flat-file system?"

4. Recovery:

   • Describe checkpointing vs. transaction logs with a real example (e.g., Daraz orders).
   • Short-answer: "How would you recover a database after a power outage?"

5. Users and Privileges:

   • Classify users (admin, designer, end-user) and assign privileges.
   • SQL practice: "Write commands to restrict a TA from deleting records."

Pro Tip: For numerical questions (e.g., "Calculate the fine for a violation"), always show step-by-step calculations with SQL queries. For example:

-- Calculate total fines for a police officer
SELECT SUM(fine_amount) AS total_fines
FROM Violation
WHERE officer_id = 'PO123';