SCENARIO

Northgate Hospital's data governance audit found a critical issue: nursing staff have direct SELECT access to the billing table, which contains patient payment data and credit card details — a GDPR violation. You are the DBA. Your job is to create purpose-built views that expose only what each role needs, then revoke direct table access and replace it with view-based access.

TASKS

• Create patient_summary view — name, ward, admission date (no billing info)
• Create ward_stats view — ward name, occupancy count, avg stay days
• GRANT SELECT on both views to nurse_role
• REVOKE SELECT on billing from nurse_role
• Test that nurses can read the view but not the base table
• Run SHOW GRANTS to verify the final permission state

KEY COMMANDS

• CREATE VIEW patient_summary AS SELECT p.id, p.full_name, p.ward_id, w.name AS ward, p.admitted_date FROM patients p JOIN wards w ON p.ward_id = w.id;
• CREATE VIEW ward_stats AS SELECT w.name, COUNT(p.id) AS patients, ROUND(AVG(DATEDIFF(NOW(),p.admitted_date)),1) AS avg_days FROM wards w JOIN patients p ON p.ward_id=w.id GROUP BY w.name;
• GRANT SELECT ON hospital_db.patient_summary TO 'nurse_role';
• REVOKE SELECT ON hospital_db.billing FROM 'nurse_role';
• SHOW GRANTS FOR 'nurse_role';
• DROP VIEW IF EXISTS patient_summary;