6. Security
CHANGES IN SQL Server 2005 help make SQL Server more secure and security more approachable for the developer and the administrator. An entire new set of security requirements when hosting .NET Framework code inside SQL Server is addressed by using traditional user- and role-based security combined with .NET Framework hosting APIs and attribute-based security. Classic SQL security is improved by separating users from schemas and integrating password management with Windows Server 2003, and the attack surface of SQL Server is reduced by having many options turned off by default.
New Security Features in SQL Server 2005
SQL Server 2005 adds new security features, not only to make SQL Server more secure, but also to make security more understandable and easier to administer. Some of these features will permit programmers to develop database applications while running with the exact privileges that they need. This is known as the principle of least privilege. No longer does every programmer need to run as the SQL Server administrative login, sa.
• Security for .NET Framework executable code—Administration and execution of .NET Framework code is managed through a combination of SQL Server permissions, Windows permissions, and .NET Framework code access security. What user code can or cannot do inside and outside SQL Server is defined with three distinct levels.
• Password policies for SQL Server users—If you run SQL Server 2005 on a Windows 2003 Server, SQL users can go by the same policies as integrated security users.
• Separation of users and schemas—SQL Server 2005 schemas are first-class objects that can be owned by a user, role, group, or application roles. The capability to define synonyms makes this easier to administer.
• Granting permissions—No longer do users or logins have to be in special roles to have certain permissions; they are all grantable with the GRANT, DENY, and REVOKE verbs.
• New security on SQL Server metadata—New metadata views are not directly updatable, and users can list metadata only about objects to which they have permission.1 There is also a new grantable VIEW DEFINITION permission.
• Execution context for procedural code—You can set the execution context for stored procedures and user-defined functions. You can also use the EXECUTE AS syntax to change the current user.
• Support of certificates and encryption keys—SQL Server 2005 can manage certificates and encryption keys for use with Service Broker, with Web Services Secure Sockets Layer (SSL), and for new data encryption functions.
• Code signing of procedural code—You can use a certificate or asymmetric key to sign procedural code.
We’ll look at these features in detail in this chapter. Running non–Transact-SQL (T-SQL) code in a secure and reliable fashion inside SQL Server is a new concept, and we’ll spend a lot of this chapter examining how SQL Server’s new security features combine with the latest .NET Framework runtime version to make this possible. We include a review of how security currently works in SQL Server in general to establish a baseline of knowledge.
We will start by looking at a change in the general policy of how SQL Server is configured. Microsoft refers to this as the 3 Ds: Secure by Design, Secure by Default, and Secure by Deployment. Though having optional features turned off by default technically is not a feature as such, most will consider it an important step forward in securing SQL Server.
Optional Features Are Turned Off by Default
The SQL Server product has always been known for being feature rich. A multitude of new features is added with each release, sometimes too many for many DBAs to keep track of. Although it’s a treat to see new features (that’s what this book is about, after all), in previous releases they usually arrived with the product, installed and enabled by default. And enabled features that you don’t know exist can hurt you by increasing the surface area exposed to attackers. These bad guys probe for any weakness in existing and new features, which in past releases included goodies such as command-line program invocation (xp_cmdshell), sending mail messages (xp_sendmail), and invocation of COM classes (sp_OACreate). Most of these features will run as the user that runs the SQL Server service process. Sometimes, for ease of installation, this is set to LocalSystem, a Windows superuser account. And if you are running in mixed security mode, and you’ve set your “sa” password to null (that is, no password), you’re wide open, although it must be pointed out that neither running as LocalSystem, enabling mixed security, nor a blank password is the default, and a blank password is strongly discouraged throughout the product and all utilities.
Configuring Security Settings
SQL Server 2005 ships with a new configuration utility named SQL Server Surface Area Configuration Utility. This program lets the DBA configure the database on two levels: services/connections and features. When you bring up the utility, the startup screen gives you the capability of configuring SQL Server instances on a local or remote machine. The services for the database engine itself and any additional services are configurable. Note that in the SQL Server 2005 release, the SQL Browser service is a separate service, as is SQL Server Integration Services. In addition, this panel will allow you to configure remote connections to SQL Server instances. Some administrators were surprised to find that all remote database connections are OFF as a starting point in SQL Express and SQL Server Developer Edition. You can also configure connections on a per-protocol basis. The second screen allows you to turn SQL Server 2005 features on and off. In addition to new features, existing features have been turned off; if existing applications need them, you must turn them back on. The features that are off by default include
• Ad-hoc remote queries (OPENROWSET and OPENQUERY)
• Database mail and SQL mail
• Remote access to the dedicated administrator connection
• OLE automation (sp_OACreate and friends)
• xp_cmdshell
• CLR integration
• Web Assistant
Finally, no Service Broker or HTTP ENDPOINTs are created by default; you need to create them explicitly. Figure 6-1 shows the Security Configuration Manager screen for configuring features. It is shown being used to enable the CLR on an instance of SQL Server. Note that this is the equivalent of using the T-SQL batch shown in the inset in the figure.
Figure 6-1. Surface area configuration for features
In the SQL Server 2005 release, care has been taken to require that SQL Server passwords be stronger. You’ll read later in this chapter about improvements when using SQL Server logins and passwords. With regard to feature enabling, two good examples of the new policy are ENDPOINTs used for HTTP (discussed in Chapter 12) and SQLCLR features (discussed in Chapters 2 through 5).
HTTP ENDPOINTs (endpoints that allow SQL Server to expose stored procedures as Web Services) are not enabled by default. Someone with an extended privilege must explicitly invoke CREATE ENDPOINT; there are no “default endpoints” set up on install. ENDPOINT definition prohibits using any security style but Windows integrated choices (Windows integrated security never sends passwords over the network, even in encrypted form) unless you are using the SSL. SSL, though not as robust as Windows integrated (NTLM or Kerberos) authentication, does at least encrypt all network traffic for the connection. It’s the same encryption you use to send credit-card numbers over the Internet when you purchase products on the Web. When an ENDPOINT is defined, it’s not ON by default; you must create it as ENABLED or explicitly enable it with an ALTER ENDPOINT command. No Web Services or batch access (available separately) are enabled by default within an ENDPOINT, either.
Security and Metadata
One of the ways to gain information about a SQL Server database is to rummage through the system metadata views. In previous versions of SQL Server, you could retrieve metadata information on other users’ database objects that you did not own or have access to. SQL Server 2005 remedies this situation.
One of the new permissions added to SQL Server 2005 is the VIEW DEFINITION permission. Not only are the new system metadata views (which start with the identifier sys.) read-only, but also, you can use the VIEW DEFINITION privilege to permit or prohibit access to metadata. This privilege is exposed on individual database objects; it’s also very useful at the schema level. If you do not have VIEW DEFINITION permission, system views and stored procedures will not list object information (for example, through sp_helptext) or metadata.
Error messages have been changed as well, so as not to provide information that would indicate the existence of objects you don’t have access to. Attempting to drop a procedure that you don’t “know about,” for example, produces the following error message: “Cannot drop the procedure ‘foo,’ because it does not exist or you do not have permission.” Although this feature does not stop “guessing attacks,” it makes SQL Server metadata less subject to random browsing.
A Quick Review of SQL Server Security Concepts with Enhancements
SQL Server 2005 extends the SQL Server security system to handle .NET Framework assemblies and the calls that these assemblies might make. With a .NET Framework assembly, we are concerned with both internal calls (calls to other assemblies, or “ordinary” stored procedure and table access) and external calls (calls from a .NET Framework assembly to the world outside the SQL Server process) in SQL Server 2005. Because the new security is based on the original SQL Server security system, and because there have been many enhancements to SQL Server security in SQL Server 2005, it helps to start with a quick review of how SQL Server security works in general.
Authentication and Authorization: Principals and Permissions
Any security system is concerned with two main concepts: authentication and authorization. Authentication is the process of identifying a principal with strong evidence; authorization is determining what operations a principal can perform. In SQL Server, a login is a principal, and authentication is done by SQL Server when a user logs in to it. When a user—let’s call him Bob—has identified himself to the SQL Server instance by logging in (that is, SQL Server is aware that the user session “5” is actually “bob”), he can do whatever operations, such as add records to tables or query tables, he has authorization to do. The operation can include access to SQL Server objects, as well as the ability to use functionality. One example of the latter is the ALTER TRACE permission that secures SQL Server tracing. These objects and functionality groups are known as securables. This is shown in Figure 6-2.
Figure 6-2. Principals, permissions, and securables in SQL Server 2005
A SQL Server process is called an instance, as in “an instance of the service process sqlservr.exe.” One or more SQL Server instances can run on one machine running Windows. Authentication is done per instance of SQL Server. Principals—that is, logins—that can log in to an instance were identified to SQL Server in previous releases by using system stored procedures and stored in a system view, syslogins. In SQL Server 2005, this information is defined and maintained through the CREATE LOGIN DDL statement and exposed through sys.server_principals and sys.sql_logins.
SQL Server supports Windows users and groups as logins and SQL Server–specific logins, but it may be configured to support only Windows users as logins, which is recommended practice.2 Windows user accounts may be local to the machine running SQL Server or accounts in the Active Directory for the enterprise to which SQL Server belongs. SQL Server no longer supports permitting only SQL Server logins.
In SQL Server 2000, the DBA defines Windows logins to SQL Server using the sp_grantlogin system stored procedure. SQL Server–defined users are assigned their login name, password, and (optionally) language and default database using the sp_addlogin system stored procedure. These system stored procedures still exist in SQL Server 2005 but should be considered deprecated; the equivalent functionality is exposed in the CREATE LOGIN DDL statement in SQL Server 2005, which should be used instead. In SQL Server 2005, you can also use the ALTER LOGIN DDL statement to DISABLE a LOGIN or to RENAME a LOGIN. You could rename the special “sa” login to a different name, like “Fred.” In SQL Server 2005, there is also the ability to create logins that are associated with certificates and asymmetric keys. These logins cannot be used to log in to SQL Server from a client but are used for some advanced security features we’ll be covering in the chapter, as well as with Service Broker endpoints.
Authenticated logins of a particular SQL Server instance can be mapped to principals called users in a particular database. Each login can have a default database; logging in will drop the user into this database. Logins may also be allowed to access databases other than their default database, as defined by the administrator. In SQL Server, the term user refers to a login’s identity in a particular database. Depending on how users are mapped to logins by the administrator, the user ID for a particular user in a particular database may not be the same as the login ID. It is, however, a best practice to make the user name the same as the login name.
A login can have only a single user in a given database, as the following code illustrates:
CREATE LOGIN bob WITH PASSWORD='m8b#5pL'
GO
USE PUBS
GO
CREATE USER pubsbob FOR LOGIN bob
--the following will fail, login bob
-- may only have a single user in a database
CREATE USER otherPubsBob FOR LOGIN bob
Figure 6-3 shows the relationship between logins and users.
Figure 6-3. Defining database users for SQL Server logins
Figure 6-3 shows an instance of SQL Server with two logins: bob and niels. The login bob is mapped to the user bob in the pubs database. The login bob has a default database of pubs, so when bob logs in, by default, he will be referencing the pubs database as user bob. The login niels is mapped to the user nielsB on the database pubs, but the default database for this login is nwnd. The login niels will have to use a three-part name to reference pubs, and when he does, he will be referencing it as the user nielsB. The login niels is also mapped to the nwnd database as user niels, which is the default database for that login. When niels logs in, by default, he will connect to the nwnd database and do so as the user niels. Note that login niels has a different user name on pubs than on nwnd.
If a user has not been granted access to a database, and the “guest” user is enabled on that database, he accesses the database as a user called “guest.” The guest user is off by default in new databases and must be enabled if needed. If a user has been granted access to a database but has no explicit user mapped to it, the token created for the user will have all the user’s information except for a primary ID, so “public” will be used instead (the user_name() function returns the login name in this case). The most common example of this is when access to a database is granted via a Windows group membership.
In SQLServer 2005, you create a user with the CREATE USER DDL statement. The sp_adduser system stored procedure is retained for backward compatibility and acts a little differently from CREATE USER with respect to SCHEMAs, as you’ll see soon. In SQL Server 2005, you can also create users who are associated with asymmetric keys and certificates, as you can with logins. In addition, you can create users who are not tied to logins by using the CREATE USER ... WITHOUT LOGIN statement. The reason for users without logins is to represent the identity associated with a partner’s certificate when using Service Broker remotely. We’ll talk more about Service Broker in Chapter 11.
A SQL Server user in a particular database can be permitted to perform DDL and DML operations; therefore, a database in SQL Server (rather than an instance) usually is the authorization domain. Users are granted permissions using the GRANT DDL statement, such as the following:
GRANT SELECT ON SOMESCHEMA.SOMETABLE TO SOMEUSER
In SQL Server 2005, permissions can also be granted on instance-level objects like ENDPOINTs and even commands like SHUTDOWN.
Permissions on a database table, view, or stored procedure, as well as other database object permissions, are granted to a user by the object’s owner. A permission can be granted to each user, but this would be cumbersome and unwieldy as many users are added to the database. Therefore, SQL Server users can be grouped into roles. In addition to custom roles—which can be defined, for example, for users of a specific application—a few special, built-in roles exist. The only special role we’ll mention here is db_owner, the database owner role. Multiple users can be assigned to the db_owner role for a particular database, and this role gives the user unlimited privilege inside that database. Note that the db_owner role is not the same as DBO, the special database owner principal. There can only be one DBO per database. There is also the special principal public that refers to anyone who has access to a particular database. Public appears along with the database roles in some graphic user interface tools, though technically, it’s not a role. In addition to roles, SQL Server authorization is aware of Windows groups, if database users were derived from Windows-based logins, but Windows groups do not have any correspondence to database roles.
In addition to user-based security, SQL Server has the notion of applicationbased security. Some administrators might wish to permit or deny access based on the current application, rather than the user. For this purpose, SQL Server permits definition of application roles. Application roles are different from roles in that they contain no users. In fact, when a SQL Server user assumes an application role, because code in an application (for example, an inventory control application) issues a statement such as the following,
exec sp_setapprole @role='inventoryrole',
@password= 'AStrongPW!'
the user gives up all the permissions granted him as a user. The user can access only those database objects that the role has permission to access. A new feature in SQL Server 2005 allows unsetting an application role as well as setting it. To maintain security, when using this feature, you must use an option on sp_setapprole that returns a cookie. The cookie is required for sp_unsetapprole; this prohibits users from arbitrarily calling sp_ unsetapprole. Listing 6-1 shows an example.
Listing 6-1. Using sp_unsetapprole requires a cookie
sp_addapprole 'myapp', 'StrongPW1'
GO
DECLARE @theCookie varbinary(256)
EXEC sp_setapprole 'myapp', 'StrongPW1',
@fCreateCookie = true, @cookie = @theCookie OUTPUT
-- Check user, should be myapp
SELECT USER_NAME()
-- now, unset it
EXEC sp_unsetapprole @theCookie
-- Check user, should be original user
SELECT USER_NAME()
GO
Application roles are not used as much as they could be because of issues with connection polling. The application role is not reset when a connection is returned to the pool automatically. If, however, the programmer remembers always to reset the identity using sp_unsetapprole, connection pooling should be able to coexist with application roles.
The following object-level permissions can be granted to a user, a role, an application role, or a Windows group:
• SELECT
• UPDATE
• DELETE
• INSERT
• EXECUTE
• REFERENCES
In SQL Server 2000, SELECT, INSERT, UPDATE, and DELETE are permissions that can be granted on SQL Server table and views. EXECUTE permission can be granted on a stored procedure or user-defined function. REFERENCES permission means that a user who is the owner of table A can define a foreign key to table B, which she does not own, if she has REFERENCES permission on table B. REFERENCES permission is also used to allow a user to define a VIEW or UDF using the SQL WITH SCHEMABINDING option if the view or UDF uses a table or view that is owned by a different owner. You can also use GRANT to grant a permission and give the user permission to grant access to others; this is called GRANT WITH GRANT OPTION. In SQL Server 2005, these permissions can also be granted at a higher scope—for example, at the SCHEMA scope.
SQL Server 2005 adds the following new object-level permissions:
• ALTER
• CONTROL
• RECEIVE (Service Broker queues)
• TAKE OWNERSHIP
• VIEW DEFINITION
If a user is permitted to create objects like tables, views, or stored procedures, when the user issues the CREATE statement, in SQL Server 2000 she becomes the owner of the object. This changes in SQL Server 2005, as the object owner is the schema owner by default. Object owners have unlimited permissions on objects, including the permission to grant other users permissions. One special case is that if the user is a member of the special DBO principal, objects created by that user are considered owned by DBO. Because different users can define objects having the same name—that is, BOB.SOMETABLE and MARY.SOMETABLE—many SQL Server installations prefer to have all database objects defined and owned by DBO, although this can be done differently in SQL Server 2005 with the introduction of schemas (discussed later in this chapter). This simplifies determining permissions, which can become quite complicated, as we’ll see later in this chapter.
As an aside, when anyone in the SQL Server system role sysadmin logs on to a SQL Server instance (this includes Windows system administrators by default), she automatically becomes DBO in every database by default. Programmers should always test their programs by running the program when logged on to Windows as nonadministrators to prevent surprises at deployment time. Obviously, security makes SQL DML statements act differently depending on the user who executes them. In addition, because changing databases inside a SQL Server instance using the USE SOMEOTHERDATABASE statement is an expensive operation, a best practice when using SQL Server from an application server (where like sessions will be pooled using connection pooling) is to define a special user for each distinct application in the application server and to give that user a default database that is appropriate for that application. When users must be distinctly identified inside SQL Server—for example, for auditing—this is not possible, of course, although each user should have a default database that corresponds to the database he will be working in most often.
Execution Context and Ownership Chaining
When you execute a stored procedure or user-defined function, or use a VIEW prior to SQL Server 2005, access to objects inside the stored procedure always occurs using the identity of the caller of the stored procedure. Many people don’t recognize this at first glance, because permissions are checked only when the owner of the stored procedure is different from the owner of the object the stored procedure is accessing. This permits giving users access to database tables only through sanctioned stored procedures while denying them access to the underlying tables directly.
In the following example, let’s say the same user, FRED, owns both the employee table and the update_salary stored procedure. FRED does not grant BOB permission to access the employee table but does grant BOB permission to execute the update_salary stored procedure. Now BOB can update the employee table, but only through the stored procedure:
--Logged in as FRED
CREATE TABLE FRED.employee (
-- other fields elided for clarity
emp_id INT,
name VARCHAR(20),
address VARCHAR(50),
phone VARCHAR(15),
salary_grade INT,
salary MONEY
)
GO
-- procedure for update
CREATE PROCEDURE FRED.update_salary(
@EMP_ID INT,
@NEW_SALARY MONEY)
AS
UPDATE FRED.employee SET salary = @NEW_SALARY
WHERE emp_id = @EMP_ID
GO
-- BOB can only execute the procedure
GRANT EXECUTE ON FRED.update_salary to BOB
GO
If the same user (FRED) owns both the stored procedure and the table, permissions are never checked when the stored procedure accesses the table using DML statements. Because BOB has EXECUTE permission on the stored procedure, the stored procedure works. This is shown in Figure 6-4.
Figure 6-4. An ownership chain. BOB can access the table only through the stored procedure.
If, however, FRED alters the stored procedure also to access another table owned by a different user (say, ALICE) inside the stored procedure, the ability of BOB (not FRED) to access the salary_audit table is checked:
-- procedure for update
-- FRED owns the PROCEDURE
ALTER PROCEDURE FRED.update_salary(
@EMP_ID INT,
@NEW_SALARY MONEY)
AS
-- FRED owns the employee table
UPDATE FRED.employee SET salary = @NEW_SALARY
WHERE emp_id = @EMP_ID
-- But ALICE owns the salary_audit table
INSERT INTO ALICE.salary_audit values(@EMP_ID, @NEW_SALARY)
GO
Figure 6-5 illustrates ownership chaining.
Figure 6-5. If FRED’s stored procedure accesses ALICE’s table, this breaks the ownership chain.
Notice two things about this diagram:
• Because BOB is invoking a stored procedure owned by FRED, BOB must have EXECUTE permission on the stored procedure. He need not have access to the underlying tables as long as FRED also owns the tables.
• Because FRED is the owner of the stored procedure but does not own the ALICE.salary_audit table, permission of the caller (BOB) on the ALICE.salary_audit is checked.
In general, SQL Server checks permissions only when an object accesses an object that is not owned by the same accessor. If the same user owns two objects, and one object accesses the other, permissions are not checked at all, at any level. This is known as an ownership chain. If, at any level, an object accesses another object that is not owned by the accessor, permissions are checked because the ownership chain has been broken.
Until SQL Server 2000 SP3, ownership chains were permitted across databases. After SP3, this is no longer the default, but you can enable cross-database ownership chains on a per-database basis as an option, using the following statement (this is not a recommended practice):
USE master
EXEC sp_dboption MyDatabase, 'db chaining', 'true'
GO
In addition, permissions are always checked when you are using dynamic SQL inside a stored procedure. A different implementation of the update_salary stored procedure is shown in the following code:
-- procedure for update
-- FRED owns the PROCEDURE
ALTER PROCEDURE FRED.update_salary(
@in_emp_id INT,
@in_new_salary MONEY)
AS
-- FRED owns the employee table
-- table is accessed through dynamic SQL
EXECUTE sp_executesql
N'UPDATE FRED.employee SET salary = @new_salary
WHERE emp_id = @emp_id',
N'@new_salary MONEY, @emp_id INT',
@new_salary = @in_new_salary, @emp_id = @new_emp_id
GO
In SQL Server, access is always checked when dynamic SQL is invoked, regardless of the owner of the object that the dynamic SQL statement accesses. Because we’re using dynamic SQL, when BOB executes the stored procedure, BOB’s access to the employee table is checked. Because BOB does not have access to the table, the stored procedure fails.
In SQL Server 2005, the concept of ownership chaining remains tied to the object owner, but in most cases, the object owner will be the schema owner. SQL Server 2005 also introduces the notion of execution context other than “current user.” Setting execution context can solve the dynamic SQL problem just described but must be managed carefully. We’ll discuss it later in this chapter.
SQL Server Password Policies and Credentials
SQL Server 2005 tightens authentication for SQL Server logins when SQL Server runs under Windows Server 2003, and there are also some updates to authentication when SQL Server runs on other operating systems. As we mentioned at the beginning of this chapter, users can use Windows authentication or SQL Server authentication to log in to SQL Server. Windows authentication is very secure because a user’s password is never sent across the network, and the domain or machine administrator can enforce password policy. The password policy can require that users change their passwords at the first login to the NT domain or machine. The policy can require users to use strong passwords—for example, at least eight characters, including at least one number, letter, and special character. The policy can also require users to change their passwords every so often. The policy can specify that a login will be locked out after a certain number of bad password attempts. When a database administrator uses only Windows logins, SQL Server inherits this level of enforceable security. Until SQL Server 2005, SQL Server logins had none of these necessary security characteristics. And weak passwords are acknowledged to be the weakest link in most security systems.
With the new SQL Server 2005 security features, SQL Server logins will have all the same security policy features available. Both SQL Server users and application roles will use the policy. With Windows Server 2003 or later, the policy will be implemented via an OS-level call, NetValidatePassword-Policy, so that the administrator can use the same policy for both Windows integrated and SQL Server logins. To give companies that convert to SQL Server 2005 time to analyze how the policy will affect existing applications, these policies can be turned off on a per-login basis. Obviously, this is not recommended. The CHECK_EXPIRATION policy is set to OFF by default, because organizations and software vendors will have to add password-changing capabilities to each application. As Windows provides users the ability to change their passwords at login time (or while logged on to Windows), SQL Server users will have the ability to change their passwords during login. Client APIs, such as OLE DB, ODBC, and ADO.NET, and the client tools, such as SQL Server Management Studio, have been enhanced to support this.
Password policy is set by using the Active Directory Users and Computers tool if you’re using Active Directory or by using the Local Security Settings administrator tool if you’re administering a nondomain computer. Table 6-1 shows the settings that are exposed using Local Security Settings.
Table 6-1. Security Policies for Windows and SQL Server 2005 Logins
Note that Account Lockout Duration (the amount of time accounts are locked out when you reach the Account Lockout Threshold) and Reset Lockout Counter After (the amount of time after which the invalid login attempts revert to zero, if you haven’t exceeded them) are not applicable until you set Account Lockout Threshold to something other than zero.
There are two password options for SQL Server logins: CHECK_EXPIRATION and CHECK_POLICY. CHECK_EXPIRATION encompasses minimum and maximum password age, and CHECK_POLICY encompasses all the other policies. When you run afoul of either policy, the SQL Server login must be unlocked by the DBA, as shown in an example later in this chapter.
An administrator can add a new login through SQL Server Management Studio or by using the T-SQL statement CREATE LOGIN. The legacy stored procedure sp_addlogin will be supported for backward compatibility but will not expose the new features. As shown in the following example, you can create a new SQL Server login that requires the password to be changed on the user’s first login attempt by using the MUST_CHANGE keyword.
Attempting to access the SQL Server instance without changing the password will result in an error:
CREATE LOGIN FRED WITH PASSWORD = 'hy!at54Cq' MUST_CHANGE,
DEFAULT_DATABASE = pubs,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON
GO
When FRED attempts to log into the database, he’ll get a “Must change password on first login” error. The database administrator can manage password-policy problems with DDL. If login FRED has been locked out, for example, after three bad login attempts, the database administrator can unlock the login by using the following code:
ALTER LOGIN FRED WITH PASSWORD = 'fredsNewpassword#' UNLOCK
GO
In those rare cases where the database administrator wants to turn off the password expiration enforcement or security policy enforcement, ALTER LOGIN can accomplish this. Neither of the following statements will work when the MUST_CHANGE flag is set and the user has not yet changed his password:
ALTER LOGIN FRED WITH CHECK_EXPIRATION = OFF
GO
ALTER LOGIN FRED WITH CHECK_POLICY = OFF
GO
Encryption Keys and Built-In Encryption Functions
Industries and governments are beginning to demand the encryption of database data. In the United States, the health-care and credit-card industries are the first industries to be affected. Because of new laws, for example, doctors are not automatically permitted access to other doctors’ patient records. Before SQL Server 2005, data encryption was supported only by third-party encryption products. In SQL Server 2005, data encryption is a built-in function. Data encryption is sometimes accomplished in other databases at a column level, by declaring a column to be encrypted through a DDL extension. This type of encryption will not be sufficient for the doctors example unless each doctor’s patients are stored in a different table. SQL Server 2005 does not implement column-level encryption; it provides data encryption functions instead. It’s up to the developer to call these functions, manually encrypt the data during an INSERT statement, and decrypt it during SELECT statements.
To encrypt and decrypt data, we need encryption keys. So the first questions to be answered are “Where will the encryption keys be stored?” and “How will the keys themselves be encrypted?” Before covering the encryption functions themselves, we need to answer these questions.
SQL Server 2005 stores and manages secrets by making them database objects and managing them through normal DDL statements, for the most part. Keys are tied to databases, and the encryption objects are arranged hierarchically. At the top of the chain is the service master key. This key is generated automatically when SQL Server 2005 is installed, and it’s generated and stored using the DPAPI (data protection API, a Windows operating system feature). This key is stored in the master database. Key generation uses the principal that is running the SQL Server service as the starting point for the key. We’ll get back to how you back up and restore the key, and to the repercussions this has when the SQL Server service principal is changed, in a moment. Each database can have a database master key. The database master key is not generated automatically for each new database; it is optional. It can be generated manually using the CREATE MASTER KEY DDL statement, as follows:
-- Generating the database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPW#'
The database master key is a 128-byte DES3 key. When the database master key is generated, it is stored in two places. One copy is encrypted with the service master key and stored in the master database; one copy is encrypted with the supplied password and stored in the database itself. The database master key is required for functions such as encrypted conversations when using the Service Broker feature. We’ll have more to say about this in the Service Broker chapter (Chapter 11).
The three key types that SQL Server can store are X.509 certificates, symmetric keys, and asymmetric keys. These secrets are defined at a database level; they are not part of a database schema. Symmetric keys and asymmetric keys are used to encrypt and decrypt data, and possibly other secrets. Certificates can be used for data and secret encryption but can also be used for other SQL Server 2005 functionality, such as SSL encryption on HTTP endpoints. SQL Server 2005 can generate its own certificates or use certificates generated from outside sources. When it uses certificates generated from outside sources, certificate authentication chains, expiration policies, and revocation lists are not used. You can generate a certificate (good for one year) with the following DDL statement:
-- Generating a certificate,
-- no password, so encrypted by database master key
CREATE CERTIFICATE mycert
AUTHORIZATION certuser WITH SUBJECT = 'Cert for certuser'
Each type of secret can be protected by a variety of mechanisms. Table 6-2 shows the protection possibilities for each type of secret.
Table 6-2. Encryption Choices for SQL Server Security Secrets
When you create symmetric and asymmetric keys using DDL statements, you specify the key, the owner, the encryption algorithm that the key uses, and the mechanism used to encrypt the key itself. A variety of encryption algorithms are supported.
Now that we have the basic information down, let’s walk through a scenario for using encryption keys and show the entirety of the DDL involved.
A table contains a column with encrypted data. User1 and User2 should be able to see only their own encrypted data. Admin1 should be able to see data encrypted by both User1 and User2. Listing 6-2 illustrates creating the appropriate SYMMETRIC KEYs and the CERTIFICATEs to protect them. Each CERTIFICATE and SYMMETRIC KEY is owned by the corresponding user, as shown in Listing 6-2. Admin1 (the administrator) must have access to both keys and both certificates.
Listing 6-2. Creating symmetric keys that are protected by certificates
-- Generate a certificate for user1 and user2
CREATE CERTIFICATE User1cert
AUTHORIZATION User1 WITH SUBJECT = 'Cert for User1'
CREATE CERTIFICATE User2cert
AUTHORIZATION User2 WITH SUBJECT = 'Cert for User2'
GO
-- symmetric keys to be used for encryption
-- they are faster than certificates
CREATE SYMMETRIC KEY Key1 AUTHORIZATION User1
WITH ALGORITHM = TRIPLE_DES3 ENCRYPTION BY CERTIFICATE User1cert
CREATE SYMMETRIC KEY Key2 AUTHORIZATION User2
WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE User2cert
GO
The service master key, database master keys, and certificates must be backed up, restored, and maintained just like other database objects. Certificates, symmetric keys, and asymmetric keys can be backed up and restored with the database. Certificates can also be backed up to disk or restored from disk separately. Backing up and restoring only the public key or the public–private key pair is supported for certificates. The database master key is just another symmetric key, but there is special DDL to create and manipulate it. When you detach a database with a database master key and attach it to another instance, you need to open (via a password) the database master key and encrypt it using the service master key of the new instance. It is also possible to use a database master key without having the database master key encrypted by the service master key and stored in the master database. More on this later.
The service master key is special because:
• It is tied to the principal that runs the SQL Server service process.
• It is used to encrypt database master keys.
• It is used to encrypt other credentials at server scope, such as credentials.
You can back up and restore this key with DDL statements, like you can with database master keys and certificates. You cannot change the identity of the principal running the SQL Server service process, however, without explicitly decrypting and reencrypting the service master keys. Doing so could make all your keys unusable. The SQL Server Configuration Manager contains a special function to accomplish changing a SQL Server service principal. You must use this utility to protect the viability of your keys.
Encryption Functions
Now that we have the proper keys in place, let’s discuss the functions used for data encryption. There are 15T-SQL built-in functions defined for this purpose and a related helper function:
• EncryptByAsymKey
• DecryptByAsymKey
• EncryptByCert
• DecryptByCert
• EncryptByPassPhrase
• DecryptByPassPhrase
• EncryptByKey
• DecryptByKey
• DecryptByKeyAutoCert
• DecryptByKeyAutoAsymKey
• SignByAsymKey
• SignByCert
• VerifySignedByAsymKey
• VerifySignedByCert
• HashBytes
First, a word about keys and encryption/decryption functions. Symmetric keys use the same key for encryption and decryption. This presents a key-distribution problem, as the key must be distributed unchanged to both sides. Symmetric keys, however, are orders of magnitude faster for encryption/decryption than asymmetric keys. Asymmetric keys use a key pair to accomplish their purpose. Only the owner of the asymmetric key has the private-key part; the outside world has the public-key part. Public keys are so public that they can be published in key directories. One use of asymmetric key pairs is to encrypt a hash of some data (such as a legal document) with your private key. A user who possesses your public key can recalculate the hash and decrypt the encrypted hash with your public key, and be assured that it came from you because only you have the private key used to encrypt it. This is called signing. The outside world can use your public key to encrypt data to send to you; only you have the correct private key to decrypt it. This is known as sealing the data. Asymmetric keys are slower to encrypt and decrypt data, but are often used to generate or protect a symmetric “session key” for use in a single data-encryption session. Certificates are simply asymmetric keys with additional metadata: a key issuer, expiration date, and so on. SQL Server supports X509.V1 certificates; it can store but cannot use the V3-specific fields in a X509.V3 certificate. To summarize, there are three general rules of encryption to remember:
• Symmetric keys are faster than asymmetric keys.
• The greater the amount of data,4 the longer it takes to encrypt and decrypt.
• In general, the longer the key used for encryption, the safer the encryption is, but the longer it takes to encrypt and decrypt.
Let’s use the set of keys we defined in the preceding section to encrypt some data in a table. We’ll show the scenario in which two users can each see only their own data. In addition, there is an administrator who can see both users’ data. First, we’ll define a table that will hold an encrypted field:
-- The primary key and name data are public
-- The secret_data column is encrypted
CREATE TABLE dbo.secret_table (
id INT PRIMARY KEY IDENTITY,
first_name VARCHAR(20),
last_name VARCHAR(50),
secret_data VARBINARY(8000)
)
GO
GRANT INSERT, SELECT ON dbo.secret_table TO User1, User2
GO
Note that this table definition does not specify anywhere that secret_data is to be used to store encrypted data. In fact, the same column can be used to store both encrypted and unencrypted data, though this might lead to some unwieldy SQL statements. The column is defined as VARBINARY (which is a requirement) but doesn’t support the new data type VARBINARY(MAX). But how big does the column actually have to be? And how much encrypted data can be stored? The minimum amount of room needed for storage can be calculated using the algorithm in Listing 6-3. The encryption algorithms use “normal” VARCHAR data types rather than the new VARCHAR(MAX) data type, so the maximum amount of encrypted data is just under 8,000 bytes.5 Attempting to encrypt any bigger data will result in a truncation error.
Listing 6-3. Algorithm to calculate field size needed for encrypted data
CREATE FUNCTION dbo.CalculateCipherLen(
@KeyName sysname, @PTLen int, @UsesHash int = 0 )
RETURNS int
as
BEGIN
declare @KeyType nvarchar(2)
declare @RetVal int
declare @BLOCK int
declare @IS_BLOCK int
declare @HASHLEN int
-- Hash length that
SET @HASHLEN = 20
SET @RetVal = NULL
-- Look for the symmetric key in the catalog
SELECT @KeyType = key_algorithm
FROM sys.symmetric_keys WHERE name = @KeyName
-- If parameters are valid
IF( @KeyType is not null AND @PTLen > 0)
BEGIN
-- If hash is being used. NOTE: as we use this value to
-- calculate the length, we only use 0 or 1
IF( @UsesHash <> 0 ) SET @UsesHash = 1
-- 64 bit block ciphers
IF( @KeyType = N'R2' OR @KeyType = N'D'
OR @KeyType = N'D3' OR @KeyType = N'DX' )
BEGIN
SET @BLOCK = 8
SET @IS_BLOCK = 1
END
-- 128 bit block ciphers
ELSE IF( @KeyType = N'A1' OR @KeyType = N'A2' OR @KeyType = N'A3' )
BEGIN
SET @BLOCK = 16
SET @IS_BLOCK = 1
END
-- Stream ciphers, only RC4 is supported as a stream cipher
ELSE
BEGIN
SET @IS_BLOCK = 0
END
-- Calculate the expected length.
-- The formula is different for block ciphers & stream ciphers
IF( @IS_BLOCK = 1 )
BEGIN
SET @RetVal =
( FLOOR( (8 + @PTLen + (@UsesHash * @HASHLEN) )/@BLOCK)+1 ) *
@BLOCK + 16 + @BLOCK
END
ELSE
BEGIN
SET @RetVal = @PTLen + (@UsesHash * @HASHLEN) + 28
END
END
RETURN @RetVal
END
GO
Now let’s put some data into our table. Also be aware of the fact that encrypting data makes the column essentially useless as an index or with the UNIQUE, PRIMARY KEY, and FOREIGN KEY column constraints.
To use symmetric keys, a user must not only have permission to use them, but also must explicitly open the key. To open a key, you have to supply the appropriate secret for the key; this depends on how the key was secured with the CREATE and ALTER DDL statements. If the key was secured by using the database master key, only access to the key is needed. The database master key itself can be automatically opened, as long as it’s stored in the master database as described earlier in this chapter (protected by the service master key); this is the default. Keys need to be open for both encryption and decryption. Let’s put an encrypted row into the database for each user, as shown in Listing 6-4.
Listing 6-4. Encrypting data while inserting it into a table
-- insert rows into secret_table, using encryption
EXECUTE AS USER='user1'
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert;
INSERT dbo.secret_table VALUES('Joe', 'User',
EncryptByKey(Key_GUID('Key1'),'some secret number1'))
CLOSE SYMMETRIC KEY Key1
GO
REVERT
GO
EXECUTE AS USER='user2'
OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert;
INSERT dbo.secret_table VALUES('Jill', 'Smith',
EncryptByKey(Key_GUID('Key2'),'some other secret number2'))
CLOSE SYMMETRIC KEY Key2
GO
REVERT
GO
Keys should be closed when you are through with them, although all open keys (known as the keyring) are automatically closed on session termination. This is true even when connection pooling is used. The keyring is per-session, however, and unrelated to the execution context. Now let’s fetch the data.
If you attempt to fetch a column that contains encrypted data without using the appropriate decrypt function, the encrypted data is returned whether or not you have the appropriate encryption key open. To fetch the unencrypted data, you must use an encryption algorithm in the SELECT statement. An ease-of-use alternative is to encapsulate this in a VIEW. When the decrypt function is specified as part of a SELECT statement, SQL Server attempts decryption using all the keys that you have open. If any key is successful, the correct data is returned; otherwise, the decrypt function returns NULL. This is illustrated in Listing 6-5.
Listing 6-5. Only users with access to the keys can access data
EXECUTE AS USER='user1'
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert
-- you get the encrypted value
SELECT id, first_name, last_name, secret_data
FROM secret_table
-- you either get the unencrypted value or NULL
SELECT id, first_name, last_name,
cast(decryptByKey(secret_data) AS VARCHAR(256))
FROM secret_table
CLOSE SYMMETRIC KEY Key1
GO
REVERT
GO
EXECUTE AS USER='user2'
OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert
-- you get the encrypted value
SELECT id, first_name, last_name, secret_data
FROM secret_table
-- you either get the unencrypted value or NULL
SELECT id, first_name, last_name,
cast(decryptByKey(secret_data) AS VARCHAR(256))
FROM secret_table
CLOSE SYMMETRIC KEY Key2
GO
REVERT
GO
EXECUTE AS USER='admin1'
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert
OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert
-- cyphertext
SELECT id, first_name, last_name, secret_data
FROM secret_table
-- sees both rows decrypted
SELECT id, first_name, last_name,
cast(decryptByKey(secret_data) AS VARCHAR(256))
FROM secret_table
CLOSE ALL SYMMETRIC KEYS
GO
REVERT
GO
In addition to the encrypt/decrypt functions for every key type and even pass phrase–based encryption/decryption, there are automatic decryption functions when using certificates or asymmetric keys. These functions are provided as a convenience if a single certificate or asymmetric key is used to encrypt all the data in a table, as shown in Listing 6-6. In this case, you can use a VIEW to read the table, and key management is taken care of automatically.
Listing 6-6. Using the DecryptByKeyAutoCert function in a VIEW
-- Generate a certificate owned by DBO
CREATE CERTIFICATE somecert
AUTHORIZATION DBO WITH SUBJECT = 'Cert for Encrypted column'
GRANT CONTROL ON CERTIFICATE::somecert TO lowpriv_user
GO
CREATE SYMMETRIC KEY somekey AUTHORIZATION DBO
WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE somecert
GRANT VIEW DEFINITION ON SYMMETRIC KEY::somekey TO lowpriv_user
GO
CREATE TABLE dbo.secret_col_table (
id INT PRIMARY KEY IDENTITY,
secret_col VARBINARY(8000)
)
GO
OPEN SYMMETRIC KEY somekey DECRYPTION BY CERTIFICATE somecert
INSERT dbo.secret_col_table VALUES(
EncryptByKey(Key_GUID('somekey'), 'more secret data'))
CLOSE SYMMETRIC KEY somekey
GO
CREATE VIEW dbo.public_view AS
SELECT id, convert(varchar(100),
DecryptByKeyAutocert(cert_id('somecert'), null, secret_col))
AS dbo.secret_col
FROM dbo.secret_col_table
GO
-- now the lowpriv_user can access the column
GRANT SELECT ON dbo.public_view TO lowpriv_user
GO
Note that when using a VIEW defined using an automatic decryption function, the low-privileged user requires not only access to the view, but also access to the certificate (CONTROL) and the ability to “see” (VIEW DEFINITION) the encryption key. You can use a helper function that uses the EXECUTE AS OWNER feature to further simplify this; we’ll be looking at EXECUTE AS later in this chapter.
Separation of Users and Schemas
SQL:1999 defines the concept of a database schema as a named group of data that is owned by a particular authorization ID. Schemas are scoped to the database (called CATALOG in SQL:1999 and the information schema views), and one database can contain one or more schemas. Schema objects—such tables, views, and stored procedures, live in a schema—and the two-part name of a database object is actually schemaname.objectname.
Prior to SQL Server 2005, the concept of a schema was tied to a particular user. Any objects created by a user were owned by that user, and SQL Server really defined the two-part name of a database object as ownername.objectname rather than schemaname.objectname. There was a CREATE SCHEMA DDL statement, but you did not have the option of naming your schema, only its owner, as shown here:
-- SQL Server 2000 create schema, no schema name
CREATE SCHEMA AUTHORIZATION fred
CREATE VIEW v1 as SELECT au_id, au_lname FROM authors
GRANT SELECT ON v1 to public
GO
-- SQL Server 2005 create schema with name
CREATE SCHEMA fredstuff AUTHORIZATION fred
This pre–SQL Server 2005 CREATE SCHEMA statement actually was a convenient way to create objects that belonged to a specific user (like fred, in this case) and grant permissions to them in a single DDL statement batch. The problem of having database objects tied to a particular user was that to drop the user, the database administrator had to reassign or drop and recreate all that user’s database objects.
SQL Server 2005 introduces the concept of named schemas as separate from users. When you use the new CREATE USER DDL statement to create a user, you can assign a default schema for that user. If a default schema is not assigned, the DBO (database owner) schema is the default, as shown here:
-- user's default schema is uschema
CREATE USER u1 FOR LOGIN u1 WITH DEFAULT_SCHEMA = 'uschema'
go
-- user's default schema is dbo
CREATE USER u2 FOR LOGIN u2
go
A schema can be owned not only by a specific user (created with a SQL Server login or Windows login), but also by a database role or an application role defined in that database. The new CREATE APPLICATION ROLE DDL statement permits assignment of a default schema, but because many users can be assigned to a role (an ordinary role, not an application role), CREATE ROLE does not assign a default schema for roles, Windows groups mapped as users, or symmetric and asymmetric keys mapped as users. Note that the legacy procedures sp_adduser and sp_addapprole have been changed first to create a schema with the same name of the user or application role and then to call the appropriate CREATE statement, specifying that schema as the default schema. Use of the new CREATE statements is preferred; the behavior of the stored procedures is kept only for backward compatibility.
The owner of a schema (a single user or multiple users) can create database objects within that schema if he has CREATE privilege on the object and also grant schema-level privileges to others. The schema owner does have to be granted permission to create the database objects, but the grant permission exists on a database level, not on a schema level. Here’s an example of a user that has an associated schema and is also the owner of that schema:
USE demo1
GO
CREATE LOGIN alogin1 WITH password = 'password1',
DEFAULT_DATABASE = demo1
GO
-- default named schema
CREATE USER auser1 FOR LOGIN alogin1
WITH DEFAULT_SCHEMA = aschema1
GO
CREATE SCHEMA aschema1 AUTHORIZATION auser1
GRANT CREATE TABLE TO auser1
GO
EXECUTE AS USER='auser1'
GO
-- this works and creates aschema1.table1
CREATE TABLE table1 (theid INTEGER)
GO
In this case, if we did not set a default_schema for the auser1 user, his default_schema would be dbo. Because auser1 is not a member of the dbo database role, the CREATE TABLE statement would fail.
What this means to the database administrator is that because schemas (and the objects they contain) can be owned by a role, when a user is dropped from the database, the database objects she has created do not have to be reassigned or dropped and re-created. Listing 6-7 shows an example using a SQL Server role for a payroll system.
Listing 6-7. Managing access by inclusion in a role
USE payrolldb
GO
CREATE ROLE payroll
GO
CREATE SCHEMA prschema AUTHORIZATION payroll
GO
CREATE LOGIN janet WITH PASSWORD = 'StrongPW#',
DEFAULT_DATABASE = payrolldb
GO
- default named schema
CREATE USER janet FOR LOGIN janet
WITH DEFAULT_SCHEMA = prschema
GO
—- add janet to payroll and let her create tables
sp_addrolemember 'payroll', 'janet'
GRANT CREATE TABLE TO janet
GO
Now user janet can create tables, and they will be contained within the prschema schema. If Janet is reassigned, the user janet can be dropped from the database without affecting any of the tables she has created.
Having named schemas affects the way database object names are resolved. If user janet issues the SQL statement SELECT * from benefits, SQL Server will attempt to resolve the table name benefits in this order:
sys.benefits(only for system objects; in this case,benefitsis not a system object)prschema.benefits(using the default schema)dbo.benefits
One further special case needs to be mentioned. It is possible that a database user will have a default schema that she does not own (such as DBO) but will have the ability to create database objects in a different schema.
In that case, the database object in the CREATE DDL statement must use the two-part name explicitly. If user janet were defined without a DEFAULT_SCHEMA keyword,6 for example, her default schema would be dbo. Because she does not have permission on the dbo schema, any attempt to create a database object with a one-part name would fail:
-- this statement would fail
CREATE TABLE benefits2003 (empid INT) -- other columns elided
-- this statement would succeed
CREATE TABLE prschema.benefits2003 (empid INT)
Schemas have their own sets of permissions. You can grant or deny permissions like SELECT, EXECUTE, or VIEW DEFINITION on a schemawide basis. The following SQL statement prohibits the role payroll from seeing any database objects in the bob schema using the system views:
DENY VIEW DEFINITION ON SCHEMA::bob TO payroll
Synonyms
SQLServer 2005 introduces support for a database object known as a synonym. A synonym is just an alternative name for an existing database object that keeps a database user (more likely, a database programmer) from having to use a multipart name for an object. Synonyms can be defined on a two-part, three-part, or four-part SQL Server object name. A synonym can be defined by the following database objects:
• Table
• View
• Stored procedure
• User-defined function
• Extended stored procedure
• Replication filter procedure
Although synonyms can be created on a multipart object name, they are scoped to the database that they are created in. Listing 6-8 shows some examples of creating and using synonyms.
Listing 6-8. CREATE-ing and using SYNONYMs
USE AdventureWorks
GO
CREATE SYNONYM dbo.Customers_East ON Eastserver.Northwind.dbo.Customers
GO
CREATE SYNONYM dbo.Contact FOR Person.Contact
GO
-- use the one-part or two-part name
SELECT * FROM Contact
SELECT * FROM dbo.Customers_East
GO
USE Northwind
GO
-- use the three-part name
SELECT * FROM AdventureWorks..Contact
An interesting feature of synonyms is that a synonym itself belongs to a schema. Although you can create a synonym for a user-defined function, because a user-defined function must always be referred to using a two-part name, even if a synonym is defined, you must use the function’s two-part synonym name.
Specifying Execution Context for Procedural Code
In previous versions of SQL Server, catalogued procedural code always ran within the security context of the caller, as explained earlier in this chapter in the section on ownership chaining. This is a good strategy for the most common case—for example, when you want to allow users access to tables through stored procedures without giving them access to the base tables. Ownership chaining won’t work in two cases, however. The first case is where the owner of the procedure and the owner of the database object that the procedure accesses are not the same. The second case is where you use dynamic SQL, as shown in Figure 6-6.
Figure 6-6. Dynamic SQL will always break an ownership chain.
Dynamic SQL usually shouldn’t be used if you can accomplish the same result using a different construct. As an example, in Listing 6-9, we’ll use a stored procedure that executes the following dynamic SQL composed by concatenating strings. (Note: We’ll cover the new T-SQL PIVOT syntax in Chapter 8.)
Listing 6-9. Dynamic SQL with string concatenation is a security risk
-- this should get a dynamic list of properties
CREATE FUNCTION products.get_property_names()
RETURNS VARCHAR(1000)
AS
BEGIN
-- hardcode for now
RETURN '[color], [size], [fabric]'
END
GO
-- CALLER needs SELECT permission on products.properties
CREATE PROCEDURE products.get_property_values
AS
DECLARE @stmt VARCHAR(8000)
DECLARE @col_list VARCHAR(1000)
SET @col_list = products.get_property_names()
-- NOTE: that this is subject to SQL injection
-- "col_list" must be validated properly
SET @stmt='SELECT * FROM products.properties
PIVOT (MAX(value)
FOR name IN ('+@col_list+')) AS P'
EXECUTE(@stmt)
go
SQL Server 2005 now allows you to specify that procedural code execute in a different execution context. There are four reasons you might want to do this:
• You want dynamic SQL to execute in the context of the creator of the stored procedure, as static T-SQL would.
• Because data access code in CLR procedures (through the SqlClient data provider discussed in Chapter 4) is effectively dynamic SQL, you might want this code to execute in the context of the creator of the stored procedure as well.
• You want to evaluate ownership chains in the context of the creator of the stored procedure rather than the caller of the procedure, although it’s a good idea not to rely on ownership chains at all.
• You need an execution principal for code, like Service Broker activation procedures, that don’t have a caller in the traditional sense. (Service Broker is discussed in Chapter 11.)
You choose the execution context on a per-procedure basis when you create the procedure, using the EXECUTE AS parameter. Execution context can also be set on triggers and user-defined functions, except for inline table-valued user-defined functions. An example is shown in the following code:
-- this will execute as the owner of the stored procedure
-- if properties table and get_property_names() have the same owner,
-- ownership chain is not broken
CREATE PROCEDURE products.get_property_values
WITH EXECUTE AS OWNER
AS
DECLARE @col_list VARCHAR(1000)
SET @col_list = products.get_property_names()
-- NOTE: that this is subject to SQL injection
-- "col_list" must be validated property
SET @stmt='SELECT * FROM products.properties
PIVOT (MAX(value)
FOR property_names IN ('+@col_list+')) AS P'
EXECUTE( @stmt)
go
You can also use EXECUTE AS with batches. In fact, we’ve been using it with the examples in this chapter. It replaces and supersedes the functionality of SETUSER in previous versions of SQL Server. You go back to the previous identity by using the REVERT statement. EXECUTE AS enhances SETUSER in its ability to impersonate a login as well as a user and in the fact that EXECUTE AS blocks can be nested. You can also specify a cookie with EXECUTE AS used for batches; the cookie is required in the REVERT statement to revert to the previous security context.
There are different EXECUTE AS options with procedural code and SQL batches, as listed in Table 6-3.
Table 6-3. EXECUTE AS Options
EXECUTE AS CALLER is still the default behavior in SQL Server 2005. EXECUTE AS SELF or EXECUTE AS OWNER can be used in the definition of the stored procedure so that even though permission will be checked when dynamic SQL is used, it will be checked against the procedure owner’s permissions. The distinction between the two is that the owner of a procedure might be the schema owner and may not necessarily be the principal who created the procedure. The procedure creator may not be the same as the schema owner, who is the owner of every object created in the schema by default. Figure 6-7 shows using EXECUTE AS SELF to make dynamic SQL behave the same as static SQL.
Figure 6-7. Using EXECUTE AS SELF with dynamic SQL
Special care must be taken to guard against SQL injection (that is, piggybacking of dangerous code after “normal” parameters) when EXECUTE
AS is used. Although counting the rows in a table is pretty mundane code, the fact is that any dynamically constructed code in a stored procedure can be potentially dangerous. EXECUTE AS also can be used to create applications that won’t depend on ownership chaining without necessarily using dynamic SQL at all. The purpose is to give flexibility to the application writers. Say that you have a procedure that looks like this:
-- this will execute as DBO
CREATE PROCEDURE count_rows_as_dbo(@name NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
go
Given the count_rows_as_dbo stored procedure in the preceding example, if the procedure was catalogued by the DBO, the following code will execute as DBO, regardless of the user who calls it:
DECLARE @s varchar(50)
-- DO NOT fill in this variable from user input!!!
SET @s = 'authors;drop table customers'
-- count the rows and drop the table!
count_rows_as_dbo @s
Although EXECUTE AS SELF looks interesting, it should be used with care because it can make ownership chains more complex. When a stored procedure with EXECUTE AS SELF accesses any table that the current owner does not own, an ownership chain will be broken, and permissions on the underlying object will be checked. In addition, when a different stored procedure uses this stored procedure, it is possible that ownership chains could be broken at two levels, as shown in Listing 6-10.
Listing 6-10. EXECUTE AS SELF does not bypass ownership checks
-- table FOO_TABLE is owned by DBO.
-- using a procedure count_rows_as_me
CREATE PROCEDURE count_rows_as_me(@name NVARCHAR(50)
WITH EXECUTE AS SELF
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
go
SETUSER JAY
go
-- this checks permissions if JAY is not DBO
count_rows_as_me 'foo_table'
--
--
SETUSER 'fred'
go
CREATE PROCEDURE select_and_count
AS
SELECT * FROM customers
count_rows_as_me 'foo_table'
go
-- this does two ownership checks
-- even if FRED is DBO
EXECUTE select_and_count
go
By default, procedural code that uses a nondefault execution context can access only resources in the current database—that is, you may not use three-part names at all. This is to prevent a user with DBO privilege in one database from gaining access to data in another database. If you need to access resources in another database or system-level resources, you must grant appropriate permissions to the executor of the code. Another option is to sign the code with a certificate, map the certificate to a login, and grant permissions to the certificate. We’ll see this option later in this chapter.
Administrators and auditors will be concerned about the use of EXECUTE AS, because the function SUSER_NAME() returns the current identity, not the original caller’s identity. Auditors can keep track of this by auditing identity changes using SQL Profiler events, Event Notifications, or Windows Management Instrumentation (WMI) events. In addition, a new ORIGINAL_LOGIN() function retrieves the name of the login of the session. There is no function that returns the original user name, however. In addition to using ORIGINAL_LOGIN(), it is possible to call EXECUTE AS CALLER to revert to the original caller (go back to the default behavior). It is highly recommended to use EXECUTE AS CALLER on all the operations that are not required to be executed by the module’s context whenever possible.
Code Signing
A slightly different way to approach procedural execution context is to use certificates or asymmetric keys to sign a procedure. This is possible for T-SQL stored procedures, user-defined functions, and triggers. Adding a signature to procedural code must be accompanied by defining a database user corresponding to the certificate or asymmetric key that signed the code. Then permissions are granted to that user. When the procedure executes, after verifying the signature, the certificate or asymmetric key–mapped user will be used to modify the current execution context. It will always be added as a secondary identity to the execution context (think of it as a role for the duration of the executing code). Additionally, if the module was marked by EXECUTE AS, it will also be added to the authenticators list. Listing 6-11 shows an example.
Listing 6-11. Signing a stored procedure with a certificate
-- signed modules
CREATE CERTIFICATE signing_cert
AUTHORIZATION DBO WITH SUBJECT = 'Cert for Signing Procs'
GO
CREATE USER signing_user FOR CERTIFICATE signing_cert
GO
CREATE SCHEMA user1_schema AUTHORIZATION user1
GRANT CREATE PROCEDURE TO user1
GO
-- this table is in DBO schema
-- no access granted to user1
CREATE TABLE signing_table (
id INTEGER PRIMARY KEY,
thedata VARCHAR(100)
)
GO
INSERT signing_table VALUES(1, 'data1')
INSERT signing_table VALUES(2, 'data2')
GO
EXECUTE AS USER='user1'
GO
CREATE PROCEDURE user1_schema.get_signing_table
AS
SELECT * FROM signing_table
GO
-- attempt to execute it, fails
EXECUTE user1_schema.get_signing_table
GO
REVERT
GO
-- sign the procedure with the cert
ADD SIGNATURE TO user1_schema.get_signing_table
BY CERTIFICATE signing_cert
-- then give the cert's user access to table
GRANT SELECT ON signing_table TO signing_user
GO
EXECUTE AS USER='user1'
GO
-- attempt to execute it, works now
EXECUTE user1_schema.get_signing_table
GO
REVERT
GO
Even though the example doesn’t show this, it is also possible to specify that a certificate’s private key should not be maintained by the database after signing the modules with ALTER CERTIFICATE signing_cert REMOVE PRIVATE KEY. The private key is necessary only for signing, not for authorization.
As a general rule, EXECUTE AS is good only in the database in which the procedure is defined. This is useful in cases when a Web-hosting service runs a single instance of SQL Server and assigns a different database to each client. Clients shouldn’t be able to access data in others’ databases in the same instance, even (or especially) under the DBO identity. If access to the target database using impersonated credentials is desired, it can be accomplished by allowing the source database’s DBO AUTHENTICATE privilege and setting the foreign database as TRUSTWORTHY. This can be accomplished only by someone with CONTROL SERVER privilege (sysadmin). An alternative uses code signing and the TRUSTWORTHY database property by giving the DBO permission to authenticate and marking the trustworthy bit ON. It should be pointed out that by granting AUTHENTICATE, the DBO on the SOURCE database can easily become DBO on the TARGET database, and if the authenticate has been granted to a server scope, the DBO can become sysadmin. Because of this, TRUSTWORTHY really refers to the trustworthiness of the database administration chain and should be set properly. The details of impersonation in foreign databases and instances are outside the scope of this book.
SQL Server Permissions and SQLCLR Objects
We have six new kinds of SQL Server objects in the managed world of SQL Server 2005. Three of these objects are managed-code variations on SQL Server objects:
• Stored procedures
• User-defined functions
• Triggers
Three of the objects are new in SQL Server 2005:
• Assemblies
• User-defined types
• User-defined aggregates
The reason that all these objects are new is that they all run executable code rather than have SQL Server run the code. In previous versions of SQL Server, extended stored procedures or COM objects using COM automation to run code always ran that code in the context of the Windows user account that was running the SQL Server service process. With the introduction of a managed environment that can control aspects of code loading (through the assembly loading policy mentioned in Chapter 2) and code execution through Host Protection Attributes (HPAs) that work through code access security, execution of .NET Framework assembly code catalogued in SAFE assemblies is as safe, security-wise, as running vanilla T-SQL code from inside SQL Server. From a SQL Server object-security point of view, all SQLCLR code is safer than the equivalent extended stored procedure.
When SQL Server is used as a host for the .NET Framework runtime:
• Managed user code does not gain unauthorized access to user data or other user code in the database.
• There are controls for restricting managed user code from accessing any resources outside the server and using it strictly for local data access and computation.
• Unless impersonation is specifically coded into the procedure, managed user code can access only resources that the SQL server identity can access.
• SQL Server uses .NET Framework code access security (CAS) and provides three distinct permission sets for managed user code.
• CLR procedures and functions are a way to provide security wrappers similarly to the way T-SQL procedures and functions do, by using SQL Server permissions.
We’ll first look at the extension of the traditional SQL Server object security to the new objects and then go on to describe .NET Framework–specific considerations.
Assembly Permissions: Who Can Catalog and Use an Assembly?
To catalog assembly code to SQL Server, a user must have the ability to execute the CREATE ASSEMBLY DDL statement. ALTER ASSEMBLY and DROP ASSEMBLY are related DDL statements. By default, only members of the sysadmin server role and the db_owner and ddl_admin database roles have the permission to execute the assembly-related DDL statements. The permission can be granted to other users. The user or role executing the statement becomes the owner of the assembly. In addition, it is possible to assign an assembly to another role using the AUTHORIZATION parameter of CREATE ASSEMBLY or ALTER AUTHORIZATION, as shown in Listing 6-12. The reason that you might want to change an assembly’s owner is that there is a separate AppDomain created per database and per assembly owner. If you need to call directly (not using T-SQL) into another assembly’s methods, it must be owned by the same owner.
Listing 6-12. Using ASSEMBLY DDL
--
-- create an assembly owned by fred
--
CREATE ASSEMBLY SomeTypes
AUTHORIZATION fred
FROM 'c: ypesSomeTypes.dll'
GO
-- create an assembly owned by DBO
-- while logged on as sysadmin
CREATE ASSEMBLY SomeMoreTypes
AUTHORIZATION dbo
FROM 'c: ypesSomeMoreTypes.dll'
-- alter the first assembly to be owned by DBO
ALTER AUTHORIZATION ON assembly::SomeTypes TO dbo
In the most common scenario, CREATE ASSEMBLY reads bytes from the Windows file system, although if you use CREATE ASSEMBLY specifying the hexadecimal bytes that make up the assembly as part of the CREATE ASSEMBLY DDL statement, no file system access of any kind is required. The preceding example reads bytes from a network share. ALTER ASSEMBLY may also read bytes from the file system if the options of ALTER ASSEMBLY that reload code or load debugging symbols are used. Some Windows security principal must have the permission to read the required files. But what security principal is used? This depends on the privilege of the user running the SQL Server service process and whether the SQL Server user is using Windows integrated security or SQL Server security to log in to the server.
If the user is logged in using a SQL Server security login, the access to the remote file system will fail. When a Windows security login is used, access to the bits is obtained through impersonation. That means file system access will fail if the user running the SQL Server service process does not have the (Windows) right to perform impersonation—that is, to change the currently executing thread so that it executes as a different user. If the user running the SQL Server service process has impersonation authority, and the user is logged in to SQL Server as a Windows user, the request to read bytes executes using an impersonation token of the currently logged-on user.
One final piece of the puzzle is needed for CREATE ASSEMBLY and ALTER ASSEMBLY. We can define three different levels of code access security for a specific assembly: SAFE, EXTERNAL_ACCESS, and UNSAFE, listed in order of decreasing code safety. Although these levels relate to code access security, additional permissions are required to execute CREATE and ALTER ASSEMBLY and to give the resulting assembly any permission set other than SAFE. To CREATE or ALTER EXTERNAL_ACCESS or UNSAFE assemblies, you have two choices:
• The LOGIN has the appropriate permission (UNSAFE ASSEMBLY or EXTERNAL ACCESS) in the master database, and the user database has the TRUSTWORTHY property on.
• The ASSEMBLY is signed with an asymmetric key or certificate that has a LOGIN with UNSAFE ASSEMBLY or EXTERNAL ACCESS permission.
Here’s an example of how the second choice would work:
- Create a strong-named key in c: empassm.snk.
- Sign the assembly unsafe1.dll with this strong-named key.
- Make a SQL Server
LOGINfor the key. - Give
LOGINthe appropriate permissions. - Catalog the unsafe assembly.
In code, it looks like Listing 6-13.
Listing 6-13. Creating an UNSAFE assembly without marking the user database TRUSTWORTHY
-- master key in master database
USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
go
-- keyfile generated by Visual Studio 2005
-- or .NET Framework command line utilities
-- this is the keyfile that the assembly is signed with
CREATE ASYMMETRIC KEY assm FROM FILE='c: empassm.snk'
GO
CREATE LOGIN snk FROM ASYMMETRIC KEY assm
GO
-- this is in the master database
GRANT UNSAFE ASSEMBLY TO snk
GO
USE userdb
GO
CREATE ASSEMBLY unsafeassemblyex FROM 'c: empunsafe1.dll'
WITH PERMISSION_SET = UNSAFE
GO
That’s only one variation of it. You can also use the key stored in the assembly (CREATE ASYMMETRIC KEY FROM EXECUTABLE FILE= . . .) or an assembly already catalogued inside the database (CREATE ASYMMETRIC KEY FROM ASSEMBLY . . .). You can do the same thing using certificates rather than asymmetric keys.
Some of the permissions that relate to an assembly are based on the user’s identity—that is, normal SQL Server authorization. In general, access to all the .NET Framework–based SQL Server objects is predicated on the checking of three different types of interobject links. These are known as invocation links, schema-bound links, and table-access links.
Invocation links refer to invocation of code and are enabled by the EXECUTE permissions. The code may be managed or T-SQL code, such as a stored procedure.
Examples of this could be a user calling a database object (for example, a user calling a stored procedure) or one piece of code calling into another piece of code (for example, an assembly calling another assembly or a procedure accessing a UDT column).
Schema-bound links are always between two database objects and are enabled by the REFERENCES permission. The presence of the schema-bound link causes a metadata dependency in SQL Server that prevents the underlying object from being modified or dropped as long as the object that references it is present. You cannot drop an assembly if it contains a user-defined type that has been catalogued, for example, and you cannot drop a user-defined type that is in use as a column in a table.
Table-access links correspond to retrieving or modifying values in a table, a view, or a table-valued function. They are similar to invocation links except that they have a finer-grained access control. You can define separate SELECT, INSERT, UPDATE, and DELETE permissions on a table or view.
REFERENCES permission gives a user the ability to create objects that reference SQLCLR assemblies or specific objects such as stored procedures, user-defined functions, or user-defined types that reside in assemblies; REFERENCES on a UDT, for example, gives a user permission to create tables that use the UDT as a column. REFERENCES permission allows the grantee to define schema-bound links to that object.
EXECUTE permission on an assembly allows a user to invoke methods or instantiate public classes within that assembly. Granting a user EXECUTE permission on an assembly does not automatically give him access to the stored procedures, user-defined functions, and UDTs that are defined within an assembly as SQL Server objects. Permissions to the specific object to be accessed must also be granted. Interassembly invocation links between assemblies owned by different users are controlled by the fact that a separate AppDomain is created for each assembly owner, rather than by the EXECUTE permission on assemblies. It is highly recommended that if you need to call between classes in different assemblies, those assemblies should have the same owner.
A user-defined type must be defined in the SQL Server catalog to be visible to SQL Server stored procedures and other T-SQL procedural code, just as an assembly is. When a UDT is defined in the SQL Server catalog, users need the appropriate permission to invoke it, just as they do for any other database object.
A UDT that is catalogued to SQL Server with CREATE TYPE is secured through permissions like any other SQL Server object. As with assemblies, you can grant REFERENCES and EXECUTE permissions on a UDT; with a UDT, however, the meaning is slightly different. Schema-bound links, in the context of a UDT, consist of:
• Creating a table with the UDT as a column
• Defining a stored procedure, UDF, or trigger on the static method of a UDT
• Defining a view using the WITH SCHEMABINDING option that references the UDT
EXECUTE permission on a UDT is defined at the class level, not at the method level. Granting EXECUTE permission on a UDT does not automatically grant permission on every stored procedure or user-defined function in the UDT through T-SQL. This must be granted by granting permission to the stored procedure or UDF SQL Server object directly. EXECUTE permission is also required to fetch a UDT or execute its methods from code inside the server using the SqlClient data provider.
User-defined aggregates follow the same rules. A schema-bound link to a user-defined aggregate would consist of:
• Creating a table with the user-defined aggregates used in a constraint
• Defining a stored procedure, UDF, or trigger that uses the user-defined aggregate
• Defining a view using the WITH SCHEMABINDING option that uses the user-defined aggregate
REFERENCES permission would be required to create any of the database objects listed earlier.
Ownership chains apply when using user permissions with SQL Server objects, just as they do when using other SQL objects, such as tables and views. Following are a few examples that will illustrate the concepts:
• User bob attempts to execute CREATE ASSEMBLY for bobsprocs. The bobsprocs assembly has a method that references another assembly, timsprocs, that is already catalogued in the database. User bob needs to have REFERENCES permission to the timsprocs assembly, because a schema-bound link will be set up between the two assemblies.
• If user bob creates a procedure, bobproc1, that is based on a method in the bobsprocs assembly, no permissions are checked. If user fred creates the bobproc1 procedure, however, this will set up a schema-bound link. User fred needs to have REFERENCES permission to the bobsprocs assembly.
• The procedure bobproc1 in bobsprocs is specified as execution_context = caller. When user alice attempts to execute bobproc1, she must have EXECUTE permissions on the procedure, but the code runs as bob.
• User alice then defines a table, atable, using the UDT bobtype, which is part of the assembly bobsprocs. To do this, she needs REFERENCES permission on the bobproc assembly and on the bobtype UDT.
• User joe attempts to execute a SELECT statement that contains the UDT bobtype in the table atable. To do this, he needs SELECT permission on atable and EXECUTE permission on bobtype.
What .NET Framework Code Can Do from within SQL Server: Safety Levels
SQL Server permissions take care of dealing with security from a SQL Server–centric point of view. But if a .NET Framework stored procedure can load arbitrary assemblies from the file system or the network, the security of the SQL Server process could be compromised. The first concern is taken care of by the new .NET Framework hosting APIs. Aside from a specific subset of the .NET Framework base class libraries (BCL), SQL Server handles all assembly-loading requests. You cannot instruct SQL Server to load arbitrary assemblies from the local file system or the Internet. In addition, the IL code in each .NET Framework assembly is checked for validity when CREATE ASSEMBLY is run. On a more granular level, the .NET Framework uses not only SQL Server user-based permissions, but also .NET Framework code access security.
Introduction to Code Access Security
.NET Framework code access security is meant to check the permissions of code before executing it, rather than checking the permissions of the user principal who executes the code. Code access security determines how trustworthy code is by mapping pieces of evidence—such as where the code was loaded from, whether the code was signed with a digital signature, and even which company wrote the code—to permissions. This evidence is collected and inspected when the code is loaded. Code access security matches evidence against the security policy to produce a set of permissions. Security policy is a combination of enterprise security policy, machine policy, user-specific policy, and AppDomain security policy. The general concept of determining permissions from evidence and security policy is discussed in the .NET Framework documentation.
Code access security works by taking the intersection of the privileges at four different runtime security policy levels:
• Enterprise
• Machine
• User
• Host
SQL Server 2005 honors the policies set at other policy levels, so you can effectively subset the three well-defined levels by restricting the privileges at, for example, the machine level.
In most ordinary .NET Framework programs, code access security is used when code is loaded to determine the location (most likely, the file system or network) of the code. .NET Framework assemblies loaded from SQL Server, however, can be loaded from only two places:
• The SQL Server database itself (user code must be catalogued and stored in the database)
• The global assembly cache (Framework class libraries only)
When CREATE ASSEMBLY is run, the code is analyzed, and any outside code that it calls (dependent assemblies) is also catalogued and stored inside SQL Server. Code location evidence means very little for SQL Server assemblies, because .NET Framework code is never loaded from the Internet or the local file system. SQL Server enforces a stronger security policy, using HPAs as well as three levels of security that are declared when the assembly is catalogued. If SQL Server determines that the assembly contains code it shouldn’t be allowed to execute, CREATE ASSEMBLY simply fails. The .NET Framework class libraries are the only code loaded from the global assembly cache, and they are subject to strong constraints, which we will discuss later in this chapter.
Code access security enforces permission-based security through HPAs at execution time as well. With each access to any resource that requires a permission (such as a file or DNS resolver), the code access security inspects the call stack to ensure that every piece of code, up to the original caller, has the appropriate permission. This is known as the stack walk.
Between code analysis at create assembly time and the execution-time stack walk, the .NET Framework code access security system and SQL Server’s extensions to strengthen it ensure that no SAFE code is called that could compromise the stability and security of the system in unforeseen ways. This is a big improvement over pre–SQL Server 2005 compiled code, which consisted of extended stored procedures and COM-based components.
Code Access Security and .NET Framework Assemblies
Because SQL Server controls assembly loading, as well as facets of .NET Framework code execution, it can also assign a custom “safety level” to an assembly. Safety levels determine what non–SQL Server resources .NET Framework assemblies can access. There are three safety levels: SAFE, EXTERNAL_ACCESS, and UNSAFE. These are specified on CREATE ASSEMBLY and changed by using ALTER ASSEMBLY under the control of the database administrator. The different safety levels approximately correspond to the following:
• SAFE—Can access computational .NET Framework classes and local data access using the SqlClient data provider. Safety is equivalent to a T-SQL procedure.
• EXTERNAL_ACCESS—Can access all code that SAFE mode can and, in addition, items such as the file system, the Registry, and other databases through ADO.NET. It is approximately equivalent to a T-SQL procedure that can access some of the system extended stored procedures.
• UNSAFE—Can access most (but not all) code in a subset of the base class libraries. It is approximately equivalent to a user-written extended stored procedure, but it also has the advantage of memory management unless unmanaged memory and pointers are used.
What these different levels can do is enforced by a permission set. SQL Server provides each assembly with decorating methods with HPAs. These HPAs are enforced at execution time (or possibly during Just-in-Time compilation), based on the code’s security level. Because SQL Server HPAs and permission sets are documented, third-party library writers are free to instrument their libraries to be sensitive to SQL Server’s permissions. Table 6-4 shows a summary of the general behavior of each of the named permission sets.
Table 6-4. General Behavior of SQL Server Permission Sets
Following is a more comprehensive list of the permissions afforded assemblies on a permission-set basis. Unless otherwise noted, the assemblies can obtain the Permission initialized with PermissionState.Unrestricted. Note that this list is subject to change in Service Packs and could be affected by the underlying operating system and .NET Framework. Windows XP SP2, for example, forbade almost all use of the managed classes that created raw sockets.
• Permission set for SAFE assemblies
– SecurityPermission(SecurityPermissionFlag.Execute)
• Permission set for EXTERNAL_ACCESS assemblies
– Permission set for SAFE assemblies, plus:
– ConfigurationPermission
– DnsPermission
– EnvironmentPermission
– EventLogPermission
– FileIOPermission
– OleDbPermission
– RegistryPermission
– SocketPermission
– SqlClientPermission
– WebPermission
• Permission set for UNSAFE assemblies
– Unrestricted
Let’s go through an example of how safety levels would work in practice. The short program in Listing 6-14 accesses a search service on the Web. This code uses the classes in the System.Web namespace directly.
Listing 6-14. Assembly must be catalogued as EXTERNAL_ACCESS work for this to work
private static String WebSearch(String subject) {
String url =
"http://www.websearch.com/search?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=";
//Submit Web request and get response
url = String.Concat(url, subject);
WebRequest req = WebRequest.Create(url);
WebResponse result = req.GetResponse();
//Load response stream into string
StreamReader sr = new StreamReader(result.GetResponseStream());
string outstring = sr.ReadToEnd();
sr.Close();
return outstring;
}
We’ll use it as part of a class that is compiled into an assembly. Now we define the assembly to SQL Server, using two CREATE ASSEMBLY statements and two symbolic names with different safety levels:
--Register the unrestricted access privileged assembly
-- Create assembly with external access
CREATE ASSEMBLY searchEA
FROM 'c: ypessearchEA.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
-- Create assembly without external access
-- Must make this strong-named assembly with difference version
-- See chapter 2
CREATE ASSEMBLY searchSafe
FROM 'c: ypessearchSafe.dll'
WITH PERMISSION_SET = SAFE
GO
Then use the symbolic names to define two versions of the same user-defined function, as shown in Listing 6-15.
Listing 6-15. Invoking the network-accessing function in T-SQL
-- Create function on assembly with external access
CREATE FUNCTION WebSearchEA(@sym NVARCHAR(4000))
RETURNS REAL
EXTERNAL NAME searchEA.SearchEngine.WebSearch
GO
-- Create function on assembly with no external access
CREATE FUNCTION WebSearchSafe(@sym NVARCHAR(4000))
RETURNS REAL
EXTERNAL NAME searchSafe.SearchEngine.WebSearch
GO
-- now, attempt to use them
DECLARE @a REAL
-- this will work properly
SET @a = dbo. WebSearchEA('SQL+Server+2005')
PRINT @a
-- this fails with a code access security violation
SET @a = dbo.WebSearchSafe('SQL+Server+2005')
PRINT @a
What happens when a stored procedure that has limited access (SAFE) attempts to call a stored procedure that is declared as UNSAFE? Because of the way the stack walk works, this enforces security in a way that is even more restrictive than SQL Server ownership chains. Remember that an ownership chain is checked only when it is broken—that is, when the caller is not the owner of the object she attempts to access. The stack walk checks permissions all the way up the stack; failing permission at any level will cause the call to fail. This may also have some performance impact, because it implies that every stack walk normally goes all the way up to the top, although an assembly marked as UNSAFE has ability to assert a privilege and stop the stack walk. Code access security provides an additional level of security regardless of the user principal who executes the code.
One final piece of the code access security framework is the AllowPartiallyTrustedCallers assembly-level attribute. This attribute is used to indicate that the assembly can be called by other assemblies that are not fully trusted according to the rules of the code access security system. If a partially trusted assembly (for example, a SQL Server assembly catalogued as SAFE or EXTERNAL_ACCESS) attempts to access a strong-named assembly that is not marked with the AllowPartiallyTrustedCallers attribute, a SecurityException is thrown. Assemblies of subroutines catalogued to SQL Server and used only within SQL Server should, therefore, be marked with the AllowPartiallyTrustedCallers7 attribute to ensure that they can be called by other SAFE or EXTERNAL ACCESS assemblies.
Where Are We?
In Chapter 2, we started by declaring that the most important aspect of any database’s execution is security. SQL Server 2005, like every new version of SQL Server, includes features that make the system more secure. SQL Server 2005 mandates password-policy enforcement for SQL Server–based logins, providing equivalent safety to Windows integrated logins. SQL Server 2005 metadata uses access control like the rest of SQL Server, to prohibit arbitrary access without permission. SQL Server 2005 permits procedural code to specify its execution context, making properly written dynamic SQL safer. Finally, it improves on extended stored procedures with verifiable custom hosted .NET Framework code.
Because .NET Framework code cannot only access SQL Server objects, but also call out to the .NET Framework base class libraries, .NET Framework code inside SQL Server is subject to three levels of checking. The base class libraries are classified to determine which are safe to use, and SQL Server will refuse to load any library deemed to be inapplicable or unsafe. .NET Framework procedural code, including assemblies and user-defined types and aggregates, is subject to normal SQL Server user authorization checks. Finally, SQL Server defines three different security levels for .NET Framework code that can be specified at CREATE ASSEMBLY time. Each of the base class libraries was outfitted with custom permissions that mandate what the assembly will be able to do at each level. This is enforced via .NET Framework code access security.