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


SQLCLR Security and Designing for Reuse : Page 3

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.

Enter the Solution: Encapsulation
The general solution to this situation is to encapsulate higher-privileged operations in their own assemblies and reference them 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.

Encapsulation of HostProtection Privileges
For an example of where you might use the encapsulation technique to get around HPA restrictions, consider a SQLCLR UDF used to do currency conversions based on exchange rates:

   public static SqlDecimal GetConvertedAmount(
      SqlDecimal InputAmount, SqlString InCurrency, 
      SqlString OutCurrency)
      //Convert the input amount to
      //the base
      decimal BaseAmount =
         GetRate(InCurrency.Value) * 
      //Return the converted base amount
      return (new SqlDecimal(
         GetRate(OutCurrency.Value) * BaseAmount));
This method makes use of another method, GetRate:

   private static decimal GetRate(
      string Currency)
      decimal theRate;
         theRate = rates[Currency];
      return (theRate);
GetRate performs a simple lookup in a static generic Dictionary called rates to find the exchange rate for a given currency. Because another thread might be updating the rates concurrently, GetRate handles synchronization using a static instance of ReaderWriterLock, called rwl. Because you're working in the SQLCLR world and want to keep your assemblies cataloged as SAFE, you must mark both the dictionary and ReaderWriterLock as readonly (this is a SQLCLR restriction):

   static readonly Dictionary<string, decimal>
   rates = new Dictionary<string, decimal>();
   static readonly ReaderWriterLock 
      rwl = new ReaderWriterLock();
Alas, setting the readonly property on the static members is not enough. If you catalog this code as SAFE, it fails due to its use of synchronization (the ReaderWriterLock)—running it produces a HostProtectionException.

Encapsulate higher-privileged operations in their own assemblies and reference them from assemblies marked SAFE.
To solve this, move the affected code into its own assembly cataloged as UNSAFE. Because the runtime evaluates the host protection check for each method called, you can mark the outer method SAFE and temporarily escalate its permissions by calling into an UNSAFE core.

Think about what code you should actually move into the core. The Dictionary isn't causing the problem—the ReaderWriterLock is. But wrapping methods around a ReaderWriterLock does not seem like it would promote very much reuse; at least, not in the scenarios I commonly see. Instead, if you wrap the Dictionary and the ReaderWriterLock together you'll create a new ThreadSafeDictionary class (Listing 1). I haven't implemented all the methods in the code example, but you should see enough there to get you started.

You should place this class into an assembly cataloged as UNSAFE. Use a reference to the UNSAFE assembly in the exchange rates conversion assembly, after which you will have to change a few lines of code. First of all, the only static object that you must create is an instance of ThreadSafeDictionary:

   static readonly 
      decimal> rates =
      new ThreadSafeDictionary<string, 
Secondly, the GetRate method no longer needs to be concerned with synchronization. Lacking this requirement, its code becomes greatly simplified:

   private static decimal GetRate(
      string Currency)
      return (rates[Currency]);
You can still mark the exchange rates conversion assembly SAFE and can use the encapsulated synchronization code without throwing a HostProtectionException. Furthermore, the assembly's code will not be able to use resources that violate the permissions allowed by the SAFE bucket. This is quite an improvement over the initial implementation, from a security perspective.

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