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 from the "Download the Code" link at the top of the page. 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 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.
Given your findings and the CEO's specification of a generic design implementation, you decide your row-level security design must
- Avoid schema changes to existing database objects
- Avoid implementing nonSQL Server code libraries or functionality
- Avoid third-party or custom application-level user accounts in favor of DBMS-supported SQL Server standard login user accounts or Windows domain accounts. (One advantage of this decision is that it lets you audit the time and frequency of successful and failed login attempts.)
Of course, the final design must still somehow uniquely identify specific rows, perhaps by reference to a primary key on the table that contains the data. A row-security implementation must also relate the row data to some form of user accounts and to permissions. The design implementation you select involves adding an Authorized table to house row-access authorization information. The Authorized table from Listing 2's script has three important columns. The values of the TargetTable column identify which tables contain user-accessible data. The DataRowPrimaryKey column identifies specific key values of those defined tables. Finally, the values in the PermittedSuser_Sname column define which users are permitted access to the rows specified by the contents of the key-values and table-identifying columns.
For each original view in MedicalDb, the script creates a new view (prefixed by "view_rs_") that joins the target tables of the original views to the Authorized table and uses the current login connection's account identity in a WHERE clause to restrict the rows the user can access. Each connection a user login initiates thus correctly returns only the data the login account is explicitly permitted to view (by virtue of row entries in the Authorized table).
A key to understanding how the row- securityenabled views behave is to realize that they're simply a manifestation of the information contained in the Authorized table and applied through the query's WHERE clause to the tables that a view represents. For example, the Authorized rows for PermittedSuser_Sname Smith, which Table 1 shows, represent underlying target tables and specific rows therein that user Smith is permitted to view. For each new view_rs_ view in MedicalDb, the query results will be appropriate to the user login accessing them; once you configure the logins, login Smith sees only Smith's data when executing a SELECT * query from any new view_rs_ views.
Security Roles and Permissions
But when you test the solution you created in Listing 2, you realize you have problems. You create a test login for user Smith, add records for Smith to the Authorized table, and run some simple tests. The MedicalDb security objects and data design (featuring the new view_rs_ views and the Authorized table schema), which works perfectly well to restrict user logins when a user accesses new views, has a hole. When you're logged in as the user Smith, the view_rs_ views work fine—that is, when you test SELECT queries as the login Smith from view_rs_ View Objects, these test queries demonstrate row security for the Smith login account as expected. To reproduce the initial tests, connect as the login Smith and execute the following queries in the MedicalDb database:
SELECT * FROM \[MedicalDb\].\[dbo\]. \[view_rs_CaseDiagnosis\] — returns 0 Smith rows SELECT * FROM \[MedicalDb\].\[dbo\]. \[view_rs_CaseManagement\] — returns 1 Smith row SELECT * FROM \[MedicalDb\].\[dbo\]. \[view_rs_Cases\] — returns 2 Smith rows SELECT * FROM \[MedicalDb\].\[dbo\]. \[view_rs_PatientNotes\] — returns 0 Smith rows SELECT * FROM \[MedicalDb\].\[dbo\]. \[view_rs_Patients\] — returns 1 Smith row GO
However, when you test SELECT queries on other objects, the Smith login can access all data in any base table, as well as all data from the original views:
SELECT * FROM \[MedicalDb\].\[dbo\]. \[CaseDiagnosis\] — returns all table rows SELECT * FROM \[MedicalDb\].\[dbo\]. \[CaseManagement\] — returns all table rows SELECT * FROM \[MedicalDb\].\[dbo\].\[Cases\] — returns all table rows SELECT * FROM \[MedicalDb\].\[dbo\]. \[PatientNotes\] — returns all table rows SELECT * FROM \[MedicalDb\].\[dbo\].\[Patients\] — returns all table rows GO
You realize that you need to discontinue the security practice of making all database users members of the db_datareader fixed database role in the MedicalDb database because this practice accounts for the behavior of the Smith login you observed. Contemplating the administrative burden of assigning permissions for every object and user individually, you decide to implement two roles in the MedicalDb database to manage patient-user login-account permissions. Roles give DBAs a way to easily administer large collections of user accounts and their permissions. The code in Listing 3 creates one role, db_HIPAA _PatientUserPermissions, which explicitly specifies granted permissions members should have. Another role, db_HIPAA_PatientUserRestrictions, explicitly specifies any restrictions that you need to apply to members. You execute Listing 3's code to create security roles and permissions pertinent to implementing row security for the new views.
Adding these two roles doesn't solve anything by itself, but roles provide an organized and simplified means of comprehending, maintaining, and implementing permissions for large collections of user accounts. For example, manually verifying the permissions for 10 roles with 1000 login user members takes far less time than checking the same permissions applied to 10,000 login users individually. Thus, applying permissions and restrictions to all MedicalDb database user members is a simple matter of issuing GRANT or DENY statements against the appropriate role. For example, note that after you run Listing 3, SELECT permissions on view_rs_Patients exist for the db_HIPAA_PatientUserPermissions role, and permissions on the underlying Patients table are denied to the db_HIPAA_PatientUser-Restrictions role. You can apply or remove permissions from a database user simply by making a user a role member or removing a user from a role. Note that to see the effects of these role memberships on a specific database user (e.g., Smith), you have to add the specific user to both the Permissions and the Restrictions database roles.
Setting Up Security Roles and User Logins
After validating the row-security design, you add the bulk of the user logins. Although manually adding a few dozen user logins through Enterprise Manager is simple, a real medical organization might have hundreds or thousands of logins for you to add, assign initial passwords to, grant MedicalDb user access and identities to, and assign to the Permissions and Restrictions roles you created in the previous step. A script-based alternative can save time and protect against typographical and manual-configuration errors.
One possibility is to generate static account-creation scripts from a data table such as Patients (which contains good candidate user-login names). However, in this case, a script that uses a cursor offers certain advantages. Listing 4 shows a simple cursor-based script. For example, you can easily adjust a cursor-based script to apply coded rule logic to generate unique logins for multiple users who have the same first or last names. Compared to static scripts, cursor-based scripts tend to be compact enough that you can appropriately modify them and place them in a SQL Server job step. You might then schedule an administrative job containing such a step to automatically add new logins whenever necessary.
Some other best-practices considerations for administrative scripts and jobs include collecting and reviewing script output for successful completion or error messages, especially when you've placed scripts into job steps. When a script step fails, you can often determine what went wrong and address problems much more quickly if the script generated output that you can examine in an output file or table.
As another best practice, try to achieve a balance between consolidation and modularization. For example, a script or step that performs several dozen unrelated tasks but returns the same error when any one task fails would be a good candidate for splitting into multiple scripts or steps. Thus, breaking a script such as Listing 4 into a job with a few steps could be beneficial for troubleshooting and maintenance; however, breaking the same script into 20 or 30 steps would be excessive and add more clutter than clarity.
Having added the logins, your next step is to test and validate them. You can use Web Listing 1's script to verify that the logins you added in Listing 4 implement row security as expected. In practice, you'd sequentially create several new Query Analyzer connections, log in as different MedicalDb user logins, and check row access for each base table and for each view_rs_ view. For example, login connections of login White should be able to view only the rows pertaining to patient White, login Smith connections should be able to view only the rows pertaining to patient Smith, and so on. Similarly, none of these logins should be able to browse base tables because their membership in the db_HIPAA_PatientUserRestrictions role denies access.
Meeting the Goal
Finally, the time has come to assess how well your solution is likely to serve the company. (Will it triple your salary, or could it mean a quick ticket out the door?) You've done it—the row-security design meets MedicalDb Company's IT goal to allow any given patient access to only that patient's data, without compromising the other patients' data (or company data to which no patient should have access). Your design meets the CEO's general criteria and applies best practices wherever possible. It includes the following notable features:
- Non-DBMS code libraries and functionality aren't required, nor are additional third-party software components or external agents or services. Therefore, the design can easily be implemented into future database applications.
- The solution uses built-in SQL Server functionality to implement user-account functions and allows use of SQL Serverprovided logins for account management and support.
- The solution uses SQL Serverprovided object permissions and management features. DBAs can manage and set user permissions and activity by using standard SQL Serversupported methods.
- The design requires minimal schema changes to existing objects (especially base tables).
The DBMS-based design also supports multiple applications and prevents users from bypassing row-level security by making generic connections from such applications as Microsoft Access, Microsoft Excel, and Business Objects' Crystal Reports.
In the process of building out the design, you applied some valuable best practices to facilitate the implementation and maintenance of a production row-level security scheme, including establishing standards, using meaningful naming conventions, using roles to simplify maintenance, using cursors in dynamic account-management scripts, and performing sufficient testing. Microsoft might build support for row-level security into a future SQL Server release. Until then, you can design and implement generic DBMS-based approaches (similar to the one described here) to provide viable, general-purpose row-level security when business rules or legislated mandates call for it.
