Archives for: "January 2014"

SQL Server Row- and Cell-Level Security – Disclosure vulnerability // Schwachstellen in Zeilen-basierter Sicherheit

 

(de)
Es ist Zeit für einen weiteren Artikel zum Thema Sicherheit.
Und durch einen Forum Thread zu „Datengesteuerter Sicherheit“ mittels der IS_MEMBER(), USER_NAME(), SUSER_SNAME() – Funktionen kam ich auf die Idee, ein kurzes Beispiel zu zeigen, wie sich solche Konstrukte leicht umgehen lassen und die geschützten/verborgenen Daten offengelegt werden können, wenn sie nicht mit weiteren Mitteln gesichert werden.
Sehen wir uns ein Beispiel an.

(en)
It’s time for another post on security matters.
And through a forum-thread on data-driven security by the means of views using the IS_MEMBER(), USER_NAME(), SUSER_SNAME() – functions, I came up with the idea of giving a short example how such constructs can easily be circumvented and the protected/hidden data become disclosed, when not being secured by further means.
So let’s look at an example.

Im Folgenden werden wir ein recht verbreitetes Szenario sehen, wie Sicherheit auf Zeilenebene / Row-Level Security (und auch Zellenebene/Cell-Level Security) implementiert werden kann.

Die Architektur ist recht einfach:
Eine Tabelle enthält Datenzeilen, von welchen einige von einer bestimmten Gruppe Personen gelesen werden darf, und andere Zeilen von anderen Personen – jeweils exklusiv.
Um das zu erreichen, wird eine Sicht angelegt. Diese Sicht muss natürlich denselben Besitzer haben, so dass der Prinzipal Berechtigungen auf die Sicht alleine erhalten kann, und durch die Besitzerkette an die Daten gelangt.
Innerhalb der Sicht ist eine Where-Klause, die einen Filter auf ein bestimmtes Attribut in der Tabelle enthält, durch das der Benutzer der aktuellen Sitzung erkannt wird und ausschließlich die Daten zurückgeliefert werden, die seiner Rollen-Mitgliedschaft entsprechen.
Natürlich gibt es auch komplexere Designs mit Zwischentabellen und mehrfachen Rollenmitgliedschaften/Rechten, aber am Ende teilen alle dieselbe Schwachstelle, die ich demonstrieren werde.
Im Folgenden zunächst ein Diagramm der Architektur.

In the following we will see a quite common scenario of how Row-Level Security (and also Cell-Level Security) can be implemented.

The architecture is quite simple:
A table is holding rows of data, some of which are supposed to be readable by a certain group of people, and other rows by other people – in each case exclusively.
In order to achieve this, a view is created. This view naturally must have the same owner, so the principal can be granted permissions on nothing but the view and get to the data by means of the ownership-chain.
Within the view there is a Where-clause which contains a filter on a certain attribute in the table, by which the user of the current session is detected and returned solely the data which matches his role-membership.

Of course there are also more complex designs with intermediate tables and multi-role-memberships/permissions, but it all comes down sharing the same vulnerability which I am about to demonstrate.

First of all here's a diagram of the high-level architecture:

 SQL_Row_Level_Security_Schema

 

Sehen wir uns das ganze also an.
Die Einrichtung Tabelle und der Sicht, inklusive 2er Beispieldatensätze:

So let’s see it in action.
The Setup of the Table and the View including 2 sample data rows:

 SQL_Row_Level_Security_Table_View_Setup

Die Spalte „Role“ wird von der Sicht verwendet, um die jeweilige Zeile, unter Verwendung der IS_MEMBER()-Funktion nur Mitgliedern der jeweils hinterlegten Datenbankrolle durchzureichen.

The column “Role” is used by the view to return the respective row by using the IS_MEMBER()-function only to members of the respectively stored database-role.

 SQL_Row_Level_Security_Table_View

Benutzer, Rollen und Berechtigungen:

User(s), Roles and Permissions:

 SQL_Row_Level_Security_User_Roles_Permissions

Erinnern wir uns, was die Tabelle enthält:

Now, remember what our table contains:

 SQL_Row_Level_Security_Data

