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

CLR Integration with MS SQL Server

Introduction

My friend told me that the CLR Integration is quite complex. I think that nothing is complex if we understand it properly. So in this article I am trying to discuss about MS SQL server CLR integration. I try to demonstrate it as simple as possible by an example that everyone can understand it properly.

Point in focus

1.    What is CLR

2.    Advantage of CLR

3.    How we can make the CLR Integration

What is CLR

The full form of CLR is Common Language Runtime. The CLR Integration of MS SQL Server starts from MS SQL Server version 2005 and later version.

The database objects such as Stored Procedure (SP), Function, Triggers can be coded in CLR. The main purpose of the CLR is to complete the tasks that are not possible or complex to make in T-SQL and it is faster than the T-SQL in many cases.

Now we look what MSDN tell about CLR.

"The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005 uses CAS to help secure the managed code and prevent compromise of the operating system or database server."

Advantage of CLR

    1.    The CLR Integration layer provides some facility that is not directly available from T-SQL.  

          It offers to access .NET framework libraries.

2. Provide the better result in complex logic for intense string operation or string manipulations, cryptography, accessing system resources and file management, etc.
3. CLR are managed codes so ensures type safety and memory management.
   
   
4. It is quite convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that the .NET Framework supports.
   
   

How we can make the CLR Integration

In this example we are calling .NET CLR code from MS SQL Server. The demonstration example was implemented in MS Visual Studio 2010 and MS SQL Server 2008 and use .NET frame work 3.5.

I am going to demonstrate this simple example in to 2 steps.

1.    Using .NET Frame work for CLR Function creation

2.    Calling the CLR Function from MS SQL Server 2008

Using .NET Frame work for CLR Function creation

Step-1

Open the MS Visual Studio 2010 and then open the File menu. From File menu open the new projects.

Step-2

From New Projects dialog box, in the Installed Templates select

DatabaseàSQL ServeràVisual C# SQL CLR Database projects.

Step-3

From New Database References provide the SQL Server name in Server name and then choose the Authentication mode. Then choose the Database from Select or Enter database name.

  

Step-4

In Add new Item choose the User-Define Function

Step-5

In Function1.cs or class file I am just going to change the function name to fnDispalyName and change the code within the function. As it is a sample example I am not adding any code here just provide "Hello World".

return new SqlString("Hello World");

Sample code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString fnDispalyName()

    {

        // Put your code here

        return new SqlString("Hello World");

    }

};

Step-6

Now Build the solutions. If any error came then rectify it and re-build the solution and then Deploy the solution from Build menu.

Step-7

Open the MS SQL Server management studio and type the connect to the TEST_DB and then provide the bellow SQL Script to test.

SELECT dbo.fnDispalyName()

Output:

Hello World

Hope you like it.

Posted by: MR. JOYDEEP DAS