🔐 Advanced Database Security for APU Hospital

📌 Project Overview

This project delivers a comprehensive security solution for APU Hospital’s MedicalInfoSystem database. The original system, while functionally complete, exposed significant security vulnerabilities involving unauthorized access, weak data protection, and lack of auditing.

As a team of database security specialists, we redesigned and hardened the database using a multi-layered security framework covering Data Protection, Permission Management, and Auditing—ensuring compliance, integrity, and resilience of sensitive patient and staff data.

---

❗ The Problem: Initial Security Vulnerabilities

The baseline database lacked core security controls, leading to multiple risks:

• ❌ Unauthorized Data Access
  Sensitive fields like payment PINs and passport numbers were stored in plaintext and accessible without proper authorization.

• 📤 Data Leakage
  Users could view data outside their role scope (e.g., doctors viewing all appointments, patients accessing other patients’ info).

• 🧱 Data Integrity Issues
  No protection against double-booking appointments or invalid data entry.

• 🚫 No Backup/Recovery Plan
  No automated backup mechanism, risking permanent data loss.

• 🔍 No Audit Trail
  User actions and schema changes were not monitored or logged.

• 🗃️ Data Retention Non-Compliance
  Old records were not purged, violating data regulations like PDPA.

---

✅ The Solution: A Multi-Layered Security Framework

🔐 3.1 Data Protection

• Dynamic Data Masking
  Applied to fields like SPassportNumber (Staff) to reveal only last 4 digits to non-privileged users.

• Column-Level Encryption (CLE)
  Encrypted critical data such as PaymentCardPinCode (Patient) using asymmetric keys.

• Transparent Data Encryption (TDE)
  Encrypted the full database at rest (.mdf and .ldf files), securing it from unauthorized server attachment.

• Automated Backup Strategy

  • Full Backups: Daily
  • Differential Backups: Every 4 hours
  • Transaction Log Backups: Every 5 minutes
    Enables point-in-time recovery with minimal data loss.

• Data Retention Policy
  A stored procedure (sp_ImplementDataRetention) automatically deletes prescription records older than 7 years.

---

🔑 3.2 Permission Management

• Role-Based Access Control (RBAC)
  Created roles like AdminRole, Doctor, Nurse, Pharmacist, Patient and applied least privilege access.

• Row-Level Security (RLS)
  Applied to the Appointment table to restrict data access to only relevant users.

• Secure Views
  Patient_List view displays only the requesting patient's decrypted info.

• Secure Stored Procedures
  Sensitive operations like creating new patients are encapsulated in stored procedures with limited execution permissions.

• Custom DML Triggers

  • Prevent double-booking in Appointment
  • Enforce prescription update rules (only pharmacists or doctors can modify statuses)

• Role & Login Revocation
  A stored procedure automates revoking access and removing logins of resigned staff.

---

📝 3.3 Auditing

• DDL, DML, and DCL Auditing
  Implemented full auditing for:

  • Schema changes (CREATE, ALTER, DROP)
  • Data access and modification (SELECT, INSERT, UPDATE, DELETE)
  • Permission updates (GRANT, REVOKE, DENY)

• Login/Logout Monitoring
  Logs all login attempts and sessions to detect suspicious activity.

• System-Versioned Temporal Tables
  Appointment table tracks every update automatically with historical versioning.

---

🛠️ Technology Stack

Component	Description
Database System	Microsoft SQL Server
Language	Transact-SQL (T-SQL)
Security Techniques	CLE, TDE, RLS, DDM, RBAC

---

🚀 How to Implement

1. Set Up the Initial Database
   Run the base script provided in Appendix 1 to create the MedicalInfoSystem database.

2. Insert Sample Data
   Populate the database with staff, patient, and appointment records.

3. Apply Security Framework
   Execute the provided consolidated SQL script that includes:

   • Data Protection enhancements
   • Permission Management
   • Auditing configuration

4. Run Test Cases
   Use the test cases to validate:

   • Role-based access (e.g., a doctor cannot see other doctors’ appointments)
   • Data masking/encryption
   • Trigger enforcement
   • Backup and retention policies
   • Audit logs

---

👥 Authors / Contributors

1. Chen Hui En
2. Lee Wen Kang
3. Liong Qian Hui
4. Tang Yu Tong

---