In einer heilen Welt, vor dem Sündenfall, wäre dies ausreichend.
(Nachdem wir uns als „Andreas“, der Mitglied der Datenbankrolle RoleAlpha ist, einloggen) würden unsere Abfragen wie folgt aussehen, und lediglich die Zeilen zurückliefern, die der RoleAlpha „gehören“:

So in an innocent world, before the fall of mankind, this would be sufficient.
(After logging in as “Andreas”, who is member of the RoleAlpha database-role) our queries would look like this and only return the rows which “belong” to RoleAlpha:

 SQL_Row_Level_Security_Query

- Natürlich wird die Funktion User_Name() nur für Demo-Zwecke eingesetzt.
Ergebnis:

- Of course the function User_Name() is only used for demo-purposes.
Result:

 SQL_Row_Level_Security_Filtered_Data

Angriff
Aber Andreas spielt nicht fair. Er ist neugierig, was sonst noch in der Tabelle stehen könnte.
Also schreibt er eine Abfrage wie diese:

Attack
But, Andreas does not play nice.  He is curious on what else might be in the table.
So he crafts a query like this:

 SQL_Row_Level_Security_Attack

Und das Ergebnis ist:

And the result is:

 SQL_Row_Level_Security_Disclosure

Nicht „schön“, aber wir haben, was wir wollten: die „geschützten“ Daten.

Der bereits geschulte Leser erkennt diese Form des Angriffs vielleicht aus einem anderen Bereich wieder: SQL Injection.
Es ist eine Form des alten Freundes „Error Based Attack“ oder „Error Disclosure“, die auch bei schlecht geschriebenen Webanwendungen zum Zuge kommt. Das habe ich u.a. 2013 auf diversen Konferenzen gezeigt (Vortragsreihe).
Der Kontext ist ein wenig anders, aber die Idee dahinter ist dieselbe.

Not exactly “pretty”, but we got what we want: the “protected” data.

The well-educated reader may remember this kind of attack from a different area as well: SQL Injection.
It’s a form of the old fried “error based attack” or “error-disclosure”, which can also be used for badly written web-applications. I have also shown that amongst others in 2013 at several conferences (series of sessions).
The context is a little bit different, but the idea is the same.

 Security-Gate-Fail

Einigen kommt das Bild vielleicht schon bekannt vor :-)

Stellt sicher, dass das nicht Euer "Vorgarten" ist!

Wo wir davon reden:

To some, this picture may already look familiar :-)

Make sure it’s not your "front-yard"!

Speaking of which:

 

Schutzmaßnahmen

Was kann man gegen solche Angriffe tun?
Im Wesentlichen stehen einem 3 bekannte Möglichkeiten zur Verfügung:

1) Einsatz von gespeicherten Prozeduren, die alle Fehler abfangen, oder, wenn man unbedingt mit Sichten Arbeiten möchte, der Einsatz einer dazwischengeschalteten Multi-Statement-Tabellenwertfunktion

2) Datenverschlüsselung (Nicht TDE!)

3) Ähnlich wie 1, Aufbau einer Mittelschicht in der Anwendung, die derartiges unterbindet.

Und schlussendlich sollte man für kritische Daten auch über eine Überwachungslösung nachdenken.

Security-measures

What can prevent such forms of attack?
Essentially there are 3 well-known methods at hand:

1) The use of stored procedures which catch all errors, or, if one really wants to use views for some reason, using of a multi-statement table valued function which will be put between.

2) Data encryption (Not TDE!)

3) Similar to 1, implementation of a mid-tier in the application which prohibits such actions.

Finally one should also think about an Auditing solution for critical data.

Die hier gezeigte Technik der Row-Level Disclosure ist nicht wirklich etwas Neues, wird aber gerne immer mal vergessen.
Nachlesen kann man darüber zum Beispiel auch in diesem (alten, aber immer noch zutreffenden) Whitepaper:

The technique of Row-Level Disclosure shown above isn’t really new, but frequently forgotten about.
One can read about this, for example, in this (old, but still applicable) whitepaper:

Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005

 

Happy securing,

Andreas

 

