Profiling SQL Server2005 SQLCLR code security

Source: Internet
Author: User
Tags cas execution implement microsoft sql server sql net access database access

The feed runs within SQL Server 2005. NET Framework code is an exciting thing or a threat? This series of articles will explore the security aspects of such SQLCLR code in a comprehensive way that developers and DBAs can learn from.

First, the introduction

One of the main advantages of writing. NET code that runs in the CLR under any environment in the host is Code access security (CAS).

CAS provides a code based rather than a user based authentication model to prevent intrusion of various code. But how does this security model coexist with the new, enhanced security features of SQL Server 2005 itself? By default, your. NET code is relatively secure, but these two security patterns are easy to conflict with and can easily cause problems. In this article, we will briefly analyze the concepts behind CAs and some of the security features introduced in SQL Server 2005, and then, in the next few, analyze how to implement the advanced programmable features provided by SQL Server while working together on both systems.

The good news is that in order to implement the security system provided by SQL Server and the common language runtime library, Microsoft has provided some tools to implement code control. However, there are many interesting problems!

The ability to write stored procedures and other code modules in C#,VB or any other. NET language has long been expected, and this is one of the most exciting features of SQL Server 2005. Ultimately, developers and DBAs can break the shackles of Transact-SQL (T-SQL) and C + + that exist in extended stored procedures and write database code in a truly highly productive language!

Also, run in the memory space of the database server. NET code scares some people, especially the DBAs who are responsible for protecting data integrity and ensuring that the servers run day and night. Run some developer code (with full access.) NET Framework and the Win32 API) has led many DBAs to insist that the maintenance of such code running on the server is beyond their capabilities.

By giving lectures at conferences and doing a lot of training, and I ask my classmates and clients, "Is it on the server?" NET code terrified them and why. " Finally, there are some of the following typical issues of concern:

• Vague security issues. Most of these are related to the current attack problem, but it is clear that more attention is being paid to what new content does not understand.

• Need to learn a new skill to assess whether the code is safe.

• There is a lot of ambiguity between data and code, especially for use. NET code to create a user-defined type this new ability.

• There is another way to "mix" code with the server, although OLE Automation (sp_os*) and command shell system (xp_cmdshell) stored procedures are always available to allow people to run external code.

In fact, in SQL Server 2005. NET Framework code, often referred to as SQLCLR code, because it is based on. NET Common Language runtime library (CLR). In fact, it's just another code module that exists and runs inside SQL Server. It's new, it's cool, but it's still just code, but it's not a plug-in substitute for T-SQL (which is still the preferred way to implement the data access code); Instead, SQLCLR code opens up new possibilities for complex database applications. Sooner or later, most DBAs would use it and would have to make a final decision-whether to keep it in the database.

In this article, I'll explore one of the most concerned questions about SQLCLR code: How secure Is it? In fact, I will deliberately blur two important concepts-security and reliability. Security means keeping data secure, while reliability means keeping SQL Server secure; Reliability is often confused with security. So while I'm mainly talking about security, I have to deal with some reliability issues.

I will assume that you are familiar with writing in SQL Server 2005. NET code, the advantages and the basic knowledge. To summarize, include the following:

• Assembly, as a packaging, publishing, and Versioning unit

·. NET code Access Security Basics

· New security features for SQL Server 2005

In other words, this article is not an introductory article about SQLCLR code.

Ii. Security host SQLCLR code

With the introduction of SQLCLR Code, SQL Server 2005 now supports two completely different run-time environments: good old, reliable T-SQL, and new developing SQLCLR. In the past few years, T-SQL has evolved with the escalating version of SQL Server and is tightly integrated with data and objects stored in a database, as well as with security systems in SQL Server. By contrast, the SQLCLR code internally uses a completely different security system provided by the CLR, which is a "warm", secure environment. In this environment, code is not run based on the security credentials of the user who is running it, but on the security credentials of the code itself. At the same time, the SQLCLR code must be implemented within the security context of the database and the server, but the two security systems are fundamentally different. Today, Microsoft's SQL Sever development team has developed a way to make both co-exist and work together.

