Редагувати

Поділитися через

Designing assemblies

  • Стаття

Applies to: SQL Server

This article describes the following factors you should consider when you design assemblies:

  • Packaging assemblies
  • Managing assembly security
  • Restrictions on assemblies

Package assemblies

An assembly can contain functionality for more than one SQL Server routine or type in its classes and methods. Most of the time, it makes sense to package the functionality of routines that perform related functions within the same assembly, especially if these routines share classes whose methods call one another. For example, classes that perform data entry management tasks for common language runtime (CLR) triggers and CLR stored procedures can be packaged in the same assembly. This is because the methods for these classes are more likely to call each other than methods of less related tasks.

When you're packaging code into assembly, consider:

  • CLR user-defined types and indexes that depend on CLR user-defined functions can cause persisted data to be in the database that depends on the assembly. Modifying the code of an assembly is frequently more complex when there's persisted data that depends on the assembly in the database. Therefore, it's better to separate code on which persisted data dependencies rely (such as user-defined types and indexes using user-defined functions) from code that doesn't have these persisted data dependencies. For more information, see Implementing assemblies and ALTER ASSEMBLY (Transact-SQL).

  • If a piece of managed code requires higher permission, it's better to separate that code into a separate assembly from code that doesn't require higher permission.

Manage assembly security

You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by specifying one of three permission sets when you create or modify an assembly: SAFE, EXTERNAL_ACCESS, or UNSAFE.

SAFE permission

SAFE is the default permission set and it's the most restrictive. Code run by an assembly with SAFE permissions can't access external system resources such as files, the network, environment variables, or the registry. SAFE code can access data from the local SQL Server databases or perform computations and business logic that don't involve accessing resources outside the local databases.

Most assemblies perform computation and data management tasks without having to access resources outside SQL Server. Therefore, we recommend SAFE as the assembly permission set.

EXTERNAL_ACCESS permission

EXTERNAL_ACCESS allows for assemblies to access certain external system resources such as files, networks, Web services, environmental variables, and the registry. Only SQL Server logins with EXTERNAL ACCESS permissions can create EXTERNAL_ACCESS assemblies.

SAFE and EXTERNAL_ACCESS assemblies can contain only code that is verifiably type-safe. This means that these assemblies can only access classes through well-defined entry points that are valid for the type definition. Therefore, they can't arbitrarily access memory buffers not owned by the code. Additionally, they can't perform operations that might have an adverse effect on the robustness of the SQL Server process.

UNSAFE permission

UNSAFE gives assemblies unrestricted access to resources, both within and outside SQL Server. Code that is running from within an UNSAFE assembly can call unmanaged code.

Also, specifying UNSAFE allows for the code in the assembly to perform operations that are considered type-unsafe by the CLR verifier. These operations can potentially access memory buffers in the SQL Server process space in an uncontrolled manner. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies by experienced developers or administrators. Only members of the sysadmin fixed server role can create UNSAFE assemblies.

Restrictions on assemblies

SQL Server puts certain restrictions on managed code in assemblies to make sure that they can run in a reliable and scalable manner. This means that certain operations that can compromise the robustness of the server aren't permitted in SAFE and EXTERNAL_ACCESS assemblies.

Disallowed custom attributes

Assemblies can't be annotated with the following custom attributes:

System.ContextStaticAttribute
System.MTAThreadAttribute
System.Runtime.CompilerServices.MethodImplAttribute
System.Runtime.CompilerServices.CompilationRelaxationsAttribute
System.Runtime.Remoting.Contexts.ContextAttribute
System.Runtime.Remoting.Contexts.SynchronizationAttribute
System.Runtime.InteropServices.DllImportAttribute
System.Security.Permissions.CodeAccessSecurityAttribute
System.STAThreadAttribute
System.ThreadStaticAttribute

Additionally, SAFE and EXTERNAL_ACCESS assemblies can't be annotated with the following custom attributes:

System.Security.SuppressUnmanagedCodeSecurityAttribute
System.Security.UnverifiableCodeAttribute

Disallowed .NET Framework APIs

Any Microsoft .NET Framework API that is annotated with one of the disallowed HostProtectionAttributes can't be called from SAFE and EXTERNAL_ACCESS assemblies.

eSelfAffectingProcessMgmt
eSelfAffectingThreading
eSynchronization
eSharedState
eExternalProcessMgmt
eExternalThreading
eSecurityInfrastructure
eMayLeakOnAbort
eUI

Supported .NET Framework assemblies

Any assembly that is referenced by your custom assembly must be loaded into SQL Server by using CREATE ASSEMBLY. The following .NET Framework assemblies are already loaded into SQL Server and, therefore, can be referenced by custom assemblies without having to use CREATE ASSEMBLY.

  • CustomMarshalers.dll
  • Microsoft.VisualBasic.dll
  • Microsoft.VisualC.dll
  • mscorlib.dll
  • System.dll
  • System.Configuration.dll
  • System.Core.dll
  • System.Data.dll
  • System.Data.OracleClient.dll
  • System.Data.SqlXml.dll
  • System.Deployment.dll
  • System.Security.dll
  • System.Transactions.dll
  • System.Web.Services.dll
  • system.Xml.dll
  • System.Xml.Linq.dll

Related content