Browse DevX
Sign up for e-mail newsletters from DevX


SQLCLR Security and Designing for Reuse : Page 4

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Encapsulating Code Access Security Privileges
Given the ease with which you can encapsulate HPA-protected resources, you might assume that code access permissions are equally straightforward to deal with. Unfortunately, because the runtime checks CAS grants dynamically via a stack walk, you cannot simply reference a second assembly—the runtime walks the entire stack each time, without regard to assembly boundaries.

To illustrate this, create a new assembly containing the following method, which reads all of the lines from a text file and returns them as a collection of strings:

public static string[] ReadFileLines( string FilePath) { List<string> theLines = new List<string>(); using (System.IO.StreamReader sr = new System.IO.StreamReader(FilePath)) { string line; while ((line = sr.ReadLine()) != null) theLines.Add(line); } return (theLines.ToArray()); }

Catalog the assembly in SQL Server with EXTERNAL_ACCESS permission, and reference it from the assembly that contains the CAS_Exception stored procedure (you should still catalog the assembly as SAFE). Modify that stored procedure as follows:

[SqlProcedure] public static void CAS_Exception() { SqlContext.Pipe.Send("Starting..."); string[] theLines = FileLines.ReadFileLines(@"C:\b.txt"); SqlContext.Pipe.Send("Finished..."); return; }

Note that I created my ReadFileLines method inside a class called FileLines; reference yours appropriately if you use different class names.

Running the modified version of this stored procedure, you'll find that you receive the same exception as before. The CAS grant did not change simply because you referenced a higher-privileged assembly, due to the fact that the stack walk does not take into account referenced assembly permissions.

Working around this issue requires taking control of the stack walk within the referenced assembly. Due to the fact that the assembly has enough privilege to do file operations, it can internally demand that the stack walk ignore those operations, even when you call it from another assembly that does not have the requisite permissions. To do this, use the Assert method of the IStackWalk interface, exposed in .NET's System.Security namespace.

Take a second look at the CAS violation and you'll note that the required permission is FileIOPermission, which is in the System.Security.Permissions namespace. The FileIOPermission class happens to implement the IStackWalk interface. To solve this particular encapsulation problem, instantiate an instance of the FileIOPermission class and call the Assert method.

The code below shows a modified version of ReadFileLines that uses the FileIOPermission class to enable all callers to do whatever file I/O-related activities that the assembly has permission to do:

public static string[] ReadFileLines( string FilePath) { //Assert that anything File IO-related that //this assembly has permission to do, //callers can do FileIOPermission fp = new FileIOPermission( PermissionState.Unrestricted); fp.Assert(); List<string> theLines = new List<string>(); using (System.IO.StreamReader sr = new System.IO.StreamReader(FilePath)) { string line; while ((line = sr.ReadLine()) != null) theLines.Add(line); } return (theLines.ToArray()); }

Note that the "unrestricted" permission only allows the same amount of unrestricted access as the assembly could get anyway. There are also other overloads for the FileIOPermission constructor that you can use to control things in a more granular level. For instance, you can pass an enumeration called FileIOPermissionAccess to some of the overloads to do things like allow read access to only a specific file.

File I/O is only one kind of permission from which you might see a CAS exception. The System.Security.Permissions namespace contains several classes that deal with various types of code access. You can determine which class you need to use by looking at the exception and seeing which permission the runtime denied. In every case, you can use the same basic pattern I've outlined here to perform the required encapsulation.

Better "Safe" Than…
SQLCLR integration in SQL Server 2005 brings several ideas to the table that SQL Server developers must fully understand in order to effectively work within the new environment. Permissions are but one class of these issues, but probably the most important.

The idea of encapsulation should be nothing new to SQL Server or .NET developers, and the methods I've presented here are just another way to apply encapsulation. Although the focus is on working towards least privilege, the potential for greater reuse is a definite bonus that you should not overlook. By putting in a bit of time and effort to learn how to work with the permissions system instead of against it, you can create more secure, robust code with much less exposed surface area—and that is always better than being sorry later!

Author's Note: I would like to acknowledge Bob Beauchemin of SQL Skills and Steven Hemingray of Microsoft, who helped me through a few of the rough spots I encountered while researching this article.

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 specializes in .NET data access layer performance optimization. Adam has written for SQL Server Professional magazine, serves as SQL Server 2005 Expert for SearchSQLServer.com, and is a co-author 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 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.
Thanks for your registration, follow us on our social networks to keep up-to-date