Being able to host the CLR reliably and securely in another application is a new feature of. NET Framework 2.0. This hosting environment, and its SQL Server implementation, is the "secret" of the peaceful coexistence of the two security systems, because the host (this is SQL Server) has a large degree of control over the code that is being run. This means that from a security standpoint, managed SQLCLR code is not allowed to access database objects that are not authorized to it. The code must be running in the SQL Server security context of the user's session, and it needs to be activated using the same permissions as the T-SQL code.

Note that the bottom line is that in a database, SQLCLR code cannot do much more than the middle-priced T-SQL code module in the same security context.

When designing how to host the CLR, Microsoft has three main design goals:

· The CLR and the code running in it do not compromise the security and stability of SQL Server.

· SQLCLR code must follow SQL Server authentication and authorization rules. This means, to some extent, that it runs in the security context of the user's session.

• System administrators must be able to control access to operating system resources. This means that there must be a secure way to access machine resources from the SQL Server process.

One of the most obvious manifestations of these goals is that, by default, CLR integration is turned off. If you want to run in a database. NET code, then a system administrator must turn it on. The T-SQL code that opens it requires the use of sp_configure:

sp_configure ' clr enabled ', 1
Go
Reconfigure
Go

Of course, you can also use the new Surface Area Configuration tool installed with SQL Server 20,051 to do this, as shown in Figure 1. From the Windows Start menu, select Microsoft SQL Server 2005→configuration tools→sql Server Surface area Configuration, and then select Surface Area Configuration for Features, then select CLR Integration from the list.

Figure 1. Surface Area Configuration Tool-this figure shows how to use the Surface Area Configuration tool to start SQLCLR.

Therefore, it is very important to correctly understand the meaning of switch CLR integration features; However, it only affects whether the SQLCLR code is allowed to run in stored procedures, triggers, user-defined types, and user-defined functions. If it is disabled, then the SQLCLR code is not executed in the server instance, and if it is started, any CLR code can be executed (assuming, of course, that the user has the correct execution permissions). If it is disabled, it will not prevent you from installing the SQLCLR assembly into the database. You can install all the assemblies you want to use (assuming, of course, that you have property permissions to do so), but they will not run in any environment until you support CLR integration.

When SQLCLR code executes, it is in a strict security environment-a layer that protects both operating system resources and the data and objects in SQL Server.

Figure 2. Security Layer: The SQLCLR code is not running in a security vacuum.

Figure 2 shows these layers of security. The operating system is forced to use its own control-using familiar user and group patterns to agree to access resources with access control lists (ACLs). Every application running in Windows needs to run in the context of a login security-it has the right permissions for resource access. Even SQL Server must operate within this framework.


SQL Server controls the security of its own environment by using its own login mechanism or by mapping to the operating system's login mechanism. In its environment, it grants or disables access to data and objects based on permissions granted by the object's owner or administrator. T-SQL also operates in this permission mode. The SQLCLR code executes in the same security environment as T-SQL code and executes in its own security environment provided by the CLR. The CLR implements code access security (CAS) to give its own permission to run code. In the later section, after discussing some other security details about the SQL Server host environment, we'll look at CAS more deeply.

Third, SQL Server-level security

SQL Server 2005 implements a more granular licensing model than earlier versions of SQL Server. This granularity extends into the SQLCLR code-for which the code needs to be installed and run with three primary permissions.

• You need to use the CREATE ASSEMBLY permission to run the CREATE ASSEMBLY statement (this statement installs a SQLCLR assembly into a database).

• In order to run code, a non-system administrator must have execute permission on a code module, and a sysadmin can run any code.

• The owner of the code must have references permissions to refer to other objects, such as using a foreign key and creating a view using pattern bindings (running in the same.) NET assembly does not require this permission.

In addition to these permissions, users who cause SQLCLR code to execute must have the usual select,insert,delete, or update permissions, on the database tables referenced by the code when they log on. There is no way to achieve this goal: based on the operation of code on data in a database table, SQLCLR that are hosted in SQL Server 2005 can circumvent these licensing requirements. Also, this permission check also hooks into the new execution context feature in SQL Server 2005, so that when you define a SQLCLR stored procedure or function, you can use the EXECUTE AS statement to specify the execution context of the code.



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.