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, page 19, 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.
End of Article
Prev. page
1
2
[3]
next page -->
