Microsoft knew that allowing .NET code to run inside the SQL Server 2005 engine would raise security-related concerns among database administrators. SQL Server 2005 addresses this concern by introducing a security architecture that combines SQL Server's user-based model with the .NET Framework's code-accessbased security model. You as an administrator have complete control over what the SQLCLR can and cannot do.
The code access security in .NET is based on permissions defined in the configurable Runtime Security Policy (which you access by selecting Start | Programs | Administrator Tools | .NET Framework 2.0 Configuration). Based on where the .NET assembly originated and the user running the assembly, the administrator can determine what that assembly can or cannot do. The .NET Framework security is provided via four configurable policy levels: Enterprise, Machine, User, and Host. The assembly receives the permissions based on the intersection of permissions defined in these policies. The highest level is Enterprise, which describes the policy for an entire application. The next level is the Machine policy, which affects all .NET assemblies running on the machine. For instance, if you define Machine policy that disallows writing or creating new entries in the registry under the HKLM hive, then all assemblies running on that machine will not be able to create entries or write into the HKLM hive in the registry. The User policy allows assigning different permissions to the same assembly based on the users executing it. In the case of SQL Server 2005, it is the account under which SQL Server 2005 service is running. The Host policy is defined by the host of the .NET CLRin this case, SQL Server 2005.
SQL Server 2005 Host policy allows you to put user assemblies into one of following three permissions buckets:
When you import a .NET assembly by using the CREATE ASSEMBLY DDL statement, you can put that assembly in one of the three permissions buckets by using the WITH PERMISSION_SET = clause, as illustrated here:
CREATE ASSEMBLY sqlclrXMLHelper FROM 'E:\Dev\sqlclrXMLHelper.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;
When the SQLCLR .NET code is executed, the intersection of permissions defined in Machine policy, User policy, and Host policy (the permissions bucket) determine what the code can or cannot do. The .NET integration in SQL Server 2005 and security considerations are further discussed in Chapter 11, "SQL Server 2005 and .NET Integration."