Wer sich ermuntert fühlt, nun einmal richtig in das Thema „Sicherheit mit SQL Server“ einzusteigen, für den habe ich auch 3 erstklassige Trainings im Angebot:

If you now feel encouraged to really dive into the subject of “Security with SQL Server”, I do have 3 first-class Trainings on offer:

Für Beginner, die hier einen guten Überblick erhalten und Grundlegende Kenntnisse erlernen:

For Starters, who gain a good overview and learn essential knowledge in the basics:

(SES) SQL Server Security Essentials for Developers & Administrators (1 day)
3. April 2014 in Düsseldorf

Für Administratoren, die fortgeschrittene Sicherheitskonzepte umsetzen müssen:

For Administrators that have to implement advanced security concepts:

(SIA) Securityworkshop for SQL Server Administrators (advanced) (1 day)
4. April 2014 in Düsseldorf

Für Entwickler, die fortgeschrittene Sicherheitskonzepte umsetzen müssen:

For Developers that have to implement advanced security concepts:

(SID) Securitysworkshop for SQL Server Developers (advanced) (1 day)
24. April 2014 in Düsseldorf

MCSM (Microsoft Certified Solutions Master) Data Platform on SQL Server 2012

 

(de)
Einige meiner engsten Kollegen wissen es bereits, und nun teile ich es öffentlich mit:

Seit 31.12.2013 bin ich nicht mehr „nur“
Microsoft Certified Master SQL Server 2008 (MCM) sondern auch
Microsoft Certified Solutions Master Data Platform (MCSM), der höchsten Zertifizierungsstufe die man für SQL Server 2012 erreichen konnte!

Hier ist der Eintrag auf dem „Master-Blog“:
Introducing our latest MCSM: Data Platform Master - Andreas Wolter

:-)

(en)
A few of my closest colleagues already know, and now I am announcing it publicly:

Since 31 December 2013 I am no longer “merely”
Microsoft Certified Master SQL Server 2008 (MCM) but also
Microsoft Certified Solutions Master Data Platform (MCSM), the highest certification that was achievable for SQL Server 2012!

Here the entry on the „Master-Blog“:
Introducing our latest MCSM: Data Platform Master - Andreas Wolter

:-)


 

MCSM: Data Platform Solutions Master

Der wahrlich krönende Abschluss eines Jahres mit mehr als einem Dutzend Vorträgen auf deutschen und internationalen Konferenzen, die ich aufgrund der Einstellung der „fortgeschrittenen Zertifizierungen“ durch Microsoft schwerlich noch toppen werden kann. Zugleich ist das ein super Einstieg für 2014.

Die Entscheidung, auch diese Zertifizierung noch anzustreben, habe ich erst relativ spät getroffen: auf dem deutschen PASS Camp. Die Zeitplanung dafür konnte ich jedoch erst Mitte Dezember angehen.

Und so lief es ab:

MCSM: Data Platform Solutions Master

This makes for a truly crowning conclusion of a year with more than a dozen presentations at German and international conferences, which I will hardly be able to outdo due to Microsoft’s shutting down of the “advanced certifications”. At the same time, it is a great start into 2014.

I have made the decision to pursue this certification fairly late – at the German PASS Camp. However, I could only tend to the timing in mid-December.

This is how it went:

Am 17.12. habe ich mich für das Knowledge-Examen für den 18.12. angemeldet. Absolviert habe ich es dann innerhalb von knapp zwei Stunden bei einem Zwischenstopp auf dem Weg nach Hamburg zu einem Kunden.

Exam 88-986: MCSM Data Platform Knowledge Exam

Die Ergebnisse erhielt ich prompt.
Daraufhin konnte ich mich am selben Tag noch schnell für die praktische Prüfung, das „Lab Examen“ anmelden.

Am 31.12. war noch ein Slot von 16:00 - 23:30 Uhr frei. Erfreulicherweise konnte dieser dann kurzfristig noch einen Tag vorverlegt werden, so dass ich nicht bis kurz vor Neujahr am Rechner sitzen musste.

Da ich an den Feiertagen dann auch noch zweimal quer durch Deutschland fuhr, war eigentlich ohnehin nur wenig Gelegenheit für intensives Lernen.

