Microsoft® SQL Server® 2012 Bible

851

Chapter 33: Authorizing Securables

33

TABLE 33-3 Sample User and Windows Group Role Assignments

User / Windows Group Database Roles

Sam Admin

John Public

Larry DBTeam

Clerks (Betty, Tom, Martha, and Mary) DataEntry

From this security model, the following users can perform the following tasks:

■ (^) Betty, as a member of the Clerks Windows group assigned to the DataEntry
user-defi ned role, can execute the application that executes stored procedures to
retrieve and update data. Betty can run select queries as a member of the Public role.
■ (^) Larry, as part of the DBTeam user-defi ned role, can perform any task in the
database as a member of the sysadmin server role.
■ (^) John cannot connect, read, or write data to any database by default as a member of
the public role.
■ (^) As a member of the Admin role, Sam can execute all stored procedures. He can also
manually modify any table using queries.
■ (^) Only Larry has unrestricted access to the database server, which includes the abil-
ity to modify server and database roles and permission assignments.

Views and Security

A popular, but controversial, method to design security is to create a view that

exposes only certain columns, or that restricts the rows with a WHERE clause and a

WITH Check Option, and then grants permission to the view to allow users limited

access to data. Some IT shops require that all access goes through such a view.

Those opposed to using views for a point of security have several good reasons:

■ (^) Views are not compiled or optimized.
■ (^) Column-level security can be applied with user-defi ned SQL Server security.
■ (^) Using views for row-level security means that the WITH CHECK OPTION must be
manually created with each view. As the number of row-level categories grows, it
can become cumbersome to do this type of manual maintenance.
c33.indd 851c33.indd 851 7/31/2012 10:01:25 AM7/31/2012 10:01:25 AM
http://www.it-ebooks.info