While T-SQL now has far more granular permissions than previously, it’s still relatively crude compared with the control available with .NET. From the point of view of security, the most important aspect of .NET–SQL Server integration is that it allows the database developer to take advantage of Code Access Security (CAS). CAS is the .NET mechanism that allows developers to state explicitly which permissions their code needs to run (for example, the permission to access unmanaged code or the permission to access some part of the file system), and also to grant or deny permission to specific code to perform some action.
Four fundamental actions can be performed with CAS permissions:
Imperative and Declarative CAS
CAS can be implemented in two ways: declaratively, using attributes to demand and assert permissions; and imperatively, by calling methods on individual permission objects (these are defined in the System.Security.Permissions namespace, and derive from the CodeAccess Permission base class). Permissions can also be configured for entire assemblies, groups of assemblies, or even the entire local machine or domain using the CAS policy management application, caspol.exe, or the .NET Configuration Wizard.
CAS permissions are slightly misnamed—in many cases, they’re actually broad groups of permissions that contain individual permissions. For example, the Security permission contains subpermissions to enable assembly execution, to allow calls to unmanaged code, and so on. To request a specific permission using imperative security, you instantiate the corresponding .NET class, passing in any parameters you need to identify precisely what your code needs permission to do, and then call the appropriate Demand(), Assert(), Deny(), or PermitOnly() method on the instance. For example, to demand Read permission on the file C: emp.txt, use this code:FileIOPermission perm = new FileIOPermission(FileIOPermissionAccess.Read,
To do the same using declarative security, place .NET attributes in front of the assembly, class, or method that requires the permission, passing in the appropriate action as a SecurityAction enumeration member:[FileIOPermission(SecurityAction.Demand, Read=@"C: emp.txt")]
Using CAS with SQL Server
To see how this works in practice, let’s look at a simple example. You’ll write a C# DLL called FileReader.dll that reads a list of names of sales representatives in a text file line by line, and returns the contents as a string array. This will be called by a SQL Server assembly (GetSalesAssem.dll), also written in C#, which looks up these names in the AdventureWorks database and extracts the sales figures for the year to date and last year, and returns these as a result set.Here is the code for FileReader.cs:
You use CAS to demand permission to read the file that’s passed in as a parameter to the method that does the file reading. If the caller doesn’t have permission to access the file, this line will throw an exception. You don’t handle this exception in this class, because you want the exception to be available to the caller. You could create a new exception with its InnerException set to the original Exception object, but this involves creating an extra object that you don’t really need.
Compile this C# file on the command line using the following statement:csc /t:library FileReader.cs
Next comes the SQL Server assembly. This reads the names from the file specified in the parameter and uses these to construct a SQL statement that gathers the sales data for each employee. You then execute this statement against the server and return the results as a data reader to SQL Server:using System;
This is where the power of CAS really shows itself, because you grant access to only one specific file (here, C: ames.txt) by calling PermitOnly() on your FileIOPermission object. Any attempt to access any other file through this assembly will fail. Now, in this case, you could have just hardcoded the value into the method call on the FileReader class, but CAS also lets you specify an array of files to which you want to permit access through this method; in which case, hard-coding the filename wouldn’t have been an option.
As well as the FileIOPermission, notice that you also require a SqlClientPermission, which permits you to connect to SQL Server via the context connection; otherwise, calling PermitOnly on the FileIOPermission would prevent you from accessing SQL Server data. You create a new SqlClientPermission object with no permissions to connect to any SQL Server, and then call the Add method on this permission object, allowing connection on the context connection with no exceptions. It’s not possible to call PermitOnly() individually on more than one permission in a method, so you need to create a PermissionSet object, add these two permissions to it, and then call PermitOnly() on the PermissionSet.
To compile this file, run the following command:csc /t:library /r:FileReader.dll /r:"<path to sqlaccess.dll>" GetSalesAssem.cs
where <path to sqlaccess.dll> is<Program Files>Microsoft SQL Server<MSSQL instance>MSSQLBinnsqlaccess.dll
For example, on our system, this is the following path:C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlaccess.dll
Now you just need to register the assembly in SQL Server and create a procedure that will call this method. The stored procedure has one parameter, of type nvarchar, which will automatically beconverted by the CLR to the corresponding .NET type (System.String), which is the type of the parameter to your .NET method:USE AdventureWorks
To test this, create a new text file called names.txt in the root of the C: folder that contains the following names:Michael G Blythe
and call the new procedure:EXEC uspGetSalesForNames 'C: ames.txt'
You should see the employee ID and sales data for each of these employees. You can check that CAS is working by making a copy of this file called names2.txt and running the procedure against that. You’ll see an error message and stack trace indicating that the attempt to demand the FileIOPermission failed.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.