Am Freitag vorher habe ich noch meine Umfrage zu Datenbankbesitz & Sicherheit gestartet (an der teilzunehmen ich euch alle herzlich einlade) und hatte dann noch ein Wochenende, mich geistig fit zu machen. Das Wissen, dass dies die letzte Gelegenheit sein würde, da Microsoft diese Prüfung am 31.12.13 letztmalig durchführte, hat ordentlich Druck erzeugt: Es würde keine Wiederholungschance geben. Letztlich war auch keine nötig: ich habe bestanden.

On 17 December, I signed up for the Knowledge Exam to be taken on 18 December. I then passed the exam within just under two hours during a stopover on my way to a customer in Hamburg.

Exam 88-986: MCSM Data Platform Knowledge Exam

I received the results immediately.
Subsequently, I was able to quickly register for the practical exam, the so-called “Lab Exam,” on the same day.

They had a slot available on 31 December, from 4 pm to 11:30 pm. Fortunately, they were able to move it to one day earlier on short notice so that I did not have to end up staying at the PC just shortly before New Year’s.

Since I was also travelling across Germany during the holidays, it actually left only few occasions for intensive study.

That previous Friday, I also launched my survey on Database Ownership & Security (to which I am inviting you all cordially to participate) and thus had one weekend to prep myself mentally. Add to this the extra pressure of knowing that this would be the last opportunity, as Microsoft carried out this exam one last time on 31 December 2013: meaning, there would be no chance to retake the exam. Which, ultimately, was not necessary: I passed the exam.

Exam 88-987: MCSM Data Platform Lab Exam

Zum Lab-Examen selber: Im Gegensatz zu den 5:25 Stunden für das „Exam 88-971: SQL Server 2008 Microsoft Certified Master: Lab Exam waren für das MCSM-Lab 7 Stunden angesetzt. Entsprechend gab es auch mehr Aufgaben. Genauer darf ich darauf nicht eingehen.

Als vermutlich letzter Prüfling darf ich aber sagen: Es hat Spaß gemacht! Und alle Mühen für den MCM und MCSM waren es Wert.

Exam 88-987: MCSM Data Platform Lab Exam

About the Lab Exam: Contrary to the 5:25 hours allotted for the „Exam 88-971: SQL Server 2008 Microsoft Certified Master: Lab Exam, it was 7 hours for the MCSM-Lab. Correspondingly, it consisted of more tasks as well. Evidently, I cannot go into detail.

However, presumably being the last examinee, I may say: It was fun! And all the efforts for the MCM and MCSM were worth it.

Noch ein paar Statistiken zum Ende des „Rennens“:

Weltweit gibt es (Stand 2.1.2014):

Some statistics at the end of the “race”:

Worldwide, there are (as of 2 January 2014):

 

MCM SQL Server 2005

MCA SQL Server 2005

MCM SQL Server 2008

MCA SQL Server 2008

MCSM Data Platform

33

23

154

7

6

 Quelle // Source: Microsoft Certified Masters and Architects: Microsoft SQL Server

- Ich nehme an, dass noch zwei bis drei Master dazukommen, wenn die letzten erfolgreichen Kandidaten veröffentlich werden. Ich werde diese Liste also in ein paar Tagen noch einmal updaten.

- I suppose two or three more Masters will join the ranks once the last successful candidates are made public. So I am going to update this list in a few days.

Dieser Liste nach haben also nur sechs Experten diese Zertifizierung auf dem SQL Server 2012 abgeschlossen. Die meisten „Neuen“ haben sich also weiter auf SQL Server 2008 prüfen lassen.

Davon kommen zwei aus dem europäischen Raum (beide aus Finnland), womit ich im gesamten deutschsprachigen Raum der zugleich erste und letzte mit diesem Abschluss bin.
Interessanterweise haben sich weltweit nur zwei Leute überhaupt sowohl unter SQL Server 2008 als auch SQL Server 2012 erfolgreich prüfen lassen. Vesa Tikkanen (Twitter: @vesat | Blog) aus Finnland ist der geschätzte MCM&MCSM Kollege!

