A Security Roadmap
(Page 1 of 7 )
The task of securing SQL Server can seem overwhelming, just from the sheer number of details to be managed. Key to the process is understanding which items you need to use, and which ones you can leave out. In this first chapter from Morris Lewis'
SQL Server Security Distilled (Apress, 2004, 1590592190), you will learn about your options for authentication and authorization, two key security processes.
IN MANY WAYS, securing SQL Server reminds me of paintings by Monet I saw at the Museum of Fine Arts in Boston years ago. When you stand very close to Monet’s paintings, all you see is little dots of color. It is only when you stand back that you see how the dots converge into a complete picture. Obviously, Monet had to focus on where he placed each spot of paint, but it is equally obvious that he knew where those daubs of paint were going to go before he started painting. For a database server, the daubs of paint might be a user, or a permission, or a piece of data, and the picture they form shows how they all relate to each other, and how they fulfill the primary goal of giving people no more and no less than the rights they need to accomplish their tasks. Much like the painting, we need to focus on where we will assign permissions, but We also need to have the big picture in mind before we start.
Quite often, people are overwhelmed at the sheer number of details to be managed when making sure that database users get the permissions they deserve and do not get permissions they do not deserve. Let’s face it, securing SQL Server is not a simple task. The process starts by trying to determine the identity of a user who wants to log in. Then SQL Server has to decide whether the user has permission to perform a very large list of activities at the server level. Finally, SQL Server has to decide whether the user can access a database, what identity he will have within that database, and what he can do with the data stored there. To add to the complexity, the user could be logging in with a Windows account instead of an account managed by SQL Server and, in SQL Server 7.0 and 2000, he could receive both server and database permissions by being a member of a Windows group. If you look at each individual piece of the process to the exclusion of the others, providing appropriate access to data does seem to be easy but, when you put all the pieces together, the total picture can be quite intimidating.
Fortunately, you do not have to be a genius like Monet to learn to combine all those individual pieces into a coherent, understandable, and manageable security plan. Part of the learning process is to develop an understanding of which things you need to use, and which things you can leave out. Just as Monet did not use every color available in a single painting, so are you not required to use every feature SQL Server offers for securing data. SQL Server is very flexible because it is used in many distinctly different environments. A technique that is appropriate for one environment will often simply not work in a different one; therefore, my goal for this book is to teach you how to evaluate the strengths and weaknesses of the different ways of securing data for your particular environment.
Even though securing a server may remind me of Monet’s paintings, our tools will consist not of brushes and paints, but of accounts, passwords, and permissions. Before we move on to other chapters in which we dig into the details of how SQL Server implements security, let’s look at what is available to help us allow the good people in and keep the bad people out.
Authentication and Authorization Every discussion of security concerns the twin processes of authentication and authorization.Authentication refers to the process of identifying a user, and authorization refers to the process of determining what that user can do. For SQL Server, authentication occurs both during initial login and each time a user attempts to use a database for the first time during a session. Authorization occurs every time auser attempts to perform any operation within a database. Authorization will also come into play any time a user attempts to change SQL Server’s configuration, use a system stored procedure, make changes to database configurations, and so on.
For authentication, which Chapter 2 covers, there are five server scenarios that are possible with SQL Server 6.5, 7.0, and 2000 running on Windows NT and 2000:
- SQL Server 6.5 on Windows NT
- SQL Server 7.0 on Windows NT
- SQL Server 2000 on Windows NT
- SQL Server 7.0 on Windows 2000
- SQL Server 2000 on Windows 2000
Fortunately, all but the last scenario use basically the same mechanisms to authenticate users. It is only when I cover SQL Server 2000 running on Windows 2000 that I need to expand the discussion to encompass the new security features in Windows 2000.
Authorization is easier to cover because there is no operating system–based difference in the authorization process between Windows NT and Windows 2000. However, SQL Server 6.5 has important differences from SQL Server 7.0 and 2000, so I cover them in separate chapters.
To get started, I’ve created a security roadmap (see Figure 1-1) to help you keep track of where you must make decisions about which feature to use.
Figure 1-1. The security roadmap
This is a picture I keep in my head when I’m troubleshooting server access problems or trying to determine what permissions a user has in a database. Each section represents a different place where you can control access. In the next few sections, I put all the security mechanisms into the context of this picture, so that hopefully at the end of this chapter you will have a sense of where each part fits into the overall scheme of managing SQL Server security.
 This chapter is from SQL Server Security Distilled, by Morris Lewis (Apress, 2004, ISBN: 1590592190). Check it out at your favorite bookstore today. Buy this book now.
|
Next: Options for Authentication >>
More Windows Security Articles
More By Apress Publishing
/ 2 
