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
Prev. page
1
[2]
3
next page 
