Microsoft is hard at work on SQL Server Denali, the next major version of SQL Server. Earlier this summer, Microsoft released Denali CTP3, which is not complete but offers a lot of interesting new features. It won't be a massive, earth-shattering update like SQL Server 2005 and SQL Server 2000, but there will be a lot of new stuff to learn.
As a developer security MVP, I’ve been exploring what’s new for security in this release. You might remember that security was almost completely overhauled in SQL Server 2005, because it was the first version since Microsoft awakened to the need for security and began its Trustworthy Computing initiative. You don’t hear or see that name much anymore, but there is no doubt that Microsoft takes security very seriously. I certainly appreciate this when so much of my data and my clients’ data is stored within SQL Server. In fact, I often find that security is the most compelling reason to upgrade to the latest version of SQL Server.
Denali will include security enhancements in four areas: security management, authentication, data protection, and auditing. I’m going to briefly explore what’s new in each of these areas. Most of these features can be administered or controlled by a DBA, but developers will have to understand the features to make applications work with SQL Server data.
Security Management
There are two new security management features in Denali. The first lets you set a default schema for groups, not just users. Schemas are a handy feature in SQL Server for partitioning database objects into namespaces and for assigning permissions across a schema. By extending support for default schemas to groups, it’s easier to use the appropriate schema in queries you’ve written. It also prevents the problem of creating implicit schemas when someone is sloppy with fully qualified names of objects. This was a large omission in SQL Server 2008, so it’s nice to see it filled at last.
The other major security management feature is user-defined server roles. At long last, you can create custom server roles and assign whatever permissions you want to them. This gives DBAs who manage the server instance and developers who need specific permissions on the server granular control over permissions. You can use a mix of GRANT and DENY permissions for a custom role to follow the principle of least privilege.
Authentication
The big news with authentication is SQL Server’s upcoming support for contained databases. A contained database is a completely self-contained database that includes all the database settings and metadata required to define the database. When you define a contained database, it has no dependencies on the instance of SQL Server in which it resides, even to the extent that users can log into the database rather than the SQL Server instance, using either a SQL Server login or Windows authentication. The user doesn’t log in via the SQL Server instance at all, although there are some interactions if the user has a login at both the server and database level. Contained databases make it easy to move the database to another instance of SQL Server.
The way authentication works is to create a database user either with a username and password or associate it with a Windows login. The newly-created user is not associated with a server-level login as was required in the past, and SQL Server authenticates the user against the database. Once successfully authenticated, you get a token good for that database only, and the security boundary for that user is tightly scoped to the database.
This contained database feature is going to open up a lot of nice scenarios for application development and database administration, so it will be interesting to see how Microsoft develops support for contained databases and how people will use them over time.
Data Protection
Encryption is one of SQL Server’s best features for providing in-depth data protection defense. Even if an attacker gets through the secure network, into the secure SQL Server instance, and gains access to your data, he or she still has to find a way to decrypt the encrypted data.