[SQL(Server)] database beveiligen tegen vrije user queries

[o_f_course]

Even een complex probleem kort samenvatten is een probleem op zich...

Ik wil in m'n programma de mogelijkheid bieden om queries uit te laten voeren door via m'n programma tekst te laten invoeren.

Over deze queries moet enige statistiek worden opgevangen (aantal rows bepalen etc).

Uiteraard wil je niet dat ze 'drop table ... ' of 'delete from ...' gaan uithalen. Die lijst is uiteraard wel aftelbaar, maarja, de kans dat ik hierin een foutje ongelijk aan 0.

Dus: Enige hints hoe ik dit kan aanpakken?

Ik zat zelf te denken om de query als tekst aan een Stored Procedure door te geven en in die stored procedure dan zoiets te laten bevatten als

SQL:

...
BEGIN TRANSACTION MijnStartPunt
...
exec(@Query2Perform)
-- statistiek bepalen
...
ROLLBACK TRANSACTION MijnStartPunt
-- statistiek opslaan
...

Het hoeft overigens niet hacker proof te zijn, en daarmee bedoel ik dat ik er vanuit mag gaan dat de gebruiker verder geen toegang tot de database heeft. Hij/zij kan dus niet zelf rare stored procedures hebben aangemaakt.

Dus:
- Is deze TRANSACTION methode een goede manier?
- Valkuilen?
- Alternatieven? Alleen als TRANSACTION niet voldoende is...

[BCC]

?! Je kan twee dingen doen:

1: de standaard user rechten en authorisaties binnen je database gebruiken om zaken als deletes of selects af te schermen op bepaalde tabellen.

2: De beveiliging regelen op applicatie niveau. Hier moet je dus denken aan rechten en rollen systemen, parameterized queries, etc.

Beide zijn prima, maar wat je nu probeert is een combinatie van 1 en 2, waardoor je dit op de verkeerde plek krijgt of een soort database in een database anti-pattern bewerkstelligt.

[ValHallASW]

Op de toolserver die in het wikimedia-netwerk hangt zijn alle databases van alle wikimedia-wiki's beschikbaar. Daar speelt dus een vergelijkbaar probleem: er mag niet geschreven worden en bij het uitlezen mogen niet alle kolommen uitgelezen worden (bv. passwords en mailadressen). Dat is gedaan door simpelweg views aan te maken; deze views zijn voor gewone gebruikers alleen uit te lezen, waardoor het wél mogelijk is om bepaalde data op te vragen maar niet om deze te veranderen.

[o_f_course]

de standaard user rechten en authorisaties

Helaas buiten mijn macht. Zou wel de ideale situatie zijn, mee eens.

De beveiliging regelen op applicatie niveau. Hier moet je dus denken aan rechten en rollen systemen, parameterized queries, etc.

Je kan uberhaubt hier alleen een query invullen als je tot een bepaald select gezelschap hoort. Maar klanten zijn soms erg wantrouwend, ook tegen zichzelf: ze willen graag dat we intern ook wat aan veiligheid doen.
Parameterized queries zou een optie kunnen zijn, ware het niet dat je wat aan veiligheid verliest. Moet ik over nadenken.

Dat is gedaan door simpelweg views aan te maken; deze views zijn voor gewone gebruikers alleen uit te lezen, waardoor het wél mogelijk is om bepaalde data op te vragen maar niet om deze te veranderen.

Dus: je kunt alleen een view naam opgeven? en daar selects etc over doen? gaat dat via een eigen query generator van wiki oid?

Ik begreep net dat het commando Truncate table waarschijnlijk niet door een ROLLBACK Transaction kan worden teruggedraaid.
Suggestie was dat een simpele aanpassing tot extra veiligheid zou kunnen leiden.

SQL:

set @Query2Perform = "SELECT * FROM (" + @Query2Perform + ") AS MyTest"
EXEC @Query2Perform

Security by obscurity...

[Voutloos]

Vraag #1 is gewoon hoeveel vrijheid er echt nodig is. Als dat mee valt, maak je een API en heb je al dit geneuzel verder niet. Bovendien heb je dan een iets beter idee welke queries gedraaid gaan worden en welke indices bijvoorbeeld nodig gaan zijn.

Met de totale vrij input ga je ook goedbedoelde queries krijgen welke de server op de knietjes krijgen qua performance. Al was het maar per ongeluk door een carthesisch product.

[BCC]

Wat je nu aan het bouwen bent is een parameterized query engine in een stored procedure.

Deze is daar gewoon niet voor bedoeld en ik vraag me af of de performance van je database niet gaat instoren, omdat dit er best wel eens ervoor kan gaan zorgen dat je query cache niet werkt.

Kolommen weggooien en toevoegen worden meestal ook niet ondersteund door transacties (ligt aan de gebruikte db).

[ Voor 17% gewijzigd door BCC op 24-08-2009 12:16 ]

[o_f_course]

Vraag #1 is gewoon hoeveel vrijheid er echt nodig is. Als dat mee valt, maak je een API en heb je al dit geneuzel verder niet. Bovendien heb je dan een iets beter idee welke queries gedraaid gaan worden en welke indices bijvoorbeeld nodig gaan zijn.

Tja...

Met de totale vrij input ga je ook goedbedoelde queries krijgen welke de server op de knietjes krijgen qua performance. Al was het maar per ongeluk door een carthesisch product.

