Chapter 8: Security

By now you will have realized that using stored procedures is the best way for making data available from an SQL database. In the earlier chapters, we have seen that there are several reasons for preferring this approach. One such major reason is security. In this chapter will examine this aspect of SQL Server stored procedures.

We will first analyze security threats and the core security requirement of SQL Server – allowing data access only to explicitly authorized users. Then, we will look at the proper implementation of stored procedures for adequately countering these threats. Finally, we will examine some general methods for securing our SQL Server implementation; we will be giving special emphasis on SQL Server stored procedures and the system stored procedures that Microsoft ships with SQL Server.

Increased Need for Security

Whenever we deal with a threat, we not only analyze the degree of the vulnerability, but also the gain-to-loss ratio that a hacker has for taking advantage of that vulnerability. If the gains are high and the losses are perceived to be low, the hacker is more likely to hack the system. Note that, the gains can be subjective or abstract. Until a few years ago, the vast majority of SQL Server applications were client-server applications, which were totally contained within the firewall of an organization. In such a scenario, the potential hacker was an employee of the company itself. The gains in successfully hacking the application and not getting caught were significantly counter-balanced by the perceived loss of losing their job and the fear of being prosecuted.

However, in today's web-enabled world, two significant things have happened:

• The quality of the data that we are collecting is getting both more valuable and more liquid (for example, credit card numbers).

• Secondly, with the increasing penetration of the Internet, the potential hacker is sitting miles away and has no direct relationship with the organization. You can't fire them, since they usually aren't an employee or contractor. Getting their name, address, and phone number to turn them over to a law enforcement authority is much more difficult.

As a result, the gains for the hackers have gone up considerably while the perceived losses have gone down dramatically. In such a scenario, hacking is bound to be more attractive to a crooked conscience.