RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQLCLR Security and Designing for Reuse

SQL Server's CREATE ASSEMBLY statement lets you catalog code with 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, and you need to understand both.

n important principal of software design is that of "least privilege." Basically, in any given layer of a program, you should grant only 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.

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.

All the examples in this article assume that you have turned on the TRUSTWORTHY database setting. This setting has quite a few implications, and it's important to fully understand its role before enabling it in production environments. For the sake of this article the setting helps to greatly simplify the examples, as it lowers the amount of work required to host an UNSAFE assembly. For a straightforward description of how to make these examples work if you have not marked your database as trustworthy, this blog post by SQL Server MVP Kent Tegels should help.

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.

By not carefully considering SQLCLR security issues when designing your SQLCLR code base, you may be allowing your code too much access.
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 via permissions 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.

A CAS Exception Example
To observe what this means, create a new assembly containing the following CLR stored procedure:

   public static void CAS_Exception()
      using (System.IO.FileStream fs =
         new FileStream(@"c:\b.txt", 
         //Do nothing...
Catalog the assembly as SAFE and execute the stored procedure. This procedure will result in the following output (truncated for brevity):

   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.

Editor's Note: This article was first published in the May/June 2007 issue of CoDe Magazine, and is reprinted here by permission.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date