Server Security Features
Normally the database is the scope of concern for the database application developer. But few things exist in a vacuum, and a database and the connections to it have to obey the laws of physics, er, the server. There are two server-level features that a developer will have to deal with in a secure SQL Server 2005 database environment-highly granular permissions and restricted access to metadata through system catalog views.
The single most important security enhancement in SQL Server 2005 at the server level is the new, highly sophisticated, permissions framework. Instead of making a user a member of a roleand thereby granting entire sets of privilegesjust so that they have just one specific permission, you can almost always grant precisely the permission that the principal needs for a specific need. This change lets you fully comply with the security principle of least privilege.
Let's start with what hasn't changed in SQL Server 2005. It uses the same GRANT, DENY, REVOKE structure for granting permissions or preventing a user from getting them. The set of fixed server and database roles is unchanged, but you'll be using them far less often. Many of the basic data and database object permissions are the same, such as GRANT SELECT.
Beyond that, there is a lot that's new. This section will just touch on the major changes, hopefully enough to whet your appetite and really start to understand why I think that anyone with important data to protect should move to SQL Server 2005 as soon as practical.
To start, SQL Server 2005 has the concept of a principal. A principal is not a new concept in Windows security, but in SQL Server it represents any individual, group, or even a process that can access a protected resource. Most often a principal will be a user, but keep in mind that it can be other things as well. Here is a list of the non-process principals in SQL Server 2005:
SQL Server-level principals
- Windows Domain Login
- Windows Group
- Windows Local Login
- SQL Server Login
- SQL Server Login mapped to a certificate
- SQL Server Login mapped to a Windows Login
- SQL Server Login mapped to an asymmetric key
- Application Role
- Database Role
- Database User
- Database User mapped to a certificate
- Database User mapped to a Windows login
- Database User mapped to an asymmetric key
- Public Role
A principal can request and receive or have denied permission to access a protected object on the server or the database. The list above shows the hierarchy of securable objects that are protected at the server, database, and schema levels.
There are far too many permission types to include here, so see SQL Server Books Online for the exhaustive list. But there are several new permission types that will give you an idea of just how granular permissions can be:
- CONTROL. This permission grants the principal receiving it control as though the principal owned the object. The granddaddy of the all is the CONTROL SERVER permission, which is the equivalent of System Administrator privileges.
- ALTER. The statement granting this permission specifies a specific object and allows the principal to change any property of the object other than ownership. It implies ALTER, CREATE, and DROP permissions on other objects within the scope of the securable object.
- ALTER ANY object. The ANY keyword is an interesting variation that allows the principal receiving the permission to alter any object of the type specified. For example, ALTER ANY LOGIN at the server level lets the principal change any login on the server.
- IMPERSONATE ON user. The permission needed by the creator of a stored procedure or other code to use EXECUTE AS to switch the security context at runtime.
- TAKE OWNERSHIP. This permission allows the principal to take ownership of the specified object but does not, by itself, transfer ownership. The principal must follow up to actually take ownership.
Permissions in SQL Server 2005 can get confusing quickly because granting a particular permission can imply the conveyance of other permissions. For example, granting the ALTER
permission on a schema implies CONTROL
permission on the schema, ALTER ANY SCHEMA
permission in the database, ALTER
permission in the database (so that you can alter the objects contained within the schema), and others.
I think that while such implying permissions are a logical effect of granting permissions, this is going to be a big source of confusion. The permission statements imply fine-grained controland they do in the sense that you can control objects in very flexible waysbut you have to be careful that you are not granting a permission with far wider scope than you anticipated.
Despite the potential confusion, the granular permissions of SQL Server 2005 finally let developers and administrators implement the principal of least privilege in databases and applications. Never again will you have to grant membership in a role with many far-ranging privileges to convey only a narrow permission needed by a user. But with all these new permissions, you'll have to carefully design the security and permission architecture of your database to make sure that you still don't give away too many permissions through implication.