If you care about your data, you must upgrade to SQL Server 2005 the day it is released. There simply is no other option.
An outrageous assertion? Perhaps. I tend to split my time equally between praising and bashing Microsoft, but the new security features and tools in SQL Server 2005 will be mandatory for protecting your data from today's increasingly sophisticated attacks. Most importantly, SQL Server 2005's many layers of security provide for defense in depth in which layer after layer of protection helps keep data safe.
This isn't to suggest that simply moving to the new database will automatically and fully protect your data. You'll still need to design database applications wisely and administer them proactively, knowing how to avoid creating custom security holes. But SQL Server 2005 provides the tools for making your data as safe as it can be against modern, sophisticated attacks.
SQL Server 2005 is the first major release of the product since Microsoft got the security religion and began implementing its Trustworthy Computing vision. And boy, does it show!
In this article, I will explore several of the security features that are of interest to developers designing and implementing SQL Server-based applications. So, for example, I won't talk in any depth about server deployment and DDL triggers, since those are of more interest to administrators. I won't specifically cover the SQL Express editions, which replace the hard-to-use-and-deploy MSDE. I also won't cover ADO.NET 2.0. Much of what I'll cover here applies to these peripheral technologies as well, but their security issues deserve separate articles. I'll focus on the core database engine here, since the major add-on features such as Reporting Services and Service Broker each have their own set of security issues and features. Hopefully I can drill down into these other features in the months to come.
I'll start out with the features that help secure the database since that is what is most important to most developers. Then I'll look at some server-level features that will affect how you write your applications and I'll finish this article with a quick summary of other security features you should at least be aware of.
This article is based on the state of SQL Server 2005 in the June Community Technology Preview release. This release is allegedly close to what the final shipping version will look like, but SQL Server Books Online still has many placeholders for information to come.
The SQL Server 2005 Security Philosophy
SQL Server 2005 is the first major release of the product since Microsoft got the security religion and began implementing its Trustworthy Computing vision. And boy, does it show!
There are four pillars on which the security features in SQL Server 2005 are founded, which you'll find traces of in every nook and cranny of the product:
- Secure by design. Microsoft performed thorough threat analyses that attempted to reveal every security vulnerability exposed to contemporary attacks. They took what they learned and made vast improvements to the “attack surface” exposed by SQL Server.
- Secure by default. Out of the box in a default installation, SQL Server 2005 is far more secure because many non-critical features are left uninstalled by default and those that are installed are turned off. This means that to use Reporting Services, for example, you have to explicitly install it. As a result, undiscovered security problems in features that are either not installed or are not activated do not provide an attack vector.
- Secure in deployment. Microsoft is providing various tools that help you make the right decisions about configuring the server and then monitoring it to make sure that it remains secure. For example, you can use the SQL Server Configuration Manager to set multiple settings to secure the database server. SQL Server 2005 is also going to be part of the Microsoft's online update services so that you don't have to manually download and apply security patches and service packs.
- Communications. Microsoft has pledged to make timely communication of threats, countermeasures, and tools so that you can respond to new and emerging threats.
Okay, this all sounds great and makes for some marvelous marketing material and conference demos. But let's step back a moment and run the B.S. detector over this stuff…. Surprisingly it rates midway on the scale, an historic low for Microsoft, even for server products where it tends to be more candid than other product classes. So what is the truth?
The fact of the matter is that SQL Server 2005 is an incredibly complex piece of software that is impossible to make 100% secure. You have my ironclad guarantee that there will be security issues that arise within days of its release. The issues will be a mixture of creative new attacks that no one can even envision today as well as attacks that probe old security problems that we all thought were fixed long ago. And even if SQL Server were perfect, inexperienced developers who are under intense deadline pressure will unwittingly build custom security holes in their applications that provide attack vectors that attackers dream about.
What's different with SQL Server 2005-and why my opening statement is sincere-is that Microsoft seems to have produced a marvelously secure product that doesn't provide easy attacks and has made it resilient enough to provide defense in depth so that one security flaw that leads to one successful attack doesn't mean complete collapse and automatic data loss.
The bottom line: It is far more likely-dramatically more likely-that you'll be able to withstand attacks using SQL Server 2005 than any prior version of the product. No guarantees, of course, but at least attackers are going to have to work far harder to get at your data.
Let's look at why this is the case, starting at the database level.
Database Security Features
The database is the central concern of data-based applications. As far as the typical application knows, the server is just a name in a connection string, the gatekeeper that makes it sometimes difficult to get at precious data. There are three major features in regards to database security that I find most interesting as a developer: data encryption, execution context, and user/schema separation.
Native Data Encryption
I once thought that encrypting data stored within any of the industrial-strength database engines was a total waste of processing cycles and storage space. The data is stored in the digital equivalent of a well-protected vault that made it virtually impossible to get past the guard, right? Wrong, of course. SQL Server 2000 proved to have way too many ways of circumventing its authentication protections, starting with the very common blank sa password. And while SQL Server 2005 is far more secure than its predecessors, it is still possible that an attacker will get access to the stored data. Thus, encrypting data becomes a defense in-depth strategy that provides a layer of protection of last resort. Even if an attacker gets access to the data, she still has to decrypt the data, which is neither an easy nor quick chore.
Encryption makes incredible demands on the processing power of a server, so encrypting every field in every row in a 10 million row table and then doing a SELECT without a WHERE clause is likely to bring the server to its knees. But it allows some interesting security options, such as code signing that allows access to resources only through code such as a stored procedure.
In SQL Server 2000 you had to either purchase a third-party product to encrypt data, make COM calls outside the server, or perform the encryption in the client application before sending it to the server. Worst of all, it also meant that you had to take responsibility for protecting the keys or certificates used to encrypt the data. Protecting keys is the hardest thing to get right with encryption, so many applications had weak data protection even though the data was strongly encrypted. I wince whenever I see a private key embedded in an application EXE or DLL file!
SQL Server 2005 solves these problems by making encryption a native feature of the database, with rich support for key hierarchies and many encryption algorithms. The flexibility is impressive, and best of all you can opt to have the database server manage all your keys for you.
SQL Server 2005 provides all of the most common types of encryption, along with a rich selection of algorithms. I'm not going to go into the details of how all these options implement encryption in this article, but here is a quick summary of encryption support in the server:
- Symmetric Key Encryption uses the same key for both encryption and decryption, making the key a shared secret. Normally this kind of encryption is difficult to implement in an application because sharing the keys in a secure way is hard. But this type of encryption is ideal when the data is stored in SQL Server and you let it manage the keys. SQL Server 2005 provides the RC2 and RC2 algorithms as well as the AES and DES families of algorithms.
- Asymmetric Key Encryption uses a public/private key pair so that the public key can be widely shared and revealed. This form of encryption is handy when you need to transmit data in encryption form outside of the server. SQL Server 2005 provides RSA with 512-, 1,024-, and 2,048-bit key lengths.
- A certificate is a kind of asymmetric key encryption, but a digital certificate protects the public and private keys, as a way to associate a private key with its owner. SQL Server 2005 supports the Internet Engineering Task Force's X.509v3 specification. You can have SQL Server generate certificates for its own use or you can import certificates provided by trusted outside certificate authorities.
As I mentioned earlier, keeping keys secure is the hardest thing about encryption to get right. SQL Server 2005 provides a key hierarchy to protect keys it uses both internally and to protect your data, shown in Figure 1.
In the figure, an arrow points from a key or service used to protect the key pointed to. So a service master key protects database master keys, which in turn protects both certificates and asymmetric keys. Symmetric keys can be protected by certificates, asymmetric keys, or other symmetric keys (as shown by the arrow that points back at itself). And you can take responsibility for protecting the key secrets of any of these keys in the database by supplying a strong password.
The root of the key hierarchy (shown, as is usual in such diagrams, at the top) is the Service Master Key. This is created and installed automatically when you install a new instance of SQL Server. You cannot eliminate this key, but administrators can do basic maintenance tasks such as back it up to an encrypted file, regenerate it if it is ever compromised, and restore it. (Regenerating a service master key is not something you should take lightly or do often. It could require decrypting and re-encrypting all of the encrypted data in the database, a painfully slow and processor-intensive process.)
The service master key is managed by DPAPI, the Data Protection API, which was introduced in Windows 2000. Built on the Crypt32 API in Windows, it strongly encrypts keys and stores them safely. SQL Server 2005 manages the interface with DPAPI for you, so you don't need to worry about it at all.
The service master key is a symmetric key and is used to encrypt any database master keys on the server. Unlike the service master key, you have to explicitly create a database master key before encrypting data in the database. Normally you'll supply a password when you create this key so that it is encrypted with both the service master key and the password. This allows you to explicitly open the key if necessary, but can also have it opened automatically, assuming you have the credentials to do so. Here's the T-SQL code for creating a database master key.
USE EncryptionDB
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'UTY6%djzZ8S7RyL'
There are various ALTER MASTER KEY statements for dropping encryption by the service master key or the password, changing the password, or dropping the database master key entirely. Normally you won't have to worry about those options, or at least you can let your favorite DBA handle it. You can only have one database master key per database.
Once you have the database master key, you're ready to start encrypting data. T-SQL has features built into it that support encryption, such as CREATE statements to create the various keys and ALTER statements to modify them. The actual encryption is performed with new paired sets of functions such as EncryptByKey and DecryptByKey for symmetric key encryption.
I suspect that a best practice will eventually emerge that you should use either EXECUTE AS ‘user’ or EXECUTE AS OWNER, but time will tell if such a recommendation emerges.
Let's look at an example of using encryption. I'll use symmetric key encryption here because I think that will be the most common form of encryption that is written to the database and read from it in clear text. Imagine that you have a Customer table with a few typical fields: customer ID, name, city, and various credit card details. The credit card details should be encrypted but the other data doesn't need to be. Imagine also that User1 owns the symmetric key and that the login causing this code to run has the required permissions to encrypt data using this key.
Start by creating a symmetric key in the database containing the Customers table, using Triple DES as the encryption algorithm. In this case, the key is itself protected by a certificate that already exists in the database. Referring back to Figure 1, symmetric keys can also be protected by asymmetric keys and existing symmetric keys.
CREATE SYMMETRIC KEY User1SymmetricKeyCert
AUTHORIZATION User1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE User1Certificate
A symmetric key must be explicitly opened before use. This step retrieves the key, decrypts it, and places it in protected server memory, ready for use.
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate
Finally you're ready to encrypt the data. In the code below, I use a regular T-SQL INSERT statement to put a row of data in the table. The id, name, and city are saved as clear text but the credit card type and number, as well as some potentially confidential notes about the customer, are stored in encrypted form, using the strong Triple DES encryption algorithm specified when the key was created.
INSERT INTO Customer
VALUES (4, 'John Doe', 'Fairbanks',
EncryptByKey(Key_GUID(
'User1SymmetricKeyCert'), 'Amex'),
EncryptByKey(Key_GUID(
'User1SymmetricKeyCert'),
'1234-5678-9009-8765'),
EncryptByKey(Key_GUID(
'User1SymmetricKeyCert'),
'Window shopper. Spends $5 at most.'))
When you're done with the symmetric key, close it to release the memory and prevent it from being misused.
CLOSE SYMMETRIC KEY User1SymmetricKeyCert
That's all there is to it! No messy key management, no complicated calls to algorithm-specific methods. Doing a normal SELECT * on the table produces the results shown in Figure 2. The fields that store encrypted data are varbinary types of a length sufficient to hold the expanded data (cipher text takes up more room than clear text, sometimes much more).
To read the data as clear text, you need to re-open the symmetric key (if you've closed it), use the DecryptByKey function to read the text, and close the symmetric key. Figure 3 shows the resultset.
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType))
AS CardType,
CONVERT(VARCHAR, DecryptByKey(CreditCardNumber))
AS CardNumber,
CONVERT(VARCHAR, DecryptByKey(Notes)) AS Notes
FROM Customer WHERE CustID = 4
CLOSE SYMMETRIC KEY User1SymmetricKeyCert
This example shows one of many ways to let SQL Server 2005 manage encryption keys for you. But you virtually always have the option of taking on the chore yourself by supplying a strong password. So you could have instead created a symmetric key that uses the RC4 encryption algorithm.
CREATE SYMMETRIC KEY User2SymmetricKeyPwd
AUTHORIZATION User2
WITH ALGORITHM = RC4
ENCRYPTION BY PASSWORD = 'sdylvxF&imeG3FP'
SQL Server creates a key based on the password you supply and encrypts the data. Just be sure to keep the password a secure secret, otherwise anyone can decrypt the data. It is not stored within SQL Server unless you explicitly store it.
Data encryption in SQL Server 2005 is a marvelous feature; one that provides a significant layer of defense that protects your data. But don't get carried away! Protect only the data that is sensitive or confidential enough to require this level of protection, or that you have a statutory requirement to encrypt. Encryption makes incredible demands on the processing power of a server, so encrypting every field in every row in a 10 million row table and then doing a SELECT without a WHERE clause is likely to bring the server to its knees. But it allows some interesting security options, such as code signing that allows access to resources only through code such as a stored procedure.
Change the Execution Context
One of the interesting problems with stored procedures in SQL Server 2000 and earlier was that the code executed with the security context of the owner of the procedure. Developers regularly rely on the fact that all of the resources used by a stored procedure-tables, views, etc.-are owned by the owner of the stored procedure that is accessing the resources. This is called an unbroken ownership chain. The reason that an unbroken ownership chain is convenient is that SQL Server will verify only that the caller of the stored procedure has execute permission on the procedure. With an unbroken ownership chain, it doesn't bother checking permissions on the underlying objects, since it assumes that it is okay for the owner of the procedure to give access to other objects it owns.
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.
If the ownership chain is broken-the procedure accesses objects not owned by the procedure's owner-then SQL Server has to verify that the caller has permissions on all of the underlying objects. This is why executing dynamic SQL within a stored procedure requires that the caller have permissions on the objects accessed by the dynamic SQL. This is why Microsoft has long recommended that all tables and objects in a database be owned by the special dbo user. Since all objects are owned by that single user, you always have an unbroken ownership chain.
But this scheme places some rather stringent limitations on the security design of an application, preventing granular permissions to protect various objects throughout the database. If dbo owns everything, you can't easily grant true ownership to different users or groups who ultimately have responsibility for the objects. You also can't take full advantage of ownership chains to protect resources. You also can't make use of fine-grained permission schemes and control the security context of a procedure; the permissions of the owner is the only security context that really matters. These issues caused developers and admins to take other, messier measures to protect objects in the database. (By the way, this discussion applies to user-defined functions-except inline table-valued functions-and triggers as well as stored procedures. To keep the discussion simple I'll just refer to stored procedures.)
SQL Server 2005 introduces the concept of execution contexts to overcome these limitations. The idea is that the creator of the procedure-who may or may not be the ultimate owner-can specify the security context of the procedure. The context can be that of a login, role, or database user so you can create users in a database that may or may not map to a server login but which have a fine-grained set of permissions on various objects. The only real limitation is that the creator of the procedure must have permission to impersonate the principal specified as the execution context.
Here's how it works. Say that you have a database table VendorTable that contains some information about vendors, such as an ID, vendor name, state, and phone number. User TestUser has SELECT permission on the underlying table, but ProcOwner has impersonation rights on TestUser. The following code executes with ProcOwner logged into Management Studio, SQL Server 2005's development environment.
CREATE PROC GetVendorByState
@state char(2)
WITH EXECUTE AS 'TestUser'
AS
EXEC ('SELECT * FROM VendorTable WHERE state =
'''+ @state +'''')
GO
Obviously there is no real need to use dynamic SQL in this example, but it makes painfully clear that the ownership chain is broken, and SQL Server will have to check permissions on the underlying table. Once this code executes, ProcOwner can assign execute permission to whomever it wants, and the procedure will run not under the security context of the caller but of TestUser. TestUser will have to have SELECT permission on the VendorTable.
The code above executes as a specific user, which is but one of four ways you can use the EXECUTE AS clause:
- EXECUTE AS CALLER means that the security context will be that of the caller of the procedure or function. In this case, there must be an unbroken ownership chain or else the caller must have the required permissions on all objects used. This is the default execution context for backwards compatibility to match how SQL Server 2000 and earlier works.
- EXECUTE AS ‘user’ is the form used in the sample code above. The permissions of ‘user’ are used to verify that all objects are accessible. The creator of the procedure or function must have IMPERSONATE permissions on the specified user, although you never need this permission to impersonate yourself.
- EXECUTE AS SELF is just a shorthand notation for the creator of the procedure. So if user ProcOwner creates a procedure with this form of execution context, the security context of ProcOwner will be used to check permissions. Note that the actual user name is associated with the procedure, not some abstract SELF user. If ownership changes and you want the new owner's security context to be used, you'll have to alter the procedure after changing ownership.
- EXECUTE AS OWNER uses the security context of the current owner of the procedure at time of execution. This is probably a better option than EXECUTE AS SELF for the reasons explained in the previous bullet point. This option is appropriate when you want to change owners and use the current owner's permissions, without changing the code.
I suspect that a best practice will eventually emerge that you should use either EXECUTE AS ‘user’ or EXECUTE AS OWNER, but time will tell if such a recommendation emerges.
Changing the execution context of code modules is a great feature for implementing least privilege practices in database applications, letting you create users that have exactly the permissions to do a specific job and no more. But like most such features that provide almost unlimited flexibility, it is easy to get carried away and obfuscate the database's security design so much as to make it impossible to maintain. Use this feature with careful thought and design, and you'll significantly improve database security.
Separating User from Schema
One of the strange things about how SQL Server 2000 and its predecessors work is demonstrated in the code below. Assume that User1 is logged in.
CREATE TABLE table1 (tID int)
GO
SELECT * FROM dbo.table1 -- Error!
SELECT * FROM table1 -- User1 can run
SELECT * FROM User1.table1 -- Runs
Assuming that User1 is not the database owner or a member of the System Administrators role, a table called User1.table1 is created by the first line of code. Trying to select data and referring to dbo.table1 doesn't work, since no table by that name exists. Only User1 can select from the table using just the name table1 since SQL Server helpfully tries to prepend the current user's name as one of the options it tries. For any other user, table1 isn't found. Finally, anyone with the proper permissions can select from User1.table1, since that is an unambiguous object name in the database.
If the current user is the database owner or a member of System Administrators, the name automatically becomes dbo.table1 unless you explicitly create the table as User1.table1.
The strange thing about this is that a specific user's name is part of the qualified name of the object. This causes all kinds of problems when you have to change ownership of an object. Say that Bob originally created the table so that its name is Bob, making the object name Bob.MyTable. During his time on this project Bob creates hundreds of other objects, including other tables, stored procedures, views, and so on.
Now it's time for Bob to go on to greater things (management, no doubt), and Floyd steps into Bob's role on the project. The problem is that the system admins want to remove all permissions in the database and remove the user Bob from access, in case Bob later gets mad at the company and vandalizes its databases to inflict revenge. But until every one of those hundreds of objects is assigned to a new owner-presumably Floyd or, if the admins have learned their lesson, a database role-Bob can't be dropped as a user. Even with an automated scripting tool, that can take some time. And any references to Bob.MyTable and all his other object references in applications distributed around the company and its customers will also have to be updated. In short, this is a nightmare. This is another reason why Microsoft has long recommended that all objects be owned by dbo and security implemented in other ways.
The reason for this mess is that SQL Server 2000 and earlier blurred the distinction between users and schemas. More specifically, a user and a schema are one and the same thing. Consider Figure 4, which shows the situation in SQL Server 2000. When Carol logs into the database and creates an object as a non-admin and non-owner, the object name is prefixed with Carol. Conceptually SQL Server created a schema that hid behind the user, but you really couldn't get at the schema and modify it independent of the user. As a result, changing ownership requires touching every object and all the code that uses those names.
As defined in the SQL-99 specification, a schema is essentially a container for objects in the database. It then may, in turn, be owned by a principal, as shown in Figure 5. Now when Carol leaves the company, admins only have to change the ownership of any schemas Carol owns, each of which could have thousands of objects. (The schema name is unlikely to have the user name in it, but you can name them any valid SQL Server object name.) This approach is much cleaner, much easier, and much more secure.
SQL Server 2005 no longer automatically creates a schema with the same name as the user when you create a user. Instead, you have to explicitly create a schema, assign ownership to a user, and then you can create and add objects as part of that schema. You can (and usually should) assign a default schema to a user so that all objects the user creates and doesn't explicitly assign to another schema become part of the default schema.
Listing 1 shows how this works. After changing to the pubs database (which isn't included as one of the SQL Server 2005 sample databases, but you can easily add it), the code creates the login carol, maps it to the user carol in the pubs database, and grants it the ability to create tables. It then changes the execution context to the new user carol.
CREATE LOGIN carol WITH PASSWORD = 'carolPWD'
CREATE USER carol FOR LOGIN carol
GRANT CREATE TABLE TO carol
EXECUTE AS LOGIN = 'carol'
GO
The code next attempts to create table1. But when the code created carol it didn't assign a default schema. SQL Server attempts to use the dbo schema, but Carol doesn't have ownership rights in the database so the CREATE TABLE statement fails.
CREATE TABLE table1 (tID int)
After reverting to the original admin login that started this session, the code creates a schema and gives ownership to user carol. You'll see the AUTHORIZATION clause a lot in SQL Server 2005 because it lets you assign ownership in the same statement that creates or alters an object.
CREATE SCHEMA CarolSchema AUTHORIZATION carol
The code then once again changes the execution context to carol and attempts again to create table1.
CREATE TABLE table1 (tID int)
But, damn, it fails again! The problem now is that just because a user owns a schema doesn't mean that it's the user's default schema. A user could own hundreds of schemas and SQL Server shouldn't be responsible for picking one to be the default. But what does finally work is creating the table to be explicitly contained within the schema.
CREATE TABLE CarolSchema.table1 (tID int)
Success at last! Listing 1 shows the complete code, including the execution context switches, to make this all happen.
The second attempt to create the table, once CarolSchema existed, would have succeeded had the code assigned a default schema, either when initially creating the user or by later altering it.
CREATE USER carol FOR LOGIN carol
WITH DEFAULT_SCHEMA = CarolSchema
-- or
ALTER USER carol WITH DEFAULT_SCHEMA = CarolSchema
By the way, don't be confused by the CREATE SCHEMA statement in SQL Server 2000. That statement simply provided a streamlined means of creating tables and views owned by a specific user and giving the user permissions on the objects. It hinted at the blurring of users and schemas but is not the same thing at all.
Like many other new statements in SQL Server 2005, the CREATE and ALTER USER statements supplant system stored procedures that do similar things. In this case, the statements replace procedures such as sp_adduser and sp_addapprole. These procedures are still around for backward compatibility but have been modified somewhat to support the user/schema separation. They create a schema with the same name as the user and make it the default schema so as to imitate the behavior of SQL Server 2000. I recommend that you use the new statements and make it all explicit, however, rather than hiding schema creation.
The separation of users and schemas in SQL Server 2005 is yet another way that you can keep tighter control over the security architecture of your databases and applications. It most certainly makes it easier to administer a database and SQL Server. You shouldn't need to have dbo own everything anymore.
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.
Granular Permissions
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 role?and thereby granting entire sets of privileges-just 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:
Windows-level principals
- Windows Domain Login
- Windows Group
- Windows Local Login
SQL Server-level principals
- 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
Database-level principals
- 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. Table 1 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 control-and they do in the sense that you can control objects in very flexible ways?but 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.
Metadata Security
Another one of the security problems with SQL Server 2000 and its predecessors is that any user with access to the server or a database could view the metadata for the server or database. In other words, query access to most system tables was unrestricted, simply because the permissions infrastructure in earlier versions of SQL Server wasn't capable of keeping track of who had what kind of permissions on what objects. The result is that once a hacker got almost any kind of toehold into the server or a database, she was able to explore the structure almost without constraint-a huge security problem.
Two things have changed in SQL Server 2005 to relieve this problem. First, the granular permissions that SQL Server now has, allowing fine-grained control over what principle has what permissions on what objects. Second is that the system tables as we have grown to know and hate them are largely gone from SQL Server. (They are still around for backward compatibility but have not been enhanced to support new features.) In their place are a set of catalog views that provide most of the same functionality as querying a system table in SQL Server 2000. For the most part the names are similar to the old system tables, with names like sys.columns, sys.databases, and sys.servers, along with many new views to expose information about new features. Hopefully you now recognize that sys is a system-level schema that contains the system catalog views.
Using catalog views instead of relatively direct access to system tables has two important effects, one good and the other really good. First, because the catalog views are read-only, you won't have to worry about learning all kinds of new hacks to directly modify system tables and potentially crash the server. Second, SQL Server can filter the results of querying a view to display information for only the objects that the caller has permission to see. This feature is officially called Metadata Visibility Configuration. For example, if the PlainJane user has access to a database but no permissions on its Customers table, a query to sys.tables in that database will show nothing about the Customers table. As a result, a hacker that gets access to the database by hijacking the PlainJane user without ownership or admin privileges will never know about the Customers table.
Even better, an attacker can't even guess about the existence of a Customers table. A blind attempt to drop the table receives this error message, which neither confirms nor denies the existence of the table.
Msg 3701, Level 14, State 20, Line 1
Cannot drop the table 'Customers', because it does not exist or you do not have permission.
This level of protection extends to system stored procedures that expose information about objects, such as sp_help and sp_helpdb.
By itself, metadata security won't directly prevent attacks. But it does mean that when someone does get unauthorized access to the server or a database it will be much harder to find juicy data or worthy attack targets.
What Else Is There?
I've barely been able to scratch the surface in this article about the security enhancements in SQL Server 2005. But hopefully you now have a feel for just how fundamentally more secure this version is, with plenty of options for locking down the server, databases, and the data within. There are plenty of other security features to explore, such as:
- Secure deployment of the server, with all but the most critical, core database features either not installed or turned off. For example, CLR integration is turned off so that you have to explicitly turn it on to be able to run .NET code within the server.
- Strong password policies that are tied into the Windows password policy. If you use SQL logins, you can have minimum password complexity standards, expire passwords periodically, and lockout a user after too many incorrect login attempts.
- Strong authentication for HTTP endpoints if you use SQL Server as a Web service endpoint in a Service Oriented Architecture (SOA) application.
- A new Computer Manager tool that replaces the Client Network Utility, Network Utility, and Service Manager.
- A dedicated administrator connection that allows access to a server even when it is otherwise not responding or is unavailable.
- New SQL Management Objects (SMO) that replace SQL-DMO and provide programmatic access to every nook and cranny of SQL Server, including security settings.
- New proxy accounts for SQL Server Agent and flexibility on permissions.
- Code signing using encryption that prevents anyone from getting access to tables and other securable objects other than through the signed code.
- DDL triggers that fire when the target object is changed, such as when a field is added to a table. This can help detect unauthorized changes to the database by attackers or by well-meaning but destructive power users.
- Database snapshots that let you quickly recover from problems, security-related or not.
- CLR security that addresses the nervousness that some database administrators have confessed to when contemplating the prospect of .NET code running inside a database. From a developer's perspective, this is a cool feature in how it blends the security infrastructure of SQL Server with that of .NET's Common Language Runtime (CLR).
- And, at the risk of offending the cliché police, much, much more!
The release of SQL Server 2005 in early November will present a brave new world for developers and administrators who have to deal with protecting data. SQL Server will be newly equipped to withstand the most insidious of today's attacks and ready to adapt to those that we haven't even dreamed of yet. Assuming that Microsoft comes through on all of its promises, data will have never been so safe.
Listing 1: Separation of users and schemas in SQL Server 2005
-- Log in using an admin account initially
USE pubs
CREATE LOGIN carol WITH PASSWORD = 'carolPWD'
CREATE USER carol FOR LOGIN carol
GRANT CREATE TABLE TO carol
EXECUTE AS LOGIN = 'carol'
GO
CREATE TABLE table1 (tID int)
-- Error: Don't have permission to create object in dbo schema
-- Revert to admin
REVERT
-- Create schema and link to login
CREATE SCHEMA CarolSchema AUTHORIZATION carol
EXECUTE AS LOGIN = 'carol'
GO
CREATE TABLE table1 (tID int)
-- Still an error. Just because there is a schema, doesn't mean
-- it's the default
CREATE TABLE CarolSchema.table1 (tID int)
-- Success at last!
REVERT