Damit habe ich die Antwort auf meinen Artikel vom September „Wer wird der letzte MCM SQL Server ;-) ?“ wohl selbst gegeben… ;-)

Schade, dass es so wenige versucht und/oder geschafft haben. Denn seien wir ehrlich: Der MCM/MCSM ist keine „Guru-only-Zertifizierung“, sondern er bestätigt praktische Erfahrung. Wenn man über diese auch in der Breite, von Sicherheit über Performance-Tuning, Datenbankdesign bis hin zu Hochverfügbarkeit und Datenwiederherstellung verfügt, dann kann man ihn in einem oder zwei Zügen, um sich an das Prüfungsformat zu gewöhnen, bestehen. – „Konnte“ muss man nun sagen. In jedem Fall meine Hochachtung an alle, die sich den Prüfungen gestellt haben.

According to this list, there are only six experts to have passed this certification on the SQL Server 2012. Most of the “new ones” thus have undergone the exam on SQL Server 2008.
Of the former, two are from the European region (both of whom from Finland), which means I am the first and last to be carrying this title in the entire German-speaking region.
Interesting to note: only two people worldwide have successfully undergone the exam based on both SQL Server 2008 and SQL Server 2012. Vesa Tikkanen (Twitter: @vesat | Blog) from Finland is the much valued MCM&MCSM colleague!

As it turns out, I have provided the answer to my article from September, “Who will be the last MCM SQL Server ;-)?” myself… ;-)

It’s too bad that only so few have tried and/or made it. Because, let’s be honest: The MCM/MCSM isn’t a “guru-only-certification,” but rather it verifies practical experience. If one has it also in its broadness, from security to performance tuning, database design to high availability and data recovery, one can, or rather could have, pass(ed) the certification in one shot, or two, to familiarize oneself with the exam format. In any case, kudos to those who endeavored the exams.

Update (3.1.):
Aufgrund der häufiger gestellten und berechtigten Frage „Warum“, möchte ich darauf hier noch kurz eingehen:

Warum habe ich mir also diese Prüfung noch „angetan“, obwohl die Zertifizierung ab dem 1.1.2014 nicht mehr erhältlich ist?
Man merkt vielleicht schon an der mit Bedacht gewählten Formulierung, dass es ja nicht so ist, dass die Zertifizierung mit einem Male „Nicht mehr existiert“. Es ist lediglich so, dass sie ab jetzt niemand mehr neu machen kann.
Demnach sah ich zumindest darin erstmal keinen Grund, sie nicht abzulegen. Nur der Zeitdruck ist größer – und Druck liegt mir praktischerweise :-)

Sodann gibt es einen ganz rationalen Grund:
Der MCM basiert auf SQL Server 2008, und dieser ist mit dem Erscheinen von SQL Server 2014 nur noch die „Vor-Vor-Version“.
Und auch wenn der MCM ganz allgemein „praktische Fähigkeiten“ belegen soll, ist die Verbindung mit einer bestimmten Version ja nicht ganz abwegig.
Der MCSM basiert auf SQL Server 2012 und dieser enthält von Extended Events über ColumnStore Indexes bis hin zu AlwaysOn einige wesentliche Neuerungen – die im SQL Server 2014 weiter verbessert werden.

Und schon immer lege ich Wert darauf, meinen Kunden einen „zertifizierten“ Beleg bieten zu können, auf den aktuellsten Technologien absolut und umfassend fit zu sein.

Im Prinzip also kein wesentlicher Unterschied dazu, warum ich 2012 den MCM abgelegt habe.

Und zuguterletzt gibt es noch einen persönlichen Grund:
Ich hätte einfach keine Ruhe gehabt, nicht zu wissen, ob ich das MCSM Lab unter SQL Server 2012 aus dem Stegreif bestehen würde und wie diese Prüfung ist :-)
Neugierde hat schon immer vieles bewegt…
Das war am Ende der endgültige Grund, die Lab auch unter hohem Zeitdruck noch am vor/letzten  Tag abzulegen.
Man kann mich natürlich auch einfach „verrückt“ nennen ;-)

 

