Lesson 2:Designing a Database Security Plan | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Once you understand database security architecture, the next step is to design a security plan that protects the database without being unnecessarily restrictive. To design a security plan, you identify security requirements contained in a list of system requirements, translate the rules into a set of user activities, and then create a User-to-Activity Map. The User-to-Activity Map serves as a security implementation guide. This lesson focuses on SQL Server security, described as layer five in Lesson 1. Network administrators, systems administrators, and engineers are responsible for implementing layers one through four of database security; database administrators and designers are responsible for implementing layer five, and application developers are responsible for implementing layer six. A security specialist typically oversees the entire security design. This lesson explores how to create a database security plan and how the security architecture explained in Lesson 1 relates to designing an efficient and effective plan.

After this lesson, you will be able to:

Extract database security requirements from system requirements.
Design security for a SQL Server 2000 database.
Determine how to assign security to users, groups, and roles.
Explain the purpose of object chaining and forming a security hierarchy through nesting.

Estimated Lesson time: 25 minutes

Requirements

In Chapter 3, "Designing a SQL Server Database," you learned that a security plan identifies database users, the data objects that they can access, and activities that they can perform in the database. Gather this information by extracting security requirements from system requirements and by determining any other security requirements that might not be part of the system requirements. For example, system requirements might not include security administrative activities such as auditing the execution of a stored procedure or running a database backup. After listing the data objects that need protection and the restricted activities that users perform in the database, create a list of unique users or classes of users and groups that access the database. Armed with this information, create a User-to-Activity Map. The map is a table that cross-references the two lists to identify which users can access protected data objects and which restricted activities they can perform in the database.

Suppose you have an employee database that is accessed by 100 users. The database contains an employee information table (Employees), a salary grade table (Salaries), and an employee location table (Locations). All employees access the Employees table and the office location information in the Locations table. A small group of employees can access the Salaries table and the home address information in the Locations table. The same small group of employees can add, delete, and modify data in all of these tables. Another user is responsible for performing nightly backups, and another group of users can create and delete data objects in the database. The security requirements for this simple database example are as follows:

All employees run SELECT statements against the Employees table.
All employees run SELECT statements on the office location information in the Locations table.
A small group of employees runs INSERT, DELETE, and UPDATE statements against all columns in all three tables.
A user runs nightly database backups and performs general database administration and requires full server access.
A group of users runs CREATE and DROP statements in the database.

The list of unique users, classes of users, and groups that access this database is as follows:

All employees are a class of users covered by the Public database role.
Members of the HumanResources Windows 2000 group require restricted access to the database.
User account JDoe is a database administrator.
Company database developers create and delete objects in SQL Server.

The following User-to-Activity Map is constructed from the information provided:


User Account, Group, or Role	Activity
Public (role)	Read-only access to the Employees table
Public (role)	Read-only access to office location columns in the Location table
DOMAIN01\HumanResources (group)	Full access to the Employees, Location, and Salaries tables
DOMAIN01\Jdoe	Full access to the database
DOMAIN01\dbDev	Create databases

For additional security examples, refer to "Planning Security" in SQL Server Books Online and to the exercise in this lesson.

Nesting and Ownership Chains

There are many ways to implement a security design in SQL Server. The goal is to implement security as efficiently as possible without compromising security requirements. For example, if 100 users all require the same level of access to the database, it is more efficient to use groups or roles than to implement security for each individual user. Both approaches will achieve the same goal, but the former design (using groups or roles) is easier to maintain and faster to implement. There are a number of security features, such as nesting, ownership chains, and predefined roles, that will help you design an efficient security plan.

Nesting

Nesting helps you avoid redundant permission assignments. For example, assign the Public role SELECT permission to tables that all authenticated users are allowed to access. Assign restricted object and statement permissions to a group (named Group01 for this example) that requires the same restrictions. Then, assign special permissions to a user or smaller group (named SpecialGroup01) that requires special permissions. The Public role contains all users, groups, and roles (including Group01 and SpecialGroup01). These two groups inherit the permissions assigned to the Public role. Group01 contains SpecialGroup01 so that SpecialGroup01 inherits the permissions assigned to Group01. Members of the group inherit special permissions assigned to SpecialGroup01.

NOTE

Windows 2000 native-mode domains also support nested groups.

Ownership Chains

Certain privileges are implied, either by predefined role membership or through object ownership. Implied permissions help you minimize the amount of manual assignment that you must undertake to meet security requirements. Consistent object ownership and forming ownership chains are other effective ways to limit and consolidate permission assignments. An ownership chain exists when a set of nested objects and the original calling object are owned by the same user and are located in the same database. Ownership chains allow database users to access data and to perform activities if they are only granted permissions to the calling object (either a stored procedure or a view). SQL Server checks only the calling stored procedure or view for object or statement permissions when the entire chain is owned by a single user account.