Daar hebben we idd ruime ervaring mee. Maar dan door onze eigen mensen Is idd gevaarlijk op een hosting server waarbij SQLServer databases van meerdere klanten behandeld... gaf leuke effecten... Maar we hebben ook klanten die gewoon zien dat er weer een mogelijkheid bijgekomen is en gewoon dan een aaptest doen.

Wat je nu aan het bouwen bent is een parameterized query engine in een stored procedure.

Tja, en dat wilde ik eigenlijk voorkomen... Maar goed, dan maar toch kijken hoe ik de input van te voren al kan inperken, bv door alleen storedprocedures oid ter beschikking te stellen.

Bedankt allemaal voor de bespiegelingen. Ik ben van genoeg kanten afgestraft om het snel en simpel te willen

[BCC]

graag gedaan

[remco_k]

Toch nog even my 2 cents;

Ik heb ook ergens in een applicatie de mogelijkheid gemaakt voor een user om een query uit te voeren.
Aan 2 kanten beveiligd;
1. Interne applicatie check op geldige query
De query moet met "SELECT ... FROM" beginnen, er mag geen DROP, TRUNCATE, DELETE, CREATE,... (noem maar op) voorkomen in de gehele query text. Voldoet de text niet; nettere versie van deze melding naar de user: "Donder op!"
2. Gebruikersrechten op de server
De applicatie logt intern zelf in op de database bij het opstarten voor al het andere werk, voor het uitvoeren van dit script wordt er met een aparte "read-only" user ingelogd, enkel om dit script uit te voeren. Deze "read-only" user heeft ook nog eens alleen maar toegang tot enkele vooraf bepaalde tabellen, zeker niet allemaal. (De tabel "users" of "settings" b.v. geen access voor deze gebruiker).
Het is ook makkelijk mogelijk om, indien nodig, de mogelijkheid voor het handmatig afschieten van (onhandige) queries uit te schakelen door de betreffende "read-only" user uit te schakelen. Want face it, dat valt niet altijd te voorkomen. Ik geloof zelfs dat er DB servers zijn waar je per user kunt opgeven op hoeveel core's zijn queries mogen draaien. Als je dan b.v. 8 cores hebt, en je zet voor deze read-only user het aantal cores op 1, dan ben je wat betreft performance ook redelijk safe.

Nou is het 2e punt ronduit de belangrijkste in dit scenario. Maar beide zijn van belang, want een foutje in 1 van de 2 punten is zo gemaakt, vooral als er (vrijwel constant) doorontwikkeld wordt op zowel de database als op applicatie nivo door verschillende ontwikkelaars.

Helaas kan jij punt 2 niet uitvoeren, dus zal je er zelf wat van moeten maken en mijn punt 1 (ook) uit moeten voeren, naast wat je er bij bedenkt...
Ik ben namelijk van mening dat je in deze situatie 2 verschillende en los van elkaar staande controles moet hebben die het met elkaar eens moeten zijn of een gebruiker al dan niet die query mag afscheiten.

Of als de inhoud van de database niet erg van belang is, slechts punt 1 inbouwen...
Dit laatste heb ik niet gezegd, maar indien goed gemaakt net zo veilig.

[cariolive23]

Uiteraard wil je niet dat ze 'drop table ... ' of 'delete from ...' gaan uithalen.

Gewoon deze rechten niet geven en je hebt geen last van dit probleem. Geef alleen die rechten die absoluut noodzakelijk zijn voor de werkzaamheden. Alle andere mogelijke rechten zijn overbodig en dus geef je die niet. Lijkt mij niets bijzonders, zo hoor je iedere database in te richten met zijn rollen. Dit voorkomt ook een hoop ellende in de situatie dat jouw database (of eigenlijk: een brakke applicatie) het slachtoffer wordt van SQL injection. Hackers kunnen niet bij informatie komen waar de gebruikte databasrol geen rechten op heeft. Verwijderen of updaten van deze data gaat evenmin lukken zonder de benodigde rechten.

Een ongelukkige SELECT die alle resources wegvreet, blijft uiteraard nog steeds mogelijk, je kunt de meest dwaze JOIN's maken die de gekste dingen uitspoken met jouw server. Dus als je dat nog ergens zou kunnen beperken, dan zou dat mooi zijn. Ik ken SQL Server nauwelijks, weet niet of dat mogelijk is.

[BCC]

Rare Joins heb je helemaal niet nodig. Een simpele select query met een complexe regexp like is meer dan voldoende. Als mensen database toegang willen wil je dat eigenlijk altijd via een API doen. Mag jij bijvoorbeeld straks ook geen kolommen of tabellen renamen omdat de klant z'n magische queries dan niet meer werken?

[cariolive23]

BCC schreef op maandag 24 augustus 2009 @ 20:51:
Als mensen database toegang willen wil je dat eigenlijk altijd via een API doen.

+1

Mijn idee, zo hoor je een database open te stellen voor gebruikers en hun applicaties. Keep up the good work!

[Gomez12]

o_f_course schreef op maandag 24 augustus 2009 @ 11:56:
[...]
Helaas buiten mijn macht. Zou wel de ideale situatie zijn, mee eens.

Serieuze vraag, waarom ligt dit buiten je macht? Als jij iets moet maken moet je wel de middelen daartoe krijgen...

Persoonlijk zou ik gewoon aan een dba vragen om een extra user ( per klant ) die alleen beperkte select rechten heeft, jouw app logt in als deze user en je hebt geen probleem meer.

Elke andere oplossing is imho gewoon een prutsoplossing omdat je altijd het risico op bugs etc hebt

[Voutloos]

Het een sluit het ander niet uit. Goed ingestelde rechten && een goede API is gewoon de winnende combi.