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


SQLCLR Security and Designing for Reuse : Page 2

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.

An HPA Exception Example
To see how HPA exceptions behave, try the same experiment again, this time with the following stored procedure (again, cataloged as SAFE):

   public static void HPA_Exception()
      lock (SqlContext.Pipe)
         //Do nothing...
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":
     HostProtectionException: Attempted 
     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; instead, the code attempted a "forbidden" operation! The SQLCLR environment considers threading and synchronization to be threats to reliability and therefore explicitly disallows them via HPA 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.

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