| Downloads |
|---|
| 15906.zip |
Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or fewer) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.
Implementing Security Through Views
Our company, like many firms, uses a time and billing system to manage client billing. To reduce the workload that ad hoc queries placed on the system and to present time and billing data in a format that users could easily query or import into analysis tools such as Microsoft Excel, we decided to create a data warehouse. The trickiest project requirement was to implement a security plan that restricts one department's employees from seeing time and billing data that another department's employees generate.
We considered dividing the data along organizational lines, but doing so would have created several undesirable side effects. First, we would have had to generate a new table or view each time we added a new user. Second, to configure security, we would have needed Database Owner (DBO) access to the database and would have had to use a complicated security program to configure permissions to the database tables. Third, and most important, we would have had to create a table for every conceivable security scenario and tailor instructions to each user—a process that becomes impossible if you support more than a handful of users.
Here's how we met the security requirement. The time and billing system comprises many tables, including the timecard table, which holds employee time records, and the timekeep table, which lists employees. Both tables use organization codes. The timecard table uses the codes to identify which group owns the time card record. The timekeep table uses the codes to identify which group an employee belongs to. Our solution was to use these organization codes to filter users to appropriate views of the data in the data warehouse.
The solution uses the SUSER_SNAME() function to return the SQL Server users' login ID name. We also created a table called SecurityAccess to use as a security filter. The SecurityAccess table contains two fields: OrgCode, which holds the organization code information, and LoginID, which holds an integrated account (a Windows NT domain name and login ID) or a standard SQL Server account (just login ID).
Using the T-SQL function and the SecurityAccess table, we first created the V_SecAccess view, which Listing 1 shows, to use in joins to create secure views of particular data. We then created the V_Timecard view, as Listing 2 shows, which returns time card records only for employees who have a certain organization code. Because the FROM clause specifies the V_SecAccess view, a user running a SELECT statement against V_Timecard can view only timecard table records that the SecurityAccess table's OrgCode field gives them access to.
However, employees in one group sometimes work on projects that a different group owns, so we modified the view so that a manager can see time card information for employees assigned to another organization code. Managers can use this view to run reports on what their employees are doing on projects that other groups own. Listing 3 shows the modified view, V_TimecardPlusEmp.
In this view, we use the DISTINCT keyword to eliminate duplicate rows from the result set so that employees whose OrgCode in timekeep matches their OrgCode in timecard won't see duplicate records in the view. We also use joins to accommodate the new either-or logic. Notice in this example that the only records not in the view are those where the employee record or the time card record don't match an OrgCode value in the SecurityAccess table for the user running the query.
Since we first implemented this security strategy, we've created some Microsoft Office 2000 applications for users who are less technically savvy, and with little effort, we've revised the security criteria to include other fields. This security strategy has also had a beneficial side effect: Because the views restrict the number of potential records that SQL Server will return, a user who omits WHERE criteria, executes a query, then leaves for lunch is less likely to cause a problem.
