Congratulations on your new job! You are the new database analyst for MedicalDb Company, and the CEO is making you responsible for selecting and implementing a secure patient-records privacy solution. Success means that your solution works well, saves the company from expensive litigation, and triples your salary. Failure means following in the footsteps of your recently fired supervisor, Aye B. Goofftup, the director of Clinical Information Systems, whose patient-records privacy solution turned out to be no solution at all.
A CEO-initiated independent audit for compliance with the Health Insurance Portability and Accountability Act (HIPAA) discovered two serious problems with the company's existing security implementation for its MedicalDbApplication application. First, patients using MedicalDbApplication's patient module to view their own medical histories, as intended, can also browse the history of any other patient. The cause appears to be a third-party software implementation problem. A component of the third-party software package was incompatible with a necessary OS service pack component; as a result, the OS service pack installation a month ago overwrote the third-party software component. You need to immediately develop views to replace the views the patient module uses and demonstrate that they restrict a patient-user to seeing only his or her own medical data. Because of the problems with third-party software, you must use only generic built-in SQL Server functionality, without third-party software or software components, to implement this solution.
Second, MedicalDbApplication's security has been subverted. Several individuals regularly use a reporting tool to bypass MedicalDbApplication's security entirely, apparently through an ODBC connection. Former director Goofftup had assured the CEO that Medical-DbApplication completely prevented casual browsing of private patient data by ordinary company employees and that the systems were fully HIPAA compliant. You need to address this compromised security issue within a month of completing your first task.
In researching privacy-compliance issues, you discover that nationally legislated mandates such as HIPAA (and internationally legislated mandates such as the European Union Data Protection Directive—EUDPD) require enterprises to support data privacy and security as never before. HIPAA specifically includes provisions designed to protect the security and confidentiality of health information. The EUDPD standardizes protection of data privacy for EU citizens. (For descriptions of the pertinent legislation, see the sidebar "Legislating Privacy," page 18.) Among other security features, many such mandates specify individualized access to private data and often amount to a requirement for a form of row-levelaccess security to support such restricted access to private data. But SQL Server 2000 and earlier releases don't provide built-in row-level security.
Front-end applications and third-party security products such as encryption code libraries take a variety of approaches to row-level user-security requirements. Possible solutions might involve any combination of the following:
- application user-account schemes (for example, user accounts that are implemented into internally developed application code and not in or supported directly by the database management system—DBMS)
- third-party services and code libraries that must be called or otherwise integrated into user applications
- schema changes to existing objects (which might necessitate rewriting existing applications when feasible)
- However, such solutions might not be traceable (to see when users have accessed information within an application, as legislation may require), easily maintainable, or readily undone in the event a given solution causes any problems with existing mission-critical systems.
Until SQL Server directly supports row-levelaccess security, you have various less-direct options, such as those mentioned above, to consider implementing in your production environments. A good way to start is by first clearly understanding the core elements of an effective row-level security solution. Any approach to row security must uniquely identify specific rows (say, by the key column values of the table containing them) and relate them to some form of user or role permissions. Your goal is to allow any given patient access to only that specific patient's data, without compromising the data of other patients or company data to which no patient should have access.
To set up this article's examples, use sa or an equivalent login in the sysadmin role to run Listing 1 in Query Analyzer. This script creates and populates the MedicalDb database that you'll use for this case study. (Note that the printed listings are just snippets of the full-length scripts that you can download at http://www.sqlmag.com, InstantDoc ID 42615. The full scripts give creation code for all tables and views, plus additional detail to help you avoid poor row-level security implementations.) The MedicalDb database views that the patient module uses (view_ CaseDiagnosis, view_CaseManagement, view_Cases, view_PatientNotes, and view_ Patients) are straightforward in design. Each involves simple joins that let patient-users view some personal identifying information from the Patients table along with specific data from certain MedicalDb base tables. The tables containing patient data include CaseDiagnosis, CaseManagement, Cases, PatientNotes, and Patients. You contemplate how to design new patient-module views to meet the CEO's directives.
You run Listing 2, page 17, to add the table and view objects required to restrict users to seeing only their own medical data rows in the MedicalDb database. From your investigation, you discover that you might someday need to provide patient access to data the CEO didn't mention (e.g., data in the DiagnosticHistory and LabResults tables). From tests in a development environment, you discover that changes to many base tables and MedicalDb views would render certain older core modules of the MedicalDbApplication inoperable. You also discover that the subversion problem is related to a MedicalDb database guest user that has db_datareader role membership and is bestowing MedicalDb data-access capabilities to Windows authentication-based DBMS logins (from the corporate domain). You immediately address this problem, among others, by removing the guest user and permissions explicitly granted to guest and public from MedicalDb.