This repository holds two related projects completed in IA 643 Database Security and Auditing. Project 6 Expands on Project 5
Project 5 Requirements: The Client Information Aliana Health System is a not-for-profit healthcare system. It operates in the states of Minnesota and Wisconsin with 61 clinics, 13 hospitals, and 15 pharmacies. Aliana Health has one of the most comprehensive electronic health record systems in the nation. The system is driven by a centralized database that made “One patient, One Record” possible. Aliana has made security and privacy protection of its patients’ medical records the highest priority, as mandated by the Federate HIPAA. The database behind the Aliana Health System consists of 51 major tables. Part of its ERD is attached as Figure 1. Aliana Health System has hired you as a database security consultant to design and implement proper security for the database. Seven tables are provided to you: Clinic, Doctor, Patient, Visit, Diagnosis, Administrator, and App_User. The structures of the seven tables were simplified for ease of implementation. Clinics are managed by clinic administrators. Doctors work in clinics. Each patient has a primary doctor, therefore, a home clinic. However, the patient can be referred to specialists in any other clinics for healthcare. The patient can change his or her home clinic for any reason. Patient’s visits with doctors are captured in the Visit table, which include visits with specialists by referrals from primary doctors. Each visit could lead to multiple diagnoses. The Project Requirements You are charged to design a virtual private database (VPD) for both the clinic administrators and the doctors. Each clinic administrator can manage his/her clinic and doctors’ information. The administrators should not have access to patient’s information. Each doctor should be able to see and maintain his or her patient’s information including visits and diagnoses. Please note that each table has two control columns -- CTL_SEC_USER & CTL_SEC_LEVEL. The columns record both user name who inserts or updates records in the tables (indicating the ownership) and the record’s security level (1 to 5). You will use triggers to automatically insert the user’s name into CTL_SEC_USER columns when a new record is inserted or an existing record is updated. Currently, each clinic has only one administrator who can select, insert, update, or delete records for his/her clinic. Each clinic has only one doctor. The VPD should allow an administrator access to only records that he or she owns. Each doctor should be allowed access to his or her own patient’s related information (no access to other doctors’ patient information). However, each doctor should be able to see information on all other doctors, administrators, and clinics within the Aliana Health System. Clinic administrators and doctors should not be allowed to access App_User which is managed by DBA643 only. The VPD should allow DBA643 to access any records in all seven tables. Do not use any view.
You will create four user accounts for the two clinic administrators and two doctors, see Table 1 below. Please use the usernames and default passwords given in Table 1. Use your IA643Spr21_tbs tablespace as default, TEMP as temporary tablespace. Do not use any profile nor password verify function. You will need to create six public synonyms for the six base tables (excluding APP_User) so that other users do not have to use DBA643 as a qualifier when they access the tables. You will also define two database roles: Admin_R and Doctor_R. Admin_R should have SELECT, INSERT, UPDATE, DELETE privileges on Administrator, Clinic, and Doctor. Doctor_R should have SELECT, INSERT, UPDATE, DELETE privileges on Patient, Visit, Diagnosis. Doctor_R should also have SELECT privilege on Administrator, Clinic, and Doctor. See Table 2.
Project 6 Requirements: Project 6 is an improved version of Project 5 to accommodate multiple administrators and multiple doctors in each clinic.
Business Requirements
One year after the deployment of project 5, Aliana Health System hired more administrators and doctors for each clinic. You are charged with the task of revising the current system according to new security requirements. The database roles defined for the doctors and the administrators in the Project 5 remain valid. When a clinic administrator logs in, he or she should have access to all records belonging to him or her. Like project 5, the administrators should not be allowed to access patient related records. When a doctor logs in, he or she should have access to all patient related records belonging to his or her clinic (including patient visits/diagnoses generated by other clinic which the doctor referred his or her patients to). The doctor should not have access to patient information in other clinics. The doctor should be able to view information of all doctors and administrators within the Aliana Health System.
To implement the Separation of Duty principle, the Aliana Health System also hired a Chief Security Officer (CSO) to manage critical security setups in various applications. You (DBA643) are one of application owners. You own the Aliana Health System’s database, table triggers, and security policy functions. You also administer the security policies. The CSO will be in charge of creating application context and logon trigger.
Project Requirements
Clean up your virtual machine. In DBA643, run the script file P6_create_tables.txt to set up the tables. (P6 has additional records added to the tables of P5). Your script file will be tested on an AWS virtual machine. You can assume the needed database is already created by P6_create_tables.txt