O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

9.1. Security Principles

When it comes to securing any system, several security principles provide the best results. The ones we talk about with respect to securing your SQL Server are as follows:

  • Principle of least privilege

  • The CIA triad

  • Defense in depth

9.1.1. The Principle of Least Privilege

The principle of least privilege is a security best practice which states that a person should be given only the rights he or she needs to do the job, and no more. The reasoning behind this is simple: If a person makes a mistake, the amount of damage that can be done is limited. If the person is malicious, or if a malicious person compromises another individual's account, granting the original person only the rights needed to do the job will limit the potential impact of an attack. In a classic scenario, suppose a user needs read access to a few tables in a database in order to create reports for the executive staff. The "easy" solution is to make the user a member of the db_datareader fixed database role. Unfortunately, this role gives read access to all tables and views in a database. Therefore, the user would have access to more data than he or she needs. Following the principle of least privilege, a database role should be created with SELECT permission granted against only the tables the user needs. The user should then be made a member of this new role.

The principle of least privilege also applies to applications, protocols, and services. For instance, it should be extended to service ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required