Afgelopen week is een kopie van onze SQL Server database ‘anoniem gemaakt’.
Dit betekent dat informatie over cliënten niet meer naar die specifieke cliënten
te herleiden is. Ook het willekeurig husselen van de informatie is niet gewenst.
Het feit dat iemand in de database gevonden kan worden, geeft eigenlijk al
informatie prijs over die persoon.
We hebben dus een aantal kolommen in de database gekozen om met random informatie te vullen (oa. achternaam, voornaam, sofinummer). Een ‘random tekst’ generator in SQL was een uitdaging.
Een generator in C# schrijven en toevoegen in SQL Server was ook een uitdaging, maar dat vonden we leuker :-).
Uiteindelijk is dit toevoegen heel eenvoudig, in een mum van tijd kan de
mooiste C# code onder SQL Server geschoven worden. Hieronder staan de 11 stappen om dit te bereiken en eventueel weer op te ruimen:
STAP 1;
Kies een nieuw project in SQL Server:
Kies voor een SQL Server een database via het dialoog:
Er kan nog een extra melding komen over het toestaan van debuggen via deze
SQL Server. Beantwoordt ook deze vraag naar keuze.
STAP 2;
Voeg hier een User-Defined functie aan toe:
Er staat nu een lege methode met de uitdagende tekst: // Put your code here
STAP 3;
Schrijf dus die code:
Deze methode levert een willekeurige tekst met een bepaalde lengte op,
met daarin letters of cijfers. De nul is voor het gemak weggelaten om
voorloopnullen te voorkomen. Let hierbij op de afwijkende types: bv. SqlInt32
ipv. een int.
STAP 4;
Ben je tevreden met de code? Build en Deploy (rechtermuis-klik op project niveau) het project dan:
STAP 5;
Open nu SQL Server en ga naar de betreffende database. Zie dat in de betreffende database de functie toegevoegd is (bij de scalar value function). Deze is nu (vrijwel zeker) nog niet aan te roepen… Type maar eens:
select dbo.RandomStringFunction(40, 0)
Dit geeft:
Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable “clr
enabled” configuration option.
De SQL Server staat nu nog niet toe om CLR (C#) code uit te voeren. Dus we
enablen dit:
sp_configure ‘clr enabled’, 1
go
RECONFIGURE
go
STAP 6;
En zie dat het werkt…De functie geeft een fraai antwoord.
Zie overigens ook dat het inlezen van de DLL eenmalig heel traag is. Het
uitvoeren van de C# functie zelf is snel, HEEEL snel. In dit voorbeeld stel ik
tekst of getallen samen uit random gekozen karakters. De seed is
hierbij automatisch gekozen en afgeleid van de huidige datum/tijd.
We gebruiken de functie voor dus het anonimiseren van data waarbij de functie
steeds opnieuw aangeroepen wordt om een veld in een rij te ‘overschrijven’. Nu
bleek dat tientallen regels toch dezelfde waarde kregen. De seed interval is dus
veel langer dan de aanroep van een enkele functie. Een
Thread.Sleep van welgeteld 1 milliseconde was voldoende om dit
probleem te pareren. Dit is overigens nauwelijks meetbaar in de totale
werking.
Toch is dit niet de meest efficiente code. Bij grote aantallen zal die
duizendste seconde wel gaan tellen. In dat geval kan beter eenmalig een lijst
aangemaakt worden. Dan kan de Random.Next meerdere keren aangeroepen worden.
Voor dit voorbeeld doen we het er maar even mee 🙂
STAP 7;
Mocht het wenselijk zijn dan kan het CLR gebruik later weer uit gezet worden:
sp_configure ‘clr enabled’, 0
go
RECONFIGURE
go
STAP 8;
Vanuit Visual Studio is een deployment niet meer mogelijk… Hoe moet
dit automatisch gedeployed en geundeployed worden zonder
gebruik te maken van Visual Studio? Dit kan ook geheel in SQL indien de DLL
beschikbaar is.
Als eerste stap moet de DLL binnen SQL Server opgenomen worden. Plaats de dll
in c:\:
CREATE ASSEMBLY SqlServerRandomProcedure FROM
‘C:\SqlServerRandomProcedure.dll’
Hiermee is de DLL veilig binnen SQL Server opgeslagen, de DLL kan hierna van
schijf verwijderd worden. Hiermee is het ook niet meer mogelijk om de werking
van SQL Server te veranderen door de DLL te veranderen, hackers hebben het
nakijken.
STAP 9;
Vervolgens moet de methode uit de DLL beschikbaar gesteld worden binnen
de database. Creëer hiervoor de betreffende methode:
CREATE FUNCTION [dbo].[RandomStringFunction](@length [int],
@OnlyAlphaNumeric [bit])
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[SqlServerRandomProcedure]
.[UserDefinedFunctions].[RandomStringFunction]
STAP 10;
Indien SQL Server inmiddels toestaat om CLR code uit te voeren dan kan
nu de functie aangeroepen worden:
select dbo.RandomStringFunction(4, 0)
(Eventueel moet dus nog die sp_configure voor CLS gebruik uitgevoerd
worden).
STAP 11;
Wil je deze nieuwe functionaliteit na gebruik weer verwijderen? Laat dan eerst de functie vervallen:
DROP FUNCTION [dbo].[RandomStringFunction]
Uiteindelijk kan de DLL uit SQL Server verwijderd worden:
DROP ASSEMBLY SqlServerRandomProcedure
Deze laatste twee stappen zijn ook nodig indien de functie met Visual Studio
gedeployed is.
Hiermee is bewezen dat het echt eenvoudig is om een speciale functie in C# te
schrijven welke in SQL Server aangeroepen kan worden. Dit is dus een enorme
verrijking op de standaard T-SQL mogelijkheden.
Sander van de Velde 