Chapter 5: Security

Overview

I have good reason to place security at the beginning of Part II. I have met many DBAs and developers who do not fully appreciate the security environment required in the implementation of SQL Server. There have been many horror stories: users running amok, unauthorized use of tools, external processes corrupting data, untrained administrators dropping databases and tables, theft, acts of God, self-mutating viruses, and so on, Here are two stories of many I have encountered in network and database administration.

I recently took over the administration of a database for a direct marketer. Now this person had been making millions selling mailing lists for almost 25 years but was not pleased with his “DBA.” During the needs or requirements analysis I was taking before providing a quote for my services, I discovered a table called CCN. Lo and behold, what I stumbled on was a gold mine of about 75,000 credit card numbers, linked of course to the customers who had been buying the database owner’s mailing lists.

I did a sort on the data and pulled about 25,000 current and active credit cards, so the database was valuable to a person of malevolent intent, to say the least. To the credit (no pun intended) of my moral virtue, I deleted the view, advised the owner of the risk, did a backup of the database and the system, and then locked down the machine. The table had been completely exposed; there was no proper domain or network security protecting the server, nor was a system administrator-password-protecting the DBMS. I told the owners that by the end of the week I would give them my proposal and a quote to take over administration of the asset

The next day I was called; the current DBA had vanished, and the database server with all the credit card information had vanished with him. I had never been hired faster in my life. The server, incidentally, was never recovered, but the credit cards were never compromised,

The second story was more recent. A client I was consulting for (a Fortune 500 company, and one of the world’s largest food distributors) suspended the credit of one of its yogurt ice-cream outlets, which threatened closure of the store because he was in a cash-flow crunch. But that did not deter the desperate ice-cream seller. He hired someone to hack into the company’s databases and managed to keep himself supplied for several weeks until the credit manager found out.

My guess was that the storeowner hired someone to do the hacking, but security was so lax that breaking into the database was easier than selling frozen yogurt on a hot day in Miami. I was called in to look for evidence, but not before the hacker managed to feed several thousand hungry yogurt lovers. The hacker got away clean, having also deleted the delivery information; we had no evidence that the deliveries had actually been made to his location.

I can tell you many more stories, but you don’t need a book to tell you that data security is one of the most important subjects in database administration. I find it odd it is not given more attention.

Many DBAs and developers are more concerned about data integrity than data security. I don’t see why the validity or plausibility of data should be deemed more important than avoiding compromise of it. After all, how valid is valid data when you don’t have it any longer or it has been seriously compromised? I am not underplaying data integrity, and you’ll find the chapter that deals with it (see Chapter 12) just as vociferous. I just know many DBAs out there that will tell you about the mother of all triggers or stored procedures they wrote, and yet they still manage a system with no system administrator password. So rife is this problem that Microsoft made a special effort in the SQL Server 2000 days to force us to use a password for the SA. And in SQL Server 2005 security is even more sophisticated.

This chapter looks at data security in general and how SQL Server 2005 presents itself as a bastion of the data it keeps and of the services it provides. It is a complex chapter for a complex subject, and you should read it before creating any facilities for logging in and using the database. And DBAs who have been baptized by fire will find discussions of new security services, especially with respect to Window Server 2003, refreshing.

If you need to configure SQL Server 2005 for secure access as quickly as possible, go directly to the section “Creating and Configuring Database Roles” and thereafter “Creating and Configuring Users.”