SQLCLR Security and Designing for Reuse
An important principal of software design is that of “least privilege.” Basically, in any given layer of a program, you should only grant minimal access such that the code has rights to only exactly the resources it needs to get its job done-and nothing more. Most SQL Server developers understand this concept: one of the main reasons to use stored procedures is to encapsulate permission to data behind controlled and auditable interfaces, thereby not giving the caller direct access.
Bringing the Common Language Runtime (CLR) into SQL Server presents an entirely new set of challenges with regard to privilege and some of the rules that SQL Server developers are used to do not completely translate. Simple grant/deny/revoke logic still applies, but the CLR also brings its own set of specialized permissions, which require slightly different handling in order to properly manipulate.
By not carefully considering these issues when designing your SQLCLR code base, you may be allowing your code too much access.
This article focuses on what the SQLCLR permission sets do to control security and reliability, and what you need to understand when working with them in order to design a system that takes advantage of least privilege. By not carefully considering these issues when designing your SQLCLR code base, you may allow your code too much access, thereby creating potential vectors for security problems down the road.
I will also show you how to work with the permissions system to create fine-grained, reusable utility methods. Creating a core set of well-audited methods will help you not only to reduce your higher-privilege surface area, but also to write less code by reusing your existing logic.
CREATE ASSEMBLY and Permission Buckets
Before you can expose a SQLCLR routine within SQL Server, you must catalog the assembly in which it resides. You do this via SQL Server’s CREATE ASSEMBLY statement. In addition to loading the assembly into SQL Server, the statement allows the DBA to specify one of three code access security “buckets” that dictate what the code in the assembly is allowed to do.
These buckets are SAFE, EXTERNAL_ACCESS, and UNSAFE. SQL Server nests the permissions it grants to each level to include the lower levels’ permissions. The SAFE bucket provides limited access to math and string functions, along with data access to the host database only. EXTERNAL_ACCESS adds the ability to communicate outside of the SQL Server instance. And UNSAFE allows the ability to do pretty much anything you want-including running unmanaged code.
What may not be readily apparent is that each bucket actually controls two distinct methods by which the SQLCLR environment enforces security. Enforcement is done both via Code Access Security (CAS) grants and permissions that are based on a new .NET 2.0 attribute called HostProtectionAttribute (HPA). On the surface, the difference between HPA and CAS is that they are opposites: CAS permissions dictate what an assembly can do, whereas HPA permissions dictate what an assembly cannot do.
But beyond this basic difference is a much more important differentiation: The SQLCLR environment checks CAS grants dynamically at run time by doing a stack walk as code executes, whereas it checks HPA permissions before calling methods in an assembly. To observe what this means, create a new assembly containing the following CLR stored procedure:[SqlProcedure]
public static void CAS_Exception()
using (System.IO.FileStream fs =
Catalog the assembly as SAFE and execute the stored procedure. This procedure will result in the following output (truncated for brevity):Starting...
Msg 6522, Level 16, State 1,
Procedure CAS_Exception, Line 0
A .NET Framework error occurred during
execution of user-defined routine
or aggregate "CAS_Exception":
Request for the permission of type
The exception thrown in this case was a SecurityException, indicating that this was a CAS violation (of the FileIOPermission type). But before it hit the exception, the procedure successfully called the SqlPipe.Send method, referenced in the first line. This is made apparent by the inclusion of the string “Starting…” in the output. Avoiding this particular violation using the SQLCLR security buckets would require cataloging the assembly using the EXTERNAL_ACCESS permission.
To see how HPA exceptions behave, try the same experiment again, this time with the following stored procedure (again, cataloged as SAFE):[SqlProcedure]
public static void HPA_Exception()
As before an exception occurs, but this time it is a bit different:Msg 6522, Level 16, State 1, Procedure
HPA_Exception, Line 0
A .NET Framework error occurred during
execution of user-defined routine
or aggregate "HPA_Exception":
to perform an operation that was
forbidden by the CLR host.
Unlike when executing the previous stored procedure, this method did not call the SqlPipe.Send method before hitting the exception-the “Sending…” string does not appear in the output. As a matter of fact, the execution context never entered the HPA_Exception method at all. The runtime threw the exception based on reflection done before actually running it. You should also note that the wording of the exception is a bit different: This time the runtime did not deny a request, but rather the code attempted a “forbidden” operation! The SQLCLR environment considers threading and synchronization to be threats to reliability and therefore explicitly disallows them, using the HPA model, for any code cataloged in the SAFE or EXTERNAL_ACCESS buckets.
Generally speaking, CAS grants are all about security-keeping code from being able to access protected resources. On the other hand, HPA permissions are more geared towards reliability and ensuring that SQL Server runs smoothly and efficiently. But both types of permission have the same net effect of controlling what code can and cannot do, and developers can control both to some degree.
A full list of what the SQLCLR environment does and does not allow based on the CAS and HPA models is beyond the scope of this article, but is well-documented. Refer to the following MSDN topics:
When SAFE Just Isn’t Enough
Up to this point, I have not addressed why you should care about permissions behavior when you catalog assemblies as SAFE. The fact is, it’s easy enough to fix these exceptions: Simply raise the permission level of the assemblies to EXTERNAL_ACCESS or UNSAFE and give the code access to do what it needs to do!
Unfortunately, as with most simple workarounds, the fix does not come without cost. In the SQLCLR environment, you grant permissions at the assembly level, rather than the method or line level. Raising the permission of a given assembly can affect many different modules contained within. And granting extra permissions en masse on every module in the assembly creates a maintenance burden: If you want to be certain that there are no security problems, you must review each and every line of code to make sure it’s not doing anything it’s not supposed to do-you can no longer trust the engine to check for you.
Even if it were possible to set permissions at the module level, that may not be granular enough. Consider a complex, 5000-line module, which requires a single file IO operation to read some lines from a text file. By giving the entire module EXTERNAL_ACCESS permissions, you’re creating yet another maintenance nightmare-you now have to check all of the remaining code to make sure it’s not doing anything unauthorized.
The situation gets even worse with threading and synchronization code. A fairly common SQLCLR pattern is to create static collections that can be shared amongst callers. However, properly synchronizing access is important in case you need to update some of the data after the collection has been initially loaded. But due to the fact that threading and synchronization require UNSAFE access, this creates a rather unappealing situation from a permissions point of view.
Is doing a stringent code review every time you make a change enough to ensure that the code won’t cause problems? And do you really want to have to do a full review every time you make a change? Ideally, you want to safeguard the majority of code, which doesn’t do anything that requires a high level of privileges, and yet still allow access to do the occasionally necessary privileged operations.
Enter the Solution: Encapsulation!
The general solution to this situation is to encapsulate higher-privileged operations inside of their own specially granted assemblies and reference those assemblies from SAFE assemblies. Proper implementation of this solution has two key benefits.
First, encapsulation greatly reduces the maintenance issue. Although you still need to do a rigorous code review for the higher-privilege assemblies, overall you’ll review a lot less code. Furthermore, you will not have to do an intensive security review for the SAFE assemblies-the engine will make sure they behave. And if you’re really concerned, you can encapsulate logging within the higher-privileged methods in order to help find out when and if the callers try to do anything strange.
In addition, you can potentially reuse this encapsulation solution. For example, consider the module that needs to read a few lines of a text file. Instead of coding the file system work directly in the module, you can catalog an external assembly that exposes a method that takes a file name as input and returns a collection of lines. Future modules that need to read lines from text files can reference the same assembly, and therefore not have to re-implement this logic. And since you will have already reviewed the assembly, you don’t need to take further action on that front just because a new caller uses it. Reusing modules like this is a common pattern in the world of object-oriented development, and it certainly has its place when working with SQLCLR integration.
As mentioned previously, there are two distinct methods that the runtime uses to enforce code security, both of which it handles differently at run time. As a result, granting lower-privileged code access to protected resources requires slightly different techniques depending on whether the code needs to override HPA or CAS permissions. In the following sections, I’ll go through the encapsulation techniques necessary to make this work.
By: Adam Machanic
Adam Machanic is an independent database software consultant, writer, and speaker based in Boston, Massachusetts. He has implemented SQL Server solutions for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. Machanic has written for SQL Server Professional magazine, serves as SQL Server 2005 Expert for SearchSQLServer.com, and is coauthor of Pro SQL Server 2005 (Apress, 2005). In addition, he regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified IT Professional (MCITP). His latest book, Expert SQL Server 2005 Development (Apress), is due out early in 2007.
SQL Server’s CREATE ASSEMBLY statement allows you to catalog code using one of three predefined security buckets. But under the covers, there are actually two distinct security models at play: Code Access Security and Host Protection.