Security in the CLR World Inside SQL Server : Page 6

It's Secure Enough
Table 3 contains a summary of the three permission sets available for SQLCLR assemblies and what kind of protections SQL Server provides for each.

• Code Access Security is the permissions set that the CLR manages within the code.
• Programming Model Restrictions are the host protection attributes as well as whether the code can use statics.
• Verifiability refers to whether or not SQL Server verifies the relative safety of the code when you install it using the CREATE ASSEMBLY statement.
• Call Native Code indicates whether the code can call Win32 APIs or do a platform invoke to external components.

 

Table 3: Permission set summary.

	Permission Set
Types of Protection	SAFE	EXTERNAL_ACCESS	UNSAFE
Code Access Security	Execute Only	Execute & limited access to external resources	Unrestricted
Programming Model Restrictions (Host Protection Attributes)	Yes	Yes	None
Verifiability Required	Yes	Yes	No
Call Native Code	No	No	Yes

As you can see, SQL Server can provide a nice sandbox for SQLCLR code that protects both the security of the data and the stability of the server, as long as you limit code to SAFE or to EXTERNAL_ACCESS with incredibly thorough code reviews.

Here's a quiz to test your understanding of SQLCLR security. It is possible to use a regular connection string and ADO.NET to access another database, maybe an Oracle or Access database? Given what you now know about accessing external resources, what SQLCLR permission set level would an assembly that accesses an Oracle database need?

Think about it before reading further.

Hint: The only managed database provider included with the .NET Framework is System.Data.SqlClient.

Got it? The assembly must be installed as UNSAFE. Why? Because the code would have to use the System.Data.OleDb objects. Because these COM-based objects, meaning unmanaged code, the assembly needs to be installed as UNSAFE because that is the only level that can access unmanaged code.

Lest you think this is Microsoft's way of bashing Oracle, the answer is the same for accessing a Microsoft Access database, since it too is based on OLE DB and thus unmanaged code.

I'll make a bold statement here: UNSAFE code should never ever be used on a production server. Unless it is trivial code that can be thoroughly reviewed and firmly validated to verify that it will do no harm to the server, you just won't be able to do enough to be confident that it is safe. While I won't rule out that there are valid uses for UNSAFE code, I'd have to think long and hard about whatever the code does is worth the risk. I generally feel the same way about extended stored procedures, which are equally risky but have to be written in complex C++ code.

I'll go so far as to say that any DBA that allows UNSAFE code to be installed on a production server is nuttier than a fruitcake unless proven otherwise. And I say that as a developer who often butts heads with DBAs, most of whom I know are nuttier than a fruitcake!

But SAFE code can do no more harm than a T-SQL stored procedure can do, and EXTERNAL_ACCESS is a reasonable compromise when you need to access external resources. So lay aside your fear of the unknown, and consider letting SAFE code into your database. Then consider EXTERNAL_ACCESS code when it makes sense for the database, applications, and users. Microsoft did a nice job at making that kind of code secure and reliable.

Previous Page