CLR security for SQL Server2005 SQLCLR code

First, CLR security

In the first article, we have discussed the security environment for the. NET code that the host suitable which executes in SQL Server-to observe the SQLCLR code module from the perspective of SQL Server. But the CLR uses its own security model. Once SQL Server agrees to all permission checks and allows code execution, the model is "forced to intervene." Just because it can be executed does not mean that it can do anything it wants to do.

The CLR provides many services for the. NET code it runs and the hosts it runs. These services include:

1 Type Security check-Verify that the code can access the memory structure in a well-defined manner;

2 role-based Security-based on who runs the code;

3 Code access Security-In this case, licensing is based on code characteristics rather than on who is running the code;

4 application domain-it provides a secure execution zone in the host process.

All assemblies with the same owner in the database are loaded into the same AppDomain, regardless of which database they are installed in. Each of the assemblies in a AppDomain can find each additional assembly by reflection. Since they have the same owner, SQL Server does not have to perform its own permission checks, which can help improve performance. However, these measures do not address the actual code access security problem.

The CLR also enforces host protection properties (HPA)-Allowing a host (in this case, SQL Server) to control the specified portion of the. NET Framework by allowing SQLCLR code to be used. In fact, in terms of reliability, there are other aspects besides security.

Second, code access security

One of the most important services provided by the CLR is Code Access security (CAS). The basic principle of CAS is to give code privileges, not to users. This may sound strange if you are accustomed to Windows or SQL Server mode to give permissions to users and logins rather than the code they are executing. However, even if the SQLCLR code executes under the security context of an administrative user, it may not have all the permissions available. In fact, SQLCLR code that executes inside SQL Server almost certainly does not have all the permissions-this is called Full trust.

Here are some basic knowledge about CAS work. The CLR collects evidence when an assembly is loaded in response to a call to a SQLCLR stored procedure, function, or other code module. It uses this evidence to assign the assembly to one or more code groups. Each assigned code group has a permission set that is assigned through a run-time security policy that determines where the code is assigned by using membership conditions. A power corresponding to the operation of protected content. In summary, the code requires that the caller must have some kind of license to perform a specific behavior.

If these are unfamiliar concepts to you, then you need to first have a thorough understanding of these very important parts of developing secure applications. Also, understanding CAS is critical for understanding the permissions that SQLCLR code has in execution.

So how does SQL Server integrate SQL Server and the CLR security environment? The first thing to understand is that these systems protect two collections of resources. The first collection contains SQL Server objects and data. SQL Server's security environment protects its own objects, even the SQLCLR code it hosts.

The CLR protects access to everything else. What does this "everything else" mean? refers to resources outside of an instance of SQL Server, including disk files, registry settings, other databases, network resources, and Web services. This means that the CAS does nothing to protect anything within its host SQL Server instance.

Now, let us pause for further consideration. Let's first figure out what kind of security system protects the critical content. Of course, we can also describe the same thing in another way: the license granted in SQL Server protects all its data and objects from being invoked for any type of execution code, regardless of whether the code is written in T-SQL or SQLCLR. The CAs protection for the CLR is accessible to all resources outside of SQL Server.

One corollary, then, is that CAs did nothing to protect an instance of SQL Server from objects or data.

Now, we will discuss the issue of CAs in more detail. However, keep in mind that the licensing issue we are talking about right now is not the kind that is inside SQL Server, but the outside-the license right in the operating system. For example, SQLCLR code has to open a disk file to record some log data, or to connect to read data from another database. CAS licensing limits how the code can access the disk file and how to connect to other databases.

In order to run a method, whenever the CLR mounts an assembly, it collects evidence about the assembly that matches the policy defined on the machine in order to grant it the appropriate license. Typically, for. NET assemblies typically include location (raw) data (where the assembly runs from) and identity data. However, since a SQLCLR assembly runs from within SQL Server, the location evidence is essentially irrelevant. As a result, there is only evidence of identity, such as whether the Assembly has a strong name or is digitally signed by a particular company.

Figure 3 The intersection of CAS licensing from four policy levels