Permission set of CLR

Introduction

In my previous article named "CLR Integration with MS SQL Server" lot of my reader asking for permission set. But I told them that in my first article related to CLR are an introductory article to grow interest of new developer. In this article I am trying discuss about permission set of CLR in SQL Server. Hope it will be informative and enjoyable.

Assembly are basically managed DLL that contains meta data information With the support of manage code inside the MS SQL server database Microsoft has developed special security settings to protects CLR.

Please note that as because the assemblies are stored in the database they also get backed up and restored with the database.

CREATE ASSEMBLY DDL statement  

 The CREATE ASSEMBLY statement registers a .NET assembly on the DB Server. After registration, the methods contained in the assembly can be referenced by user-defined CLR procedures and functions. CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

As per MSDN

"Creates a managed application module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database."

http://msdn.microsoft.com/en-us/library/ms189524.aspx

The syntax are mentioned bellow.

CREATE ASSEMBLY assembly_name

[ AUTHORIZATION owner_name ]

FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }

[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

[ ; ]

<client_assembly_specifier> :: =

        '[\\computer_name\]share_name\[path\]manifest_file_name'

  | '[local_path\]manifest_file_name'

<assembly_bits> :: =

{ varbinary_literal | varbinary_expression }

assembly_name

It is the name of the assembly. The name must be unique within the database and a valid identifier.

AUTHORIZATION owner_name

Specifies the name of a user or role as owner of the assembly. owner_name must either be the name of a role of which the current user is a member, or the current user must have IMPERSONATE permission on owner_name. If not specified, ownership is given to the current user.

<client_assembly_specifier>

Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest file name that corresponds to the assembly. <client_assembly_specifier> can be expressed as a fixed string or an expression evaluating to a fixed string, with variables. CREATE ASSEMBLY does not support loading multimodule assemblies. SQL Server also looks for any dependent assemblies of this assembly in the same location and also uploads them with the same owner as the root level assembly. If these dependent assemblies are not found and they are not already loaded in the current database, CREATE ASSEMBLY fails. If the dependent assemblies are already loaded in the current database, the owner of those assemblies must be the same as the owner of the newly created assembly.

<assembly_bits>

Is the list of binary values that make up the assembly and its dependent assemblies. The first value in the list is considered the root-level assembly. The values corresponding to the dependent assemblies can be supplied in any order. Any values that do not correspond to dependencies of the root assembly are ignored.

varbinary_literal

Is a varbinary literal.

varbinary_expression

Is an expression of type varbinary.

PERMISSION_SET

There is three of permission associated with CREATE ASSEMBLY DDL statement

§  SAFE
this is the default permission set, but it's highly restrictive. With the SAFE setting, we can access only data from a local database to perform computational logic on that data.

§  EXTERNAL_ACCESS
this is the next step in the permissions hierarchy. This setting lets us access external resources such as the file system, Windows Event Viewer, and Web services. This type of resource access isn't possible in SQL Server 2000 and earlier. This permission set also restricts operations such as pointer access that affect the robustness of your assembly.

§  UNSAFE
this permission set assumes full trust of the assembly and thus imposes no "Code Access Security" limitations. This setting is comparable to the way extended stored procedures function—we assume all the code is safe. However, this setting does restrict the creation of unsafe assemblies to users who have sysadmin permissions. Microsoft recommends that you avoid creating unsafe assemblies as much as possible.

Example

--create an assembly

CREATE ASSEMBLY myAssembly

AUTHORIZATION dbo

FROM 'C:\projects\mySolution\UserDefinedFunctions.dll'

WITH PERMISSION_SET = SAFE

GO

SELECT dbo.fnDispalyName()

References

http://msdn.microsoft.com/en-us/library/ms189524.aspx

Related tropic

http://sqlknowledgebank.blogspot.in/2012/10/clr-integration-with-ms-sql-server.html

Hope you like it.

Posted by: MR. JOYDEEP DAS