A chain is possible because views can call tables or nest other views. Additionally, stored procedures can call tables, views, or nest other stored procedures. The calling object depends on the underlying called objects. For example, a view that calls a table depends on the table in order to return a result set. As long as the same owner of the calling object owns all called objects, an ownership chain is formed. The object owner, not the user running the procedure, owns any objects created within a stored procedure. Ownership chains only apply to SELECT, INSERT, UPDATE, and DELETE statements.

NOTE

Recursive nesting does not provide security indirection because the calling and the called object are identical (and thus require identical permissions).

If an ownership chain is not formed, due to inconsistent object ownership, SQL Server checks permissions on each object in the chain whose next-lowest link is owned by a different user. The owner of any nested object retains access control. To optimize permission assignment, consider specifying the database owner (dbo) when creating database objects.

Security Design Recommendations

There are general guidelines for implementing SQL Server security. The network environment influences how you apply security accounts (users, groups, and roles) to your database security design. Existing permissions help you build an efficient database security design.

Users, Groups, and Roles

Determining whether to assign permissions to groups, roles, or users depends on who needs permissions. For example, assign permissions to an individual user account if a single user requires unique permissions. The type of server environment in which SQL Server runs also affects to whom you will assign permissions (groups, roles, or users.) For example, assign permissions to Windows domain users if the server is part of a domain and is accessed by using Windows authentication. If possible, add a user to a predefined role rather than assigning individual permissions. Assign users to roles if there is not a Windows group that adequately represents a set of users who need database permissions, if SQL Server is not running in a Windows domain, if Windows does not authenticate access to SQL Server, or if you do not have permissions to modify group membership. For example, there is a Windows 2000 group called developers that includes all company developers, but the database developers are the only members that require full access to SQL Server. Therefore, create a role named dbDev, and add all database developers to this role. Assign database permissions to groups if there is a Windows group that contains an appropriate set of members and users who connect to the database via Windows authentication.

Permissions

Use predefined roles (public, fixed database, and fixed server roles) rather than directly assigning object and statement permissions to roles, groups, and users. Fixed roles imply wide-reaching database and server permissions that cannot be changed. Therefore, carefully assign users to fixed roles. Assign permissions to groups and roles before assigning them to users.

Design your permission structure so that permission assignments that apply to all users are at the top of the security hierarchy, and place restricted permission assignments lower in the security hierarchy. Create objects with a consistent owner so that you can create ownership chains. Create views and stored procedures that make use of ownership chains and that assign permissions to the calling stored procedure or view.

When granting object permissions to a user account, you can allow the user account to grant object permissions to others. Delegating this ability to other users means that you do not have to assign all permissions in the database. Assign the Deny permission state carefully. The effective right of any users who are directly assigned this permission state or who inherit it from a group or role membership is deny, regardless of permissions granted elsewhere.

Exercise 1: Designing Security for BookShopDB

In this exercise, you will extract security requirements from the system requirements outlined for BookShopDB in Chapter 3 and add additional security requirements to the list. You will then create a User-to-Activity Map in preparation for implementing security in Lesson 3, Exercise 2. To complete this exercise, you need paper and a pencil or a word processor.

To identify security requirements

Return to Chapter 3 and review Exercise 2, "Identifying the System Requirements for your Database Design."
Identify and write down the security requirements contained in the system requirements.
Identify and write down two security requirements that do not appear in the list extracted from the system requirements.

To determine users, groups, and roles for security and create a User-to-Activity Map

Assuming that SQL Server is running in a Windows domain named BOOKSHOP, create a list of unique users, roles, and groups to accommodate the security requirements. List only the users, roles, or groups that you believe are required to accommodate the security requirements.
Create a User-to-Activity Map that ties the security requirements to the unique users, roles, and groups that you defined in the previous step.

In Lesson 3, you will implement security for BookShopDB by using the security design that you created in this exercise. In an actual database security implementation, many more additions and modifications are likely to be made to the security design.

Lesson Summary

Designing a security plan starts with understanding SQL Server security architecture and then extracting security requirements from a list of identified system requirements. However, system requirements might not include all security requirements, such as security for system administration. Therefore, the next step is to resolve additional security requirements that are not part of the system requirements. Next, identify users, groups, and roles to which privileges are assigned. Finally, tie security requirements together with identified users, groups, and roles to create a User-to-Activity Map. A security design is improved by considering the permissions to be assigned and the SQL Server network environment. For example, if two users need the same permissions, use a group or a role. Additionally, consider using existing groups in a Windows domain before creating new groups, and use predefined roles before creating new roles. Wherever possible, optimize a security design by using group and role nesting and ownership chains. Build a security hierarchy with group and role nesting. Consolidate permissions on fewer database objects by building ownership chains.