A special shout-out to David Carrico who got me started on this instead of watching SpaceBalls tonight... It really should go without saying but it is espeically true for SQLCLR Assemblies: Be careful who you grant view defintion of an assembly to -- least you unintentially expose more than you might expect. Consider some seemingly harmless code where you've hard-coded some secret into source code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
public const string _SECRET = "12345";
[SqlFunction]
public static SqlString SomeSecretsArent(SqlString Parm)
{
return new SqlString(Parm.Value.ToUpper());
}
};
Well, Dark Helmet won't have hard time cracking Druida's defenses if you're writing that code! If you grew up on Good-Old-Fashioned C (like me), you might think that _SECRET would simply be either discarded as usused or emited as literal string into the code where it was used. Sorry to tell you otherwise, but no, that's not what .NET does. Don't believe me? A little experiment might help see the light. Compile that code and catalog it as an Assembly into some database. I've done so into a database called Scratch. Then create some login and give it access to your test database. I've called my login and user Jack (my favorite troublemaker, you know). Grant that user VIEW DEFINITION on the Assembly in the appropriate schema, ala:
grant view definition on assembly::SomeSecretsArent to jack
Note that you've not granted any other permissions to Jack, other than connect and login your test database, and the right to see the defintion of the assembly. So exactly what does that mean? Interestingly enough, it means that Jack can execute this query:
select * from sys.assembly_files
At first, that might seem pretty harmless. If you deployed the assembly for yourself, likely all Jack is going to see is the first line of the table below. If you used Visual Studio to deploy, you'll see at least the files shown below:
So, what's the danger here? Afterall, all we're seeing is a bunch of hex bytes. Try this query as Jack:
select cast(content as XML) from sys.assembly_files where name like '%.cs'
Oh... well, that won't do. So okay, you might think, that makes sense and you just won't use Visual Studio to deploy so the .CS file won't be there. And you'll be safe, right? Um... No, but seeing why isn't quite as easy. Go back to Visual Studio and write a little console application like this.
using System;
using System.IO;
using System.Data.SqlClient;
namespace Downloader
{
class Program
{
static void Main(string[] args)
{
using(SqlConnection conn = new SqlConnection("[omited]"))
using (SqlCommand cmd = new SqlCommand("select name,len(content),content from sys.assembly_files where file_id=1 group by assembly_id,name,content",conn))
{
conn.Open();
string name;
long size;
using (SqlDataReader r = cmd.ExecuteReader())
{
while(r.Read())
{
name = r.GetSqlString(0).Value;
size = r.GetSqlInt64(1).Value;
Console.Write("Downloading {0} {1}...", name, size);
using(MemoryStream ms = new MemoryStream((int)size))
{
byte[] buffer = new byte[size];
r.GetBytes(2, 0, buffer, 0, (int)size);
using(FileStream fs = new FileStream(@"c:\"+name + @".dll",FileMode.Create))
{
fs.Write(buffer,0,(int)size);
fs.Flush();
}
}
Console.WriteLine(" done");
}
}
Console.ReadLine();
}
}
}
}
Then go out and get yourself a copy of Lutz Roeder's Reflector and crack open the DLL files our last little bit of code downloaded. Specifically, drill into the class, derived types.
Hopefully my point is clear. Jack can now hijack your code and see your secrets. His Schwartz might be bigger than yours afterall. Mind you, the only permission Jack has in scratch is other than connect and login is VIEW DEFINITION on the assembly file.
"So, Lone Star, now you see that evil will always triumph because good is dumb." -- Dark Helmet