Update (3 January):
Because of the frequent and legitimate question as to “why,” I will shortly go into the matter:

Why have I even bothered to take this exam when the certification is no longer available as of 1 January 2014?
One may already notice through the careful phrasing that it is in fact not the case that the certification does “no longer exist” all of a sudden. It is simply no longer possible for anyone to take the exam from now on. That is why, for once, I did not see any reason why not to take it. The only difference is that the time pressure was greater – and I’m all for it :-)

Then, there is a plainly rational reason:
The MCM is based on SQL Server 2008, and will be, with the release of SQL Server 2014, only the “pre-pre-version.” And even if the MCM is meant to generally prove “practical skills,” the connection to a specific version is not that beside the point.
By contrast, the MCSM is based on SQL Server 2012, which contains some significant improvements, from Extended Events to ColumnStore Indexes up to AlwaysOn – all of which will be further improved in SQL Server 2014.

Also, I have always been attaching great importance to offering my customers a “certified” proof, and to being absolutely and comprehensively versed in the latest technologies.

Basically, it is hardly any different as to why I have done the MCM in 2012.

And last but not least, there is a personal reason as well:
I just couldn’t have borne not to know whether I would pass the MCSM Lab based on SQL Server 2012 off the cuff, and what this exam is made of :-)
More often than not, curiosity has been the trigger to many things...
Ultimately, this was the final reason for taking the Lab even under the utmost pressure at the last day (turned day before last).
Or one could just call me plain “crazy” ;-)

Für alle, die jetzt knapp gescheitert sind, oder es generell erst für später anvisiert hatten:
Lasst Euch nicht demotivieren durch die Einstellung dieser höchsten technischen Zertifizierung.

Letztlich sollte man vor allem einfach des Wissens wegen Lernen. Ob zertifiziert oder nicht: die Erweiterung der eigenen Kenntnisse und  praktische Erfahrung ist immer eine gute Sache.

Zertifizierungen bieten vor allem Vergleichbarkeit und Standardisierung. Insofern ist der Wegfall vor allem für die Leute „auf der anderen Seite des Verhandlungstisches“ bedauerlich.

In diesem Sinne

To those of you who just narrowly failed the exam or had planned on taking it at a later time:  Don’t be demotivated by the discontinuation of this highest technical certification.

At the end of the day, one should study, above, all, for the sake of knowledge. Whether certified or not: expanding one’s knowledge and skills is always worthwhile.

First and foremost, certifications offer comparability and standardization. Consequently, the discontinuation is regrettable in the first place on behalf of the people “on the other side of the negotiation table.”

In this spirit,

 

Happy studies,

 

Andreas

 

MCM SQL Server, MCSM Data Platform

        

 

Danksagung:

Ich möchte folgenden Personen Dank sagen, die dieses Examen mit möglich gemacht haben:
Cindy McCormack, die mein Lab-Examen und die Vorverlegung noch so spontan eingeplant, Ralph Beatty, der von 5 Uhr morgens an von Redmond aus mein Examen live überwacht hat, Bob Taylor (Twitter: @SQLboBT | Blog) dafür, dass er mein Examen fast direkt im Anschluss benotet und mir damit am 31.12. noch mein Bestehen mitgeteilt hat – sowie für seinen Einsatz für die MCM Community generell, und auch nach Ablauf seines Amtes(!) als MCM-Programm-Manager.
Schließlich danke ich auch Ralf Dietrich, der mich auf dem PASS Camp in dem Gedanken bestärkt hat, die Zertifizierung noch abzulegen..

Acknowledgements:

I would like to express my gratitude to the following people who have made this exam possible: Cindy McCormack, who planned my Lab Exam and its last-minute bringing forward; Ralph Beatty, who proctored my exam from Redmond as early as from 5 am; Bob Taylor (Twitter: @SQLboBT | Blog) for grading my exam almost immediately afterwards and informing me on December 311th that I passed – plus for all the work for the MCM Community in general and even after expiration of his function(!) as MCM-Program Manager.
Finally, I am also giving my thanks to Ralf Dietrich who at the PASS Camp encouraged me with my idea of still doing the certification.