Security in the CLR World Inside SQL Server : Page 3

Is running .NET Framework code within SQL Server 2005 exciting or a threat? Which is it? This article explores the security issues of SQLCLR code so that both developers and DBAs can make informed decisions about its use.

by Don Kiely

May 22, 2006

Page 3 of 6

SQL Server-Level Security
SQL Server 2005 has implemented a far more granular permissions scheme than earlier versions of SQL Server. This granularity extends to SQLCLR code, for which there are three primary permissions required to install and run the code.

The CREATE ASSEMBLY permission is required to run the CREATE ASSEMBLY statement that installs a SQLCLR assembly into a database.

A non-admin must have the EXECUTE permission on a code module in order to run the code. A sysadmin can run any code.

The owner of the code must have the REFERENCES permission to reference other objects, such as with foreign keys and create view with schemabinding. (This permission is not required to run code located within the same .NET assembly.)

Beyond these permissions, the user or login that causes SQLCLR code to execute must have the usual SELECT, INSERT, DELETE, or UPDATE permissions on tables referenced within the code. There is nothing in how the SQLCLR is hosted within SQL Server 2005 that circumvents the need for these permissions, depending on what the code does to the data in the table. Further, the permission checks hook into the new execution context feature in SQL Server 2005, so that when defining a SQLCLR stored procedure or function you can specify the execution context of the code using the EXECUTE AS clause.

CLR Security
Up to this point in the article I've been talking about the security environment of .NET code hosted and executing within SQL Server, from the perspective of SQL Server looking in at the SQLCLR code module. But the CLR imposes its own security model. This model kicks in once SQL Server is happy with all the permissions checking and allows the code to execute. Just because it can execute doesn't mean that it can do anything it wants.
The CLR provides a number of services to the .NET code it runs as well as to the host in which it is operating. These include type safety checking to validate that the code accesses memory structures in well-defined ways, role-based security based on who is running the code, code access security under which permissions are granted based on code characteristics rather than just on who is running the code, and application domains that provide safe execution zones within the host process.

All assemblies that have the same owner in the database are loaded into the same AppDomain, no matter which database they are installed in. Each assembly in an AppDomain can discover each other through reflection. Since they have the same owner, SQL Server doesn't have to perform its own permission checking, which helps performance. But these measures don't circumvent code access security, which remains in effect.
The CLR also enforces host protection attributes (HPA) that allow a hostSQL Server in this caseto control what parts of the .NET Framework the SQLCLR code is allowed to use. These are more in the realm of reliability rather than security; see the sidebar, Reliability through Host Protection Attributes, for a description of these attributes.

Code Access Security
One of the most important services provided by the CLR is code access security (CAS). The fundamental principle of CAS is that privileges are granted to code, not to users. This may seem strange if you're used to the Windows or SQL Server scheme of assigning permissions to users and logins rather than code they are executing. But even if SQLCLR code is executing in the security context of an administrative user, it may not have the full range of available permissions. In fact, SQLCLR code executing within SQL Server almost certainly will not have all permissions, what is called full trust.
Here are the basics of how CAS works. When loading an assembly in response to the invocation of a SQLCLR stored procedure, function, or other code module, the CLR gathers evidence. It uses that evidence to assign the assembly to one or more code groups. The assigned code groups each has a permission set assigned through a runtime security policy that uses membership conditions to determine where code is assigned. A permission conveys a right to do something that is protected. Code demands that callers have certain permissions to perform the privileged action.

If these are foreign concepts to you, you'll need to bone up on this important part of developing secure applications. Understanding CAS is critical for understanding the permissions SQLCLR code has when executing.
So how does SQL Server bring together the SQL Server and CLR security environments? The first thing to understand is that there are two sets of resources being protected by these systems. The first set consists of SQL Server objects and data. SQL Server's security environment protects access to its own objects, even for SQLCLR code it hosts.

The CLR protects access to everything else. What is everything else? It is the resources outside of the SQL Server instance, including disk files, registry settings, other databases, network resources, and Web services. Anything and everything that exists outside of the SQL Server instance where the code is running. This means that CAS does nothing at all to protect anything at all within its host SQL Server instance.
Pause here a moment and let that sink in. Having a clear understanding of which security system protects what is critical for what is coming, so here is another way to state the same thing: The permissions granted in SQL Server protect all of its data and objects from any kind of executing code, whether the code is written in T-SQL or SQLCLR. The CLR's CAS protects access to all resources outside of SQL Server.

A corollary to this then is that CAS does nothing to protect a SQL Server instance's objects or data. Not a bloody thing.
Got that? Now I'll flesh out a few more details about CAS. But keep in mind that the permissions I'm talking about now are not those within SQL Server but are out in the operating system. For example, say that SQLCLR code has to open a disk file to record some logging data, or to make a connection to read data from another database. CAS permissions restrict how the code can access that disk file and the connection to the other database.

Whenever the CLR loads an assembly in order to run some method, it gathers evidence about the assembly that it matches with policy defined on the machine to grant permissions. Evidence for .NET assemblies typically involves both location/origin datawhere the assembly is being run fromand identity data. But since a SQLCLR assembly runs from within SQL Server, location evidence is not relevant. That leaves identity evidence, things such as whether the assembly has a strong name or is digitally signed by a particular company.

Figure 3. Four Policay Levels: Intersection of CAS permissions from four policy levels

The CLR takes that evidence and matches it up against four levels of policy: Enterprise, Machine, User, and AppDomain. (The SQL Server documentation often calls the AppDomain level "Host Policy," but it's the same thing. AppDomain is the more typical term in the .NET Framework and is the one I use.) The actual set of permissions granted to an assembly by the CLR is the intersection of the permissions granted at each level.

Figure 3 shows how this works. Each of the four levels has its own permission set. To determine the set of permissions granted to an assembly, the CLR takes the intersection of permissions-that is, the common set of permissions, each of which is present in all four permission sets-and grants that intersect set to the assembly.
You can examine the first three policy levels-enterprise, machine, and user-using the .NET Framework 2.0 Configuration tool available from the Administration Tools applet in Control Panel on a machine with the framework installed. Figure 4 shows the tool when you've expanded the Runtime Security Policy section of the TreeView control to show the policy levels.

It is here that a user or administrator can modify the default policy for the levels shown so that an assembly gets more or fewer permissions when it loads. This can be a rather complex subject, but for SQLCLR code-in fact, all .NET code that is installed on the local machine-these three policy levels by default assign full trust to an assembly. Full trust simply means that the code automatically has every possible permission. More precisely, it means that the CLR doesn't do any permission checking whatsoever.
Huh? If the assembly by default has permissions checking short circuited, why did I make you read all this stuff about CAS? Shouldn't I have saved you the pain and gotten right to the good stuff?

The reason is that there are four policy levels used by the CLR to assign permissions, but only three are configured using the tool shown in Figure 4. The fourth is the AppDomain level that is created when you install an assembly into a database. That policy level is controlled by SQL Server as the CLR host. And SQL Server will rarely grant an assembly full trust because it can be an extreme risk to both security and reliability.
Figure 5 shows the situation as it actually happens with SQLCLR code by default. (Keep in mind that a user or administrator can change the policy settings of the enterprise, machine, and user levels, in which case the situation is more like that in Figure 3.) Because the enterprise, machine, and user policy levels grant full trust, they are the same resulting permission set: all permissions. That permission set is then intersected with the AppDomain permission set to determine the final intersect set that becomes the permissions for the assembly.