Speculations on the suprisingly under-documented world of SQLCLR CAS permission grants

Nicole Calinoiu
November 2005

Documentation update lags strike again

I'd been hoping that the details of the SQLCLR CAS permission sets might make it into the SQL Server Books Online or other relevant documentation by the RTM timeframe. Unfortunately, I can't seem to find anything that even begins to resemble a listing of the permissions, never mind coverage of some of the pickier details of their assessment and consequences. I'd already started trying to investigate some of this on my own during the beta and, after spending a bit more time with the RTM build (i.e.: pretty much wasting a perfectly good Saturday), here's what I think I've discovered so far...

What's in the permission sets?

Unsurprisingly, the UNSAFE permission set is the unrestricted permission set, or "full trust". The SAFE and EXTERNAL_ACCESS permission sets are restricted permission grants, which are loosely described in the Books Online topic Designing Assemblies. These two include a mix of identity and "regular" CAS permissions, all of which are based on permission types available in the mscorlib assembly. The story around identity permission assignment is potentially a bit more complex than in the stand-alone CLR, and I'll come back to that later. In the meantime, here are the the details of the non-identity permissions as dumped from the RTM build...

SAFE-level permissions

In case you hadn't already noticed the links above, you can grab the the XML definition of the SAFE permission set from here. If you're not familiar with "permission-ese", the following table might make for an easier read.

The AllowBlankPassword = false restriction on the SqlClientPermission grant appears to have no effect. Since only the context connection can be used, and the Password property can't be specified in the connection string, the only situation in which a blank password might still apply is when the context connection was established using a SQL Server login account with a blank password. However, my tests indicate that use of a context connection with a blank password is actually allowed under the SAFE permission set, so this permission restriction appears to have no practical effect. I have no idea whether this is a bug or if the limitation was simply applied to the permission grant for reasons of defense-in-depth.

EXTERNAL_ACCESS-level permissions

The XML definition of the EXTERNAL_ACCESS permission set is available here, and the English version is...

The inclusion of SecurityPermission\Assertion gives code at the EXTERNAL_ACCESS level the right to assert its elevated permissions on behalf of less privileged code (usually code running at the SAFE level). While this might be useful when developing assemblies that access external resources, I tend to see assertion as a potentially very risky activity, and I'm a bit surprised that it was included at the EXTERNAL_ACCESS level instead of just at the UNSAFE level. At any rate, it's there now, and DBAs should be aware that by granting EXTERNAL_ACCESS, they're potentially allowing code at the SAFE level to access external resources in ways they might not have anticipated.

I'm guessing that the SecurityPermission\ControlPrincipal grant was probably included in order to enable code at the EXTERNAL_ACCESS level to impersonate the SQL session user when attempting to access external resources (assuming, of course, that Windows authentication was used when establishing the connection). However, such impersonation can actually be accomplished without this permission, leaving me wondering why it was actually included.

Code with SecurityPermission\ControlPrincipal is able to change the .NET-specific thread principal, which has nothing to do with access to external resources. However, the permission can be exploited to control user rights in some applications, which means that code with permission to control the principal may be able to elevate user privileges in such applications, and DBAs may need to consider this before granting EXTERNAL_ACCESS.

It's also worth noting that, contrary to at least some of the official documentation (e.g.: http://msdn2.microsoft.com/en-us/library/ms131108), even code at the SAFE level can impersonate the session user. This could lead to some potential security holes if such code can call into another assembly that asserts permissions for external actions that it does not have the right to perform on its own. Code running at either the EXTERNAL_ACCESS or UNSAFE level that accesses external resources after asserting the permissions required to do so should probably verify that it is using the expected Windows user context. Otherwise, less privileged invoking code may successfully initiate impersonation in a manner that could result in an escalation of privilege.

Interaction with other permission-restriction mechanisms

On top of the SQLCLR CAS permission set, assemblies hosted by the SQLCLR are also subject to restriction of permissions via the general CAS policy on the machine and the use of assembly-level permission attributes. An assembly hosted by the SQL CLR will be granted the intersection of the permissions granted under its SQLCLR CAS permission level, the general CAS policy on the machine, and its assembly-level permission attributes.

On a practical note, any changes to the general CAS policy on the machine may not be picked up until after the SQL Server service is restarted.

So what's up with those identity permissions?

There are six identity permissions included in the core .NET Framework. Of the six, it would appear that only StrongNameIdentityPermission and ZoneIdentityPermission are ever assigned by the SQLCLR. Details of the apparent assignment of these permissions appear in the table below.

Hmm... Sounds like something funny might be going on with the assembly evidence...

From what I can tell, it looks like SQL Server doesn't even bother storing any information regarding the location from which an assembly was originally loaded. This actually makes quite a bit of sense since using such information could lead to potential escalation of CAS privilege by assemblies with low permission grants. After all, even SAFE assemblies can run arbitrary T-SQL against the context connection, which would mean that they could potentially alter any locally stored data about their own assembly. If the SQLCLR used any data at all from outside the assembly definition itself to determine the assembly's evidence, even very low privilege assemblies would potentially be able to cause their own permission grants to be increased by mucking about with their stored evidence.

Well, then, if the SQLCLR doesn't provide any evidence about the origin of the assembly (aside from the essentially useless Zone = MyComputer information), what evidence can you use if you want to decrease an assembly's permission grant by fiddling with the general CAS policy on the machine? Strong name evidence is provided for strongly named assemblies, so that's an obvious candidate. However, what can you do when an assembly isn't strongly named?

The good news is that the SQLCLR provides System.Security.Policy.Hash evidence for hosted assemblies, and this can be used as the membership criterion for a code group in the general CAS policy. The bad news is that the SQLCLR host doesn't derive the same hash value for any given assembly as does the stand-alone CLR if the assembly is signed with an authenticode signature (even though the signature does seem to appear in the assembly definition stored within SQL Server). Therefore, if the assembly does have an authenticode signature, you must read its hash evidence from within the SQLCLR instead of via any of the tools provided for use with the stand-alone CLR.

The really bad news is that the above approaches work only to decrease the permission grants of as-deployed assemblies. If a hosted assembly is altered for any reason (like, say a routine patch application), chances are excellent that any permissions you might have denied via membership in a code group that matches on strong name or assembly hash will become available to the new version of the assembly. This means granting, not denying, permissions based on strong name and/or hash would be a safer approach overall. However, this requires that assemblies running from the local computer zone would have to be granted little or no permissions by default, which can lead to some major troubleshooting headaches. If you decide to go this route, extensive testing of any deployments (including patches) on an identically configured staging server would be a very necessary step.