Designing a Security Model

Designing a Security Model

Congratulations to Kay Novotny, Principal Systems Analyst for California-based PacifiCare Health Systems, and Timothy Wolf, Manager of the OKC Development Lab for OAO HealthCare Solutions in Oklahoma City. Kay won first prize of $100 for the best solution to the May Reader Challenge, "Designing a Security Model." Timothy won second prize of $50. Here's a recap of the May Reader Challenge and the solution.

Problem

John is the chief architect for an application that retrieves data from a SQL Server 2000 or 7.0 database. He wants to design an application security model that is easy to implement and gives users limited flexibility, yet protects the SQL Server data. John envisions a model that

Help John design the security model by using SQL Server 2000 and 7.0 features. Remember that John is only designing the high-level security mechanism and that users can log in to SQL Server through either SQL Server or Windows NT accounts.

Solution

John can use the database and application roles feature in SQL Server 2000 and 7.0 to design the security model for his application. First, he can determine the kinds of security profiles he needs according to the jobs that users perform. For example, some functions need just read-only access to reporting tables, whereas others require unlimited access to the system. Then, John can establish database roles that describe these functions and place each user in a role. Finally, he can set up application roles that mirror the database roles. An application role temporarily suspends a SQL Server login and the database user account for the login and operates under its own password. John can create an application role called Sales that has a password of SalesTest and assign other permissions—such as EXECUTE permissions on stored procedures and SELECT permissions on views—to this role. To create the roles and assign permissions, John can execute the following code:

USE appdbGOEXEC sp_addrole 'Sales'EXEC sp_addapprole 'SalesApp' , N'SalesTest'GOGRANT SELECT ON rep_tbl TO SalesGRANT EXECUTE ON AppSp1 TO SalesAppGRANT SELECT ON AppView1 TO SalesAppGO

Only after John enables the application role can users receive permissions to execute stored procedures or perform other valid operations. To enable the role, John needs to connect to SQL Server and call the system stored procedure sp_setapprole. John can hard-code—and encrypt—the application role’s password in the application, then send the password to SQL Server in encrypted format with the following call:

EXEC sp_setapprole 'SalesApp' , {ENCRYPT N'SalesTest'}, 'odbc'

John concludes that he can design the three-pronged security model for his application around the application roles feature that SQL Server 2000 and 7.0 offer. First, an application role lets users assume the identity of the application and retrieve SQL Server data under the role’s permissions only while they’re logged in to the application. Second, restricting users to the application role’s permissions also prevents them from executing the stored procedures or selecting data from the views by using a tool such as Query Analyzer or an application such as Microsoft Access. Third, the application role incorporates a streamlined mechanism for assigning permissions through a single application login instead of through multiple individual user logins. Although users’ SQL Server logins or Windows NT accounts are suspended when they access SQL Server through the application role’s single login, their personal logins continue to operate in the background, and database administrators can still audit individual user activities.

June Challenge

Now, test your SQL Server savvy in the June Reader Challenge, "Digging out the Data" (below). Submit your solution in an email message to [email protected] by May 16. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: Mark is a database developer and administrator for an Internet retailer. The company’s database is currently hosted on a server running SQL Server 7.0, but the company will soon upgrade to SQL Server 2000. As part of his primary duties, Mark generates reports for the sales people. One of his tasks requires him to assemble the following data for the company’s 20 top-selling products, ordering the data first by totals for each product sold, then by the summary rows.

Because the Products, Categories, and Order Details tables in the Northwind sample database closely approximate his database schema, Mark decides to use them to try various approaches to retrieving the data. He also wants to avoid using temporary tables and cursors for this assignment. Help Mark write a query that generates the data he wants, yet lets him easily add other filters and parameters as needed.