.NET database object security

No discussion of the new CLR features would be complete without a description of the security issues associated...

Sign in for existing members

Continue Reading This Article

Enjoy this article as well as all of our content, including E-Guides, news, tips and more.

Step 2 of 2:

• You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

with using .NET assemblies and the SQL Server CLR. Unlike T-SQL, which doesn't have any native facilities for referencing resources outside the database, .NET assemblies are fully capable of accessing both system and network resources. Therefore, securing them is an important aspect of their development. With SQL Server 2005, Microsoft has integrated the user-based SQL Server security model with the permissions-based CLR security model.

Following the SQL Server security model, users are able to access only database objects—including those created from .NET assemblies—to which they have user rights. The CLR security model extends this by providing control over the types of system resources that can be accessed by .NET code running on the server. CLR security permissions are specified at the time the assembly is created by using the WITH PERMISSION_SET clause of the CREATE ASSEMBLY statement. Table 3-4 summarizes the options for CLR database security permissions that can be applied to SQL Server database objects.

Table 3-4: CLR Database Object Security Options

Using the SAFE permission restricts all external access. The EXTERNAL_ACCESS permission enables some external access of resources using managed APIs. SQL Server impersonates the caller in order to access external resources. You must have the new EXTERNAL_ACCESS permission in order to create objects with this permission set. The UNSAFE permission is basically an anything-goes type of permission. All system resources can be accessed, and calls to both managed and unmanaged code are allowed. Only system administrators can create objects with UNSAFE permissions.

In addition to using the CREATE ASSEMBLY statement, you can also set the CLR database object permission using the project properties as is shown in Figure 3-16.

Figure 3-16: Setting the CLR permission

To interactively set the CLR permission level, open the project properties by selecting the Project | Properties option from the Visual Studio 2005 menu. Then open the Database tab and click the Permission Level drop-down. The project must be redeployed before the changes will take place.

Managing CLR database objects

As shown in Table 3-5, SQL Server 2005 provides system views that enable you to see the different CLR objects that are being used in the database.

Table 3-5: System Views to Manage CLR Database Objects

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.

Basic Transact-SQL Programming Constructs

 Home: Introduction
 Tip 1: CLR integration
 Tip 2: CLR and SQL Server 2005
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security