Chapter 4. Security

Unlike the ones that preceded it, this chapter deals with only one subject. Security has many facets, from the exterior of the system (called a surface) to the rights and privileges the users have on the objects the databases contain. Even so, we only scratch the surface (no pun intended) on the tools and features you have at your disposal to secure your system.

Throughout the chapter, I refer to "least privilege," which is a concept common to secure systems and applications. It means to allow only who is necessary to do only what is necessary, and it is the basis for planning an effective security strategy. In the military, this is called a "need-to-know" basis. In fact, many military concepts carry through to platform and database security.

In the preceding chapter, I explained how to create a maintenance plan, and in this chapter, you will find many concepts to create a security plan. You do not have to create a plan to implement good security, but having security documentation is useful for two reasons: It provides an audit trail, and it forces you to think through the process. Although optional for some, in other cases you may actually be required by compliance policies to have this documentation.

The security plan includes the surface area that is exposed to security risks and what you are doing to secure those areas. I show you how to identify both of those areas.

Common problems in database security are authorization issues, lack of auditing and enforcement, and data exposure. In this chapter, I will help you deal with these issues, and in the "Take Away" section, I show you a few useful methods for creating an automated auditing system.

As you read through this chapter, I ask you to "do as I say, not as I do." I explain how to configure your system a certain way or create complex passwords and other security measures, and then I use simple passwords or less-secure settings in my examples. The reason is that I want to make sure the examples are clear, so I might use the word password as a password. That is, of course, a bad idea, but it is for clarity. Keep that in mind as you work through the examples in this chapter.

There are two sections in this chapter: securing the system, and securing the application's database. These are actually two separate discussions; but in your role as a DBA, you will be responsible for both. I do not cover things such as routers and network demilitarized zones (DMZs) because those areas are normally handled by other groups in IT. I focus instead on the items that you are more likely to be responsible for.

Security has its own jargon, just like all the other IT disciplines. So that you can take what you learn here and refer to other publications, you need to learn a little of that jargon. Anything in the database that can be secured independently such as a table or stored procedure is called a "securable." Anything that can perform actions such as a user account, role, or service is called a "principal." Although I use the more familiar names of user and table throughout the rest of the book, in this chapter I make an effort to use the security jargon so that you can get used to it.

I first deal briefly with the areas outside the system that have security implications and then focus more in depth on the areas that DBAs are commonly asked to manage. I then describe the security within an application. Even there, the discussion does not deal with just one set of concepts, because there are multiple ways to secure access to data.

In addition to the platform and data access, you will often need to secure the data itself. In the preceding chapter, I explained how to place a password on a backup operation, but in some situations that is not enough. SQL Server 2005 enables you to encrypt data within a database. I explain data encryption in the second part of this chapter.