Define and control user privileges
When anyone—legitimate user or malicious hacker—authenticates to your system, the succeeding layer to authentication of your defense-in-depth strategy is access control. Access control limits users to only those resources and actions they have a legitimate reason to access or perform. When you design your access control strategy, adopt least privilege as your guiding principle. By limiting a user to only the resources and levels of access he or she requires, you reduce how much damage a rogue user or compromised user account can cause.
Control in Context
Access control takes slightly different forms depending on the context in which you apply it. You might need to define access control at the OS, database, and application levels. Sometimes network access control (i.e., limiting a user or device to specific subnets and ports within the intranet) is lumped in with access control (OS and application controls limiting which objects a user can access and how), and in principle, network access and access control within a system have much in common. Because these two layers of defense are usually implemented in different ways and within different components of your technology infrastructure, I see them as separate.
At the Windows OS level, access control consists primarily of managing user access to files, folders, and printers with access control lists (ACLs). Access control also comes into play when you define which operations non-domain-admin IT personnel can perform. By using ACLs with folders, system services, printers, and organizational units (OUs) and other Active Directory (AD) objects, you can grant IT staff enough authority to fulfill specialized roles without exposing the company to greater risk by generating full domain administrator accounts.
At the SQL Server database level, access control is a matter of granting access both to databases and objects within those databases (e.g., tables, views, stored procedures). Tables are typically the focus of access control in a database. You can grant insert, update, delete, and select (query) permissions at the table level and even define which columns a user or group can update. Depending on the application, users might or might not have direct access to the database. In most applications that have an application server sitting between the user and the database, the application server uses a single trusted user account to perform all operations against the database no matter who the end user is. In such a case, access control at the database level is pretty simple—only the trusted application account should have access.
In cases in which client applications directly access SQL Server through ODBC database protocols, user access control must be defined at the database level on a table-by-table basis. SQL Server and some other databases let you define database accounts (which are managed completely by the database) in AD or leverage the existing accounts defined in AD. Where possible, it usually makes sense to exploit this integration so that you can reap the rewards associated with fewer user accounts per person.
Most applications, whether they have a middle-tier application server or not, implement a significant level of access control. However, more applications are being designed to leverage AD so that you can use the accounts and groups you already maintain in AD for many purposes. Groups are indispensable for maintaining access control and verifying access. I recommend never granting permissions to individual user accounts—always do so through a group, even if the group has only one member. If you manage access through groups, you won't have to deal with users retaining inappropriate access after a job change, try to figure out what access a new hire who is replacing a departed employee needs, or convince an auditor that you have a handle on the "who has access to what" question.
I strongly recommend that you limit group usage to domain groups, which reside in AD; this makes it easy to view all the groups a user belongs to without leaving AD. If you develop the habit of using local groups defined on various member servers, determining what a user can access throughout the network can be difficult. If your company separates responsibility for administering AD from managing access to member server resources, AD administrators need to make it easy for member server administrators to create the groups they need. AD admins either need to be quickly responsive to requests for new groups and membership changes or they need to create an OU for each server administrator and delegate to server administrators the ability to manage their own groups. In either case, it's important that the name of the group or the notes field documents the group's resources and level of access.
When granting a user access to a resource, you should be able to justify your action both as a best practice and for compliance with legislation such as the Health Insurance Portability and Accountability Act (HIPAA) or the Sarbanes-Oxley Act (SOX). Each business resource (e.g., folder, database, application) should have a designated business owner who is responsible for approving access requests. Make sure you have a record of approval for each access control change you make. A simple way to accomplish this is to establish a public Exchange folder in which you post approval email from resource owners.
Audit, Maintain, Verify
Access control is a crucial layer in your defense-in-depth strategy. Use an access control strategy that is auditable, maintainable, and verifiable. For auditability, you need to be able to produce a record of access control changes and their associated approval or justification. The key to maintainability and verification is using groups for access control, and the best group model is a two-level structure that separates user types and roles from resources and access levels. For a detailed explanation of this group structure, see "Effective Access Control for Win2K and NT" at http://www.windowsitpro.com,
InstantDocID 15482. Although I wrote this article for Windows 2000 and Windows NT, everything holds true for the Windows Server 2003 and AD environment, with one exception. In Windows 2003 and AD, you can replace the use of member server local groups with domain local groups.