CLR Stored Procedures - handling security on SQL Server side
Ok this is not new to say you can write stored procedures in any language to manage them in SQL Server, as 2005 version has started this CLR integration functionality that is exposed in an assembly called
system.data.dll, which is part of the .NET Framework. So think about code access security in addition to managing security privileges for the user on database side.
CLR supports the access security mechanism which is based on the
assumption that the runtime can host both fully trusted and partially trusted
code. This means CLR code security is wrapped by a managed API that require the
corresponding permission before allowing access to the resource. BOL documentation refers that ....set of code access security permissions that are granted to managed code
when running inside SQL Server is the intersection of the set of permissions
granted by the above three policy levels. Even if SQL Server grants a set of
permissions to an assembly loaded in SQL Server, the eventual set of permissions
given to user code may be restricted further by the user and machine-level
policies.
As the CLR security mechanism can be managed on granting permissions to assemblies by
the SQL Server host policy level which is determined by the permission set specified
when creating the assembly having 3 levels of permissions SAFE, EXTERNAL_ACCESS & UNSAFE. Reverting back to what SQL documentation states that:
Only internal computation and local data access are allowed.
SAFE is the most restrictive permission set. Code executed by an assembly
with SAFE permissions cannot access external system resources such as
files, the network, environment variables, or the registry.
EXTERNAL_ACCESS assemblies have the same permissions as SAFE assemblies,
with the additional ability to access external system resources such as files,
networks, environmental variables, and the registry.
UNSAFE allows assemblies unrestricted access to resources, both
within and outside SQL Server. Code executing from within an UNSAFE
assembly can also call unmanaged code.
So what you need is another tool to verify the code execution on the security aspects, this is where the .NET Framework tool such as 'PEVerify Tool' (Peverify.exe) comes into picture. This tool helps developers who generate Microsoft
intermediate language (MSIL) to determine whether their MSIL code and
associated metadata meet type safety requirements. You need to be aware about compilers and other script engine developrs that may generate verifiably type-safe code only if you avoid using certain
language constructs. If, as a developer, you are using such a compiler,
you may want to verify that you have not compromised the type safety of
your code. In this situation, you can run the PEVerify tool on your
files to check the MSIL and metadata.
Technet has got further reference on this tool stating that this is a comprehensive tool where the DBAs can rely on security aspects having the privilege of verification checks based
on dataflow analysis plus a list of several hundred rules on valid
metadata. Further information on checks and detailed information can be found from Windows SDK by looking at the "Metadata Validation Specification" and the "MSIL Instruction
Set Specification" sections.
The ITPROs should be aware of CLR integration when you want to develop SQL CLR solutions or to decide whether to use it or not. Moreover it is more important for a DBA to how to lock down security on other side of ranch and ensure the stored procedures development will not compromise the security or using other objects in .NET for SQL Server. So the Developers & DBAs must have following list of concepts in this regard:
- The concepts and architecture of SQL CLR
- Uses of .NET namespaces in SQL Server programming tasks
- How to develop and benchmark routines in T-SQL and .NET to determine when
CLR-based solutions are advantageous
- How to replace extended stored procedures using SQL CLR stored procedures
- How to use SQL CLR objects in external applications
- How to restrict and secure SQL CLR object capabilities
- Processes and procedures for deploying SQL CLR objects
Further references on the subject you can review to Choosing between TSQL and CLR, your choice in addition to Writing
Verifiably Type-Safe Code
and Type Safety
and Security
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.