By now, you who have created .NET
assemblies for execution in SQL Server 2005, know about the three
permission sets used when cataloguing an assembly:
- SAFE
- EXTERNAL_ACCESS
- UNSAFE
So, for example, if you need to access things outside of the
database you assign the permission set EXTERNAL_ACCESS to the assembly.
But what about when you do not want to allow an assembly to everything
that is possible for that particular permission set? You do not want
the assembly for example to be able to do File IO, but it should be
allowed to do external data access.
For those scenarios you can use CAS policies, exactly as you’d do
with a “normal” .NET assembly. What, – I can hear you ask - can you
actually edit permissions in the SQLCLR permission sets? No you can not
(AFAIK), but you can create your own CAS permission set and apply that
to the assembly.
In the .NET 2.0 Configuration Wizard, create a new permission set
under the, for example, Machine level, and give that permission set the
permissions you want to allow. One necessary permission is the
Security permission; without it your assembly will not load. Having
created the permission set, you create a new code group, choose the
condition type (a Strong Name condition is good). Finally you assign
the permission set to the code group. Re-start SQL Server, execute your
code and see how your permission set works inside SQL Server. Oh, one
last thing you need to do is at the properties dialog for the code
group under the General tab, check the first check-box in the “If the
membership condition is met:“ group.
If you change your permission set you need to re-start SQL Server
for them to take effect as the permissions are parsed during start-up.
I wish I could say it was due to my brilliance (hah) that I found this out, but this was a thing that came up in class last week (guys – you were brilliant, I had so much fun). We spoke about the three levels of permissions in SQLCLR, and Bob
– who taught the module in question – said something about being able
to assign CLR permissions to SQLCLR assemblies. Neither me nor the
students, really believed him (and he wasn’t sure himself) so we
decided to try it out. Most of the hard work was done by one of the
students, unfortunately I’ve forgotten his name but if you read this,
please leave a comment – praise to who praise is due.