SQL Server & BI Blog by Andreas Wolter

(DE)
Einleitung

In diesem Artikel greife ich ein Thema auf, welches ich schon seit vielen Jahren immer wieder in Seminaren, bei Konferenzen und auch in Foren versuche zu vermitteln: Schema-Design. Mit Schema ist hierbei nicht das Datenbankschema mit seinem Tabellen-Design gemeint, sondern die „Datenbank-Objekt-Schemas“, die auch als Namensraum beschrieben werden.

Seit dem Release von SQL Server 2005, nun immerhin über 10 Jahre her, liegt es mir besonders am Herzen, Nutzer darin zu schulen, wie man Schemas richtig verwendet.
Eigentlich ist das auch gar nicht besonders schwierig. So, wie ein Entwickler/Architekt, sich für das ER-Diagramm und spätere Tabellendesign mit den Geschäftsprozessen auseinandersetzen muss, muss man sich für das Schema-Design mit den Datenbank-Zugriffs-Prozessen auseinandersetzen.
Leider jedoch sehe ich auch heute noch jede Woche Datenbanken, die nur das „dbo“ Schema kennen.

(EN)
Introduction

This article picks up a topic I have been teaching time and again in seminars, at conferences and in forums for many years: Schema-Design. Here, schema does not mean the database schema with its tabular design but rather the “database object schemas,” also described as Namespace. 

Since the release of SQL Server 2005, in fact more than 10 years ago, it is of particular importance to me to train users in correctly using schemas. As a matter of fact, it is not really that difficult. In the same way that a developer/architect has to deal with business processes for the ER-diagram and later tabular design, one has to deal with database access processes for the schema design.

Unfortunately, however, I still see databases every week which only seem to know the “dbo” schema.

Ich gebe zu, der Umfang an Informationen zu diesem Thema ist nicht so umfangreich wie zu den Dauerrennern „Indexing“ und „Performance“. Sicherheits-Härtung ist ein eher lästig empfundener Aufwand, und selten sind Entwickler in solchen Dingen geschult, um die wichtigen Entscheidungen gleich zur Design-Phase zu fällen. Mit diesem Blog-Post, der zugegeben lange überfällig ist, hoffe ich eine gute Referenz schaffen zu können. Denn, zur Entlastung aller Autodidakten, ausgerechnet die bekannte „AdventureWorks“-Datenbank ist alles andere als ein Vorbild in Sachen Schema-Design. Diese Datenbank ist prinzipiell geschaffen, um die neuen Features der SQL Server Versionen seit 2005 demonstrieren zu können, aber nicht immer werden die Konzepte dabei nach Best Practices entwickelt. Zumal das Konzept der Schema-User-Trennung damals noch recht neu war.

I admit that the amount of information on this topic is not as extensive as on the regulars “Indexing” and “Performance.” Hardening Security is an effort perceived as rather annoying, and developers are rarely trained in such areas in order to make the important decisions right at the design stage. With this – admittedly long due – blog post I hope to provide a good reference. Because, to relieve all autodidacts, ironically, the well-known “AdventureWorks” database is anything but exemplary when it comes to schema-design. This database is generally supposed to demonstrate the new features of the SQL Server Versions since 2005, but the concepts are not always being developed according to best practices. Particularly as the concept of the Schema-User separation was a relatively new thing back then.

Hintergrund-Informationen

Bis SQL Server 2000 waren User und Schemas voneinander abhängig, und man hatte nur 2 Möglichkeiten:

1)       alle Entwickler legten alle Objekte in das dbo-Schema

2)       Objekte liegen im Schema mit dem Namen des Entwicklers, also z.B.: „Andreas.Warenkorb“

Der 2. Ansatz war natürlich völlig impraktikabel, abgesehen von Ein-Mann-Entwicklungen. Entwickler waren also mit db_owner(!)-Rechten ausgestattet und dazu angehalten, Objekte bei allen DDL-Befehlen immer mit dbo.Objektname, also als „Two-Part-Name“ zu spezifizieren. Wurde das vergessen, stand plötzlich der Name des Entwicklers vor den Objekten und Kreuz-Referenzen funktionierten dann meist nicht. – Immerhin war der Verursacher dann klar :-)

Wer das alles nicht beachtete, hatte das Problem, dass er Konten von ehemaligen Entwicklern nicht vom Server löschen konnte, da ihnen ja noch Objekte zugeordnet waren, die dann am Ende fest in der Applikation verankert waren.

Background Information

Up to SQL Server 2000, users and schemas were independent of each other, and there were only 2 options:

1)       All developers put all objects into the dbo-schema.

2)       Objects are in the schema under the developer’s name, e.g.: “Andreas.Shoppingcart”

The second approach was of course entirely impracticable, putting aside one-man-developments. Developers were thus equipped with db_owner(!)-rights and instructed to always specify objects in all DDL commands with dbo.objectname, hence as “Two-Part Name.” If this was forgotten, the developer’s name would suddenly be in front of the objects, and cross-references would in most cases not work. – At least the causer would be obvious :-)

The person not paying attention to any of it would face the problem that he could not delete accounts of former developers from the server as there were still objects assigned to them and that were in the end firmly anchored in the application.

This is why the Security Team for SQL Server 2005 completely revised the schema concept, with the aim to facilitate the delegation of rights.

The dbo-Schema is essentially a relic from the pre-2005 world, which still exists due to backward compatibility reasons, and which is at the same time used as default schema for name resolution (also like before).

Sinn und Zweck von Datenbank-Schemas

Ich zitiere an dieser Stelle ein Mitglied des Security-Teams: „das Ziel der Trennung von Schemas von Usern war, die Sicherheit zu verbessern – durch die Ermöglichung von Delegierung etc.”

Oder, um das passende Whitepaper „SQL Server Best Practices – Implementation of Database Object Schemas” zu zitieren:

"Ein Schema ist ein eindeutiger Namensraum um die Trennung, Verwaltung und den Besitz von Datenbankobjekten zu erleichtern. Es entfernt die enge Kopplung von Datenbankobjekten und Eigentümern um die Sicherheitsverwaltung von Datenbankobjekten zu verbessern. Database-Objekt-Schemas bieten Funktionalitäten um Anwendungsobjekte innerhalb einer Datenbankumgebung zu kontrollieren und helfen sie zu sichern..."

Soweit zu dem hauptsächlichen Zweck.

Natürlich kann man Schemas auch als Ordnungselement verwenden. Ich möchte sogar einladen dazu, das zu tun. Aber bitte erst an zweiter Stelle, wenn die Sicherheitsgrenzen feststehen.

Aims and benefits of database schemas

At this point, let me quote a member of the Security Team: “the intent with separating Schema from Users was to increase security – to allow more controlled delegation, etc.

Or, to quote the relevant whitepaper, “SQL Server Best Practices – Implementation of Database Object Schemas:”

“A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects. Database object schemas offer functionality to control and help secure application objects within a database environment...”

So much for the main purpose. Of course, one may also use schemas as organization element. I will even invite you to do that. But please only in second place, once the security borders are set.

The image below illustrates how several database principals can use objects in a common schema:

Negativ-Beispiel

Sehen wir uns einmal die eingangs angesprochenen Schemas in der AdventureWorks-Datenbank an:

Negative example

Let us now look at the initially mentioned schemas in the AdventureWorks database:

Auf den ersten Blick mag das schön „ordentlich“ aussehen. Wenn man jedoch genauer hinsieht und überlegt, wie man dort nun Berechtigungen vergeben soll, sieht es eher chaotisch aus.

In allen Schemas gibt es Tabellen und entweder auch Sichten oder Prozeduren oder beides.
Wenn man sich jetzt eine Frontend-Applikation dazudenkt, wo soll diese nun Berechtigungen erhalten?

- Dass db_datareader, db_datawriter und eine „selbsterstellte „db_executor“ o.ä. nicht der Maßstab für diesen Artikel sind, ist sicherlich klar. - Das ist aber durchaus ein valider Ansatz für kleinere Datenbanken, mit wenigen Objekten, oder für Datenbanken, deren Objekte alle wirklich gleichermaßen verwendet werden sollen.

At first sight, this might seem “neat and tidy.” However, once you look closer and start pondering how to assign authorizations, it looks rather chaotic.

In all schemas, there are tables and either also views or procedures or both. Now if one imagines a frontend application in addition, where is it supposed to obtain permissions?

- It is certainly clear that db_datareader, db_datawriter and a “self-created” “db_executor” or the like do not serve as a benchmark for this article. – Yet it is by all means a valid approach for smaller databases with few objects, or for databases whose objects are meant to be used truly equally. 

KISS-Prinzip: „Keep it simple, stupid”

Für Sichten gibt es die SELECT- Berechtigung, bei Ad-Hoc-CRUD-Abfragen auch INSERT, UPDATE und DELETE.
Für Prozeduren genügt die EXECUTE Berechtigung.
Berechtigungen können auf Datenbank- Schema- und Objektebene vergeben werden.
Und natürlich ist die Schema-Ebene die Ebene, die sich anbieten würde, wenn man verhindern möchte, dass man Berechtigungen auf alle Objekte ohne Einschränkung vergibt, aber auch, wenn man sich nicht mit Einzel-Objekt-Berechtigungen herumschlagen möchte.

KISS-Principle: “Keep it simple and stupid“

For views, there is the SELECT permission, in ad-hoc-CRUD queries there is also INSERT, UPDATE and DELETE.
The EXECUTE permission suffices for all procedures.
Permissions can be assigned on a database-schema and object level. And of course, the schema level is the level that would present itself if the intention is to grant authorizations to all objects without restriction, but also if one does not want to grapple with single-object permissions.

Here is an example from the official MOC course:

In diesem Beispiel wird das SELECT-Recht auf alle Objekte in dem Schema „Knowledgebase“ vergeben.
Angewandt auf unser AdventureWorks-Schema-Design, würde das bedeuten, dass wir SELECT-Rechte auf alle Schemas vergeben müssen, in denen Sichten oder Tabellen liegen – mit Ausnahme derer, wo neben Tabellen lediglich Prozeduren liegen, die auch wirklich alle benötigten Operationen auf die im selben Schema enthaltenen Tabellen durchführen – was hier auch nicht der Fall ist.

Im Endeffekt wird man SELECT, INSERT, UPDATE, DELETE-Rechte auf alle Schemas vergeben müssen, zuzüglich einiger EXECUTE-Berechtigungen  auf das dbo-, HumanResources-, Production- und Sales-Schema.

Viel gewonnen hat man damit nicht.

Ein Anwender kann damit auch an den Prozeduren vorbei auf den Tabellen arbeiten, wenn er eine Direkt-Verbindung zur Datenbank aufgebaut hat.

In this sample, the SELECT permission is granted to all objects in the schema “KnowledgeBase.”
Applied to our AdventureWorks schema design, this would imply that we have to grant SELECT rights to all schemas in which there are views or tables – with the exception of those where, besides tables, there are merely procedures that actually execute all required operations against the tables contained in the same schema – which is not the case here either.

At the end of the day, one will have to grant SELECT, INSERT, UPDATE, DELETE rights to all schemas, and, in addition, a couple of EXECUTE permissions to the dbo-, HumanResources-, Production- and Sales-schema.

This does not get us very far.

A user can also work on the tables, past the procedures, after establishing a direct connection to the database.

Schema-Design richtig gemacht

Wie sähe es aus, wenn man es aus der Sicherheitsperspektive richtig macht?

Das ist nicht weiter schwer vorzustellen. SQL Server kennt ja so etwas wie “Objekt-Besitzerverkettung” (“Object-Ownership-Chaining”). Schemas haben einen Besitzer und sind Teil der Kette. Das heißt, solange die beteiligten Schemas den selben Besitzer haben, kann man in einem Schema, „Zugriffobjekte“ (Sichten, Prozeduren, Funktionen) halten, und in einem anderen Schema Objekte (Tabellen), auf die man keinen direkten Zugriff erlauben möchte.

Schema-Design done right

What would it look like if one does it correctly from a security-point-of-view?

It is not that difficult to imagine. In fact, SQL Server knows a thing called “Object Ownership Chaining.” Schemas have an owner and are part of the chain. That means, as long as the schemas involved have the same owner, one can keep “access-object” (views, procedures, functions) in one schema, and, in another schema, objects (tables) to which one does not want to allow direct access.

Das Prinzip hatte ich 2009 auf dem PASS Summit in Seattle im Vortrag „Securing SQL Server from Inside-Attacks“ „Schema-Ownership-Chaining“ getauft.

In my presentation “Securing SQl Server from Inside-Attacks” at the 2009 PASS Summit in Seattle, I dubbed this principle “schema-ownership-chaining.”

Schema-Ownership-Chaining

Man sieht hier in der (leicht modifizierten) Slide, dass ein User keinen direkten Zugriff auf die Tabellen in dem Schema „Data“ hat, sondern nur über Sichten in dem Schema „Access“ (=„Zugriff“ – daher „Zugriffsschema“). Das funktioniert, weil die Schemas und die enthaltenen Objekte denselben Besitzer haben. Hier „dbo“.

Schema-Ownership-Chaining

In the (slightly modified) slide above, one can see that a user does not have direct access to the tables in the schema “Data” but only via views in the schema “Access” (hence “Access-Schema”). This works because the schemas and the contained objects have the same owner – “dbo” in this case.

Zur SQLRally Nordic, 2012 in Kopenhagen für den Vortrag „SQL Server 2012 Security for Developers“ hatte ich das Konzept noch etwas verfeinert:

For my presentation “SQL Server 2012 Security for Developers” at the 2012 SQLRally Nordic in Copenhagen, I refined this concept a bit more:

Best Practices für Schema-Design

Aus dieser Grafik geht noch besser hervor, dass in dem Schema „App1“ keine Tabellen liegen, sondern nur Zugriffscode in Form von Prozeduren, ggf. Sichten. Daher genügt ein EXECUTE-Recht auf dieses Schema, und was immer die Prozeduren durchführen (SELECT, INSERT, UPDATE, DELETE), erfordert keine weiteren Rechte – schon gar nicht auf den Tabellen, hier im Schema „Sales“, selber.

Und noch ein zweiter Ansatz wird hier ersichtlich: Das Denken an „Prozesse“ bzw. hier Applikationen.
In vielen Datenbanken muss eine Applikation nicht wirklich auf alle Tabellen zugreifen.
Spätestens sobald mehrere Applikationen mit einer Datenbank arbeiten, wird ersichtlich, dass das „Ordnungs-Konzept“ einem im Wege steht. Idealerweise erschafft man also für jede Applikation ein eigenes Schema, das genau die Prozeduren enthält, die diese verwenden soll. Für Ad-Hoc Zugriffe, die leider oft für Code-generatoren benötigt werden, kann man das auch mit Sichten machen.
Und es hindert einen niemand daran, ein „gemeinsames Schema“ zu erschaffen, in dem Code (Prozeduren und Sichten) liegt, der von beiden verwendet wird.

Best Practices for Schema-Design

The above graphic illustrates even better that there are no tables in the schema “App1” but only access code in the form of procedures, and, where applicable, views. As a result, one EXECUTE permission is enough for this schema, and whatever the procedures are executing (SELECT, INSERT, UPDATE, DELETE) does not require any further rights – especially not on the tables themselves, here in the schema “Sales.”

A second approach becomes evident here: Considering “processes” or, in this case, applications.

In many databases, an application must not really access all tables. At the latest when several applications are working with one database it becomes apparent that the “order-concept” represents an obstacle. Ideally, then, for each application one schema is created that contains exactly those procedures the former is supposed to use. For ad-hoc-accesses, unfortunately often needed for code generators, it is also possible to do this with views.
In fact, no one keeps you from creating a “common schema” in which there is the code (procedures and views) that is used by both.

Hinweis zu Objektbesitzer und durchbrochene Besitzverkettung

Achtung: die Besitzverkettung kann auf allen Ebenen, d.h. Schema, Prozedur, Sicht oder Tabelle durchbrochen werden. Das passiert auch, wenn man den Besitzer einer Tabelle ändert, wie im Folgenden dargestellt.

Note on Object Owner and broken Ownership Chains

Attention: the ownership-chains can be broken on all levels, i.e. schema, procedure, view or table. This also occurs when the owner changes a table, as demonstrated below.

This is how you can prompt the owners of the schemas and tables involved:

Eine Principal_id von NULL bedeutet, dass die Tabelle dem Schema_owner gehört. Das Schema „Person“ gehört dem dbo (principal_id=1)

Ändern des Besitzers der Tabelle:

A Principal_id of NULL means that the table is owned by the Schema_owner. The schema “Person” is owned by the dbo (principal_id=1)

Altering the owner of the table:

An dieser Stelle ist die Besitzverkettung unterbrochen.

Und nicht nur das: auch sämtliche Schema-Berechtigungen werden dadurch zurückgesetzt!

Und so setzt man sie auf den Schema-Besitzer zurück – das ist besser, als den Besitzer explizit auf dbo (oder einen anderen Prinzipal) zu setzen:

At this point, the ownership chain is broken.

Not only that: also, as a result all schema permissions are reset!

And this is how to reset them to the schema owner – which is better than explicitly assigning the owner to dbo (or another principal):

Ergebnis:

Result:

AdventureWorks-Schema korrigierte/sicherheits-optimierte Fassung

Nachdem das Konzept nun klar ist, versuchen wir es mal mit der AdventureWorks-Datenbank. Im Folgenden mein Vorschlag:

AdventureWorks-Schema corrected/security-optimized version

Now that the concept is clear, let’s give the AdventureWorks-database a shot.
My suggestion is as follows:

Die Sichten liegen nicht mehr zusammen mit Tabellen, damit ein SELECT-Recht auch wirklich nur Sichten betrifft. Neu ist das Schema „WebShop“, was beispielhaft für eine Applikation gedacht ist, die eben alle Prozeduren aufrufen und Sichten verwenden darf, die dafür programmiert worden sind.

Das dbo-Schema ist jetzt leer und bestimmte Objekte, Log-Tabellen z.B. liegen im Admin-Schema. Man kann diese auch im dbo-Schema belassen, aber man muss bedenken, dass dieses immer als Default-Schema bei der Namesauflösung verwendet wird.

The views are not together with tables anymore so that a SELECT right is sure to concern views only. What is new is the schema “WebShop,” which is meant exemplary for an application that is permitted to call all procedures and use views programmed for this purpose.

Ausführungskontext und durchbrochene Besitzverkettung

In manchen Szenarien kann eine durchbrochene Besitzverkettung auch gewollt sein. Um dennoch bestimmten Modulen Zugriff auf Daten im Zielschema zu gewähren, ohne das Zielschema selber mit Berechtigungen freizugeben, kann man mit der „EXECUTE AS-Klausel“ arbeiten.
Die Umsetzung kann dann schematisch so aussehen:

Execution-Context and broken ownership chains

In some scenarios, a broken ownership-chain can be intentional. In order to still grant particular modules access to data in the target schema without opening the target schema itself with permissions, one can work with the „EXECUTE AS-Clause.“

Empfehlungen für Schema-Design

Im Folgenden einige Ansätze, wie man bei der Entscheidung für Schema-Design vorgehen kann.

Das Idealmaß wäre eine Struktur nach Prozess oder Anwendung (Unter Sicherheitsfachleuten auch „Rolle“)

Beispiel:

Recommendations for Schema-Design

In the following, I will present a few approaches to the decision-making for schema-design.

The ideal measure would be a structure according to process or application (also known as “role” among security experts).

Example:

Weitere Beispiele für bestimmte Szenarien, wie sie Sarpedon Quality Lab® teilweise so auch seit Jahren in Kundenprojekten implementiert und in Schulungen gezeigt:

Further examples for certain scenarios, as also partly implemented in customer projects and shown in seminars by Sarpedon Quality Lab® for years:

Standard OLTP

Data Processing (Cleansing etc.):

Data Processing (Cleansing etc.):

DataWarehouses (Source for OLAP-Cubes)

DataWarehouses (Source for OLAP-Cubes)

Für lediglich interne Datenbanken kann man auch folgenden Ansatz verwenden:

For merely internal databases it is also possible to use the following approach:

Weitere Tipps:

• Anstelle “dbo” empfehle ich einen expliziten “User without Login“ anzulegen und als dedizierten Schema-Besitzer zu verwenden.
• Die folgenden Schemas sind „backward compatibility schemas“, die man getrost löschen kann. Man kann sie nur löschen, wenn sie nicht verwendet werden, von daher gibt es da kein Risiko – und sie anzufangen zu verwende, davon rate ich natürlich auch ab.:

 Further hints:

• In place of „dbo,” I recommend setting up an explicit “User without Login” and using it as dedicated schema-owner.
• The following schemas are “backward compatibility schemas” which one can safely delete. They can only be deleted if they are no longer used, which is why there is no risk – and of course, I advise against starting to use them.:
  

Hier noch einige Links zu Artikeln zum Weiterlesen:

Here some links for further reading:

Aufruf an Entwickler

An dieser Stelle vielen Dank fürs Lesen. Der erste Schritt ist damit getan: sich mit der Thematik Schema-Design überhaupt auseinanderzusetzen.

Call to developers

First of all, thank you very much for reading. The first step has thus been taken: to generally engage with the issue of Schema-Design.

It is my hope that this also leads to a rethinking and that I am going to see more well-designed schema-uses. And a practical, logical partitioning for the purpose of order can be part of this – ideally combined with an access-schema. But anything is better than saving all programs directly in C:\Program Files\ – there, subfolders are also common practice.

(DE)
Dieses Jahr findet vom 23.-25. Februar die 3. SQLKonferenz in Darmstadt statt.

Und dieses Jahr ist für mich gleich in zweierlei Hinsicht ein besonderer Höhepunkt:

Zum einen ist meine Firma, Sarpedon Quality Lab®, erstmalig Silber-Sponsor der Konferenz. Damit unterstütze ich die PASS Deutschland e.V., der deutschen SQL Server Community, der ich seit vielen Jahren sehr verbunden bin, und in welcher ich auch seit einigen Jahren als einer der 3 Regionalgruppenverantwortlicher der Region Rheinland engagiert bin.

Und das Highlight schlechthin, seit ich 2009 das erste Mal in Seattle auf dem PASS Summit vor großem Publikum Wege zeigte, SQL Server sicherheitstechnisch auszuhebeln, ist:

(EN)
This year, from 23 to 25 February, the 3^rd ”SQLKonferenz” takes place in Darmstadt.

And this year represents a particular peak for me in two regards:

For one, my company, Sarpedon Quality Lab®, is for the first time Silver Sponsor of the conference. In this way, I am supporting PASS Deutschland e.V., the German SQL Server Community, with which I have been closely connected for many years, and in which I have also been involved as one of the 3 regional chapter leaders of the Rheinland Region for a couple of years.

And the highlight per se – ever since I had for the first time demonstrated to a large audience at the PASS Summit in Seattle in 2009 how to break through SQL Server in terms of security – is: 

Joachim Hammer, der Leiter der Security für die SQL Engines bei Microsoft in Redmond (also SQL Server und auch der In-Memory Engine XTP, Azure, APS,…) wird mit mir zusammen die neuen Sicherheitsfeatures des bald erscheinenden SQL Server 2016 vorstellen!

Das ist eine große Ehre für mich. Ich freue mich, dass ich ihn dafür gewinnen konnte, unsere Konferenz in Deutschland zu besuchen und sich in einer gemeinsamen Session dem kritischen deutschen Publikum zu stellen.

Die Vortrags-Agenda:

Joachim Hammer, the Head of Security for the SQL Engines at Microsoft in Redmond (i.e. SQ Server as well as the In-Memory Engine XTP, Azure, APS, …) will be introducing with me the new security features of SQL Server 2016, which will be released soon!

This is a great honor for me. I am happy that I was able to convince him to attend our conference in Germany and to be available to the tough German audience in a joint session.
The sessions agenda:

Joachim Hammer, Leiter des Security-Teams für die relationalen SQL Engines in Redmond, und Andreas Wolter, Microsoft Certified Solutions Master der Data Platform, geben in dieser Session Einblicke in die wichtigsten Sicherheits-Features von SQL Server 2016. Denn dieses Release wird komplett neue Features einführen, die auf verschiedenen Ebenen der Datensicherheit dienen. Die Top 3: Dynamic Data Masking, Row Level Security und das Highlight: Always Encrypted. In dieser Session gibt es technische Einblicke und Demos, aber auch Hinweise auf Sicherheits-Fallstricke sowie die einmalige Möglichkeit, Fragen zu Implementierungsdetails und dem Entwicklungsprozess direkt an den Entwicklungschef zu stellen. Die Session soll Administratoren als auch Entwickler bei der Entscheidungsfindung für die richtige Technologie unterstützen.

Der Vortrag wird in englischer Sprache stattfinden, Fragen können aber problemlos live übersetzt werden. Nutzen Sie diese einmalige Möglichkeit den Program Manager der Security Teams direkt Fragen stellen zu können.

In this session, Joachim Hammer, Program Manager of the Security-Team for the relational SQL Engines in Redmond, and Andreas Wolter, Microsoft Certified Solutions Master for the Data Platform, will give insights into the most important security features of SQL Server 2016. In fact, this release will introduce completely new features that serve data security on different levels. The top 3 features are: Dynamic Data Masking, Row Level Security, and, as a highlight: Always Encrypted. The session will provide technical insights and demos but also hints to security traps as well as the unique opportunity to address questions regarding implementation details and the development process directly to the head of development.
This session aims to assist Administrators as well as Developers in determining the right technologies for their needs.

Zeitpunkt des Vortrages: 24.2. 13:30-14:30

Wer es irgendwie einrichten kann: lasst es Euch nicht entgehen, Fragen direkt an den Program Manager für SQL Security zu stellen!

Zusätzlich wird es am 25.2. noch eine spezielle Panel Discussion zum Thema Security in SQL Server geben, die ich moderieren werde. Auch hier wird Joachim Hammer sicherlich der Star-Gast sein. Die anderen Gäste verrate ich noch nicht, da es teilweise eine Überraschung sein soll. :-)

Time of the presentation: 24 Feb., 1:30-2:30 pm.

For those who can make it work somehow: don’t miss out on the chance to pose questions directly to the Program Manager for SQL Security!

In addition, on 25 Feb. there will be a special Panel Discussion on Security in SQL Server, which I will be chairing. Here, too, Joachim Hammer will certainly be the star guest. I won’t tell who the other guests are, as it’s kind of supposed to be a surprise.  :-)

Das Thema In-Memory wird dieses Jahr übrigens von meinem geschätzten Kollegen Guido Jacobs, ebenfalls Microsoft, übernommen werden. In seinem Vortrag, der sich direkt meinem anschließt, geht es um „Operational Analytics“, das der Begriff für die im SQL Server 2016 mögliche Kombination aus In-Memory OLTP mit ColumnStore Technologie ist. Mein Tipp: es lohnt sich, sich damit nun zu befassen. Die XTP-Engine ist im SQL Server 2016 wirklich wesentlich ausgereifter und für viel mehr Workloads und Datenbankdesigns verwendbar.

Hier geht’s zur kompletten Agenda der SQLKonferenz:

This year, the topic In-Memory will be taken over by my distinguished colleague Guido Jacobs, also from Microsoft. His presentation, which will follow right after mine, is on “Operational Analytics:” the term for the combination of In-Memory OLTP with ColumnStore Technology that is possible in SQL Server 2016. My advice: it is worth dealing with it now. The XTP-Engine is far more sophisticated in SQL Server 2016 and applicable for many more workloads and database designs.   

Here you can find the complete agenda of the SQL Conference:

Ich freue mich auf interessante Gespräche. Und diesmal wird es leichter sein, mich zu finden: wenn ich nicht gerade in einem Vortrag bin, sollte ich am Stand von Sarpedon Quality Lab® zu finden sein :-).

I am looking forward to interesting discussions. This time, I will be easier to find: unless I am sitting in on a presentation, or presenting myself, you should find me at the Sarpedon Quality Lab® booth :-).

(DE)
Ziemlich genau ein Jahr, nachdem die seit 5 Jahren ersten Sicherheits-Bugs in SQL Server gefixt werden mussten, gibt es nun, seit 14.7.2015, wieder einen Grund, seine Security-Patching-Policy zu testen.

- Wenn man denn eine solche für SQL Server hat.

Und damit möchte ich jetzt niemanden anprangern. Denn: die letzten Jahre waren wir aufgrund des Mangels an Sicherheit bedingten Fixen in SQL Server schlicht verwöhnt. Sicherheitslecks (zumindest solche, die bekannt geworden sind) sind seit SQL Server 2000 so gut wie nicht mehr aufgetreten.

- Dazu gibt es diverse Statistiken und Aufsätze, wie diesem von dem amerikanischen Sicherheitsexperten David Litchfield (www.davidlitchfield.com/security.htm) oder dem ITIC-Report von 2010: SQL Server Most Secure Database; Oracle Least Secure Database Since 2002 oder auch die Statistik vom NIST (National Institute of Standards and Technology) von 2013, auf der die folgende Grafik basiert:

(EN)
With 14 July 2015, quite precisely one year after the first security bugs in 5 years had to be fixed, we have been given a new reason to test one’s security patching policy.

- Provided that you have such a policy for SQL Server.

And it is not my intention to point at anybody. Because: we have simply been spoiled due to the lack of security-related fixes in SQL Server. Security bugs (at least those that leaked out) have basically not occurred since SQL Server 2000.

- There is a range of statistics and papers, such as that by the American security expert David Litchfield (www.davidlitchfield.com/security.htm), the ITIC-Report from 2010, SQL Server Most Secure Database; Oracle Least Secure Database Since 2002, and the statistics by the NIST  (National Institute of Standards and Technology) from 2013, on which the following graph is based:

Dadurch dass er auf Basis der NIST-Daten 5 Jahre in Folge als sicherstes Datenbanksystem galt, mag der SQL Server in Sachen Sicherheitspatching durchaus aus dem Fokus der Administratoren gewandert sein.

Der Security Bulletin vom Juli 2015:

Due to the fact that, based on the NIST data, the SQL Server has been the most secure database system 5 years in a row, it may indeed have moved away from the focus of administrators in terms of security patching.

The Security Bulletin from July 2015:

Beschrieben werden 3 Sicherheitslecks:

3 security vulnerabilities are described here:

Welche SQL Server Versionen sind betroffen?

Die Liste, beginnend mit SQL Server 2008 Service Pack 3 und endend mit SQL Server 2014 findet sich im Bulletin.
Genauer kann man anhand seiner jeweiligen SQL Engine Nummer in diesem Blog-Artikel von Microsoft suchen:

Which SQL Server versions are affected?

The list, starting with SQL Server 2008 Service Pack 3 and ending with SQL Server 2014, can be found in the bulletin.

A more detailed search is possible with the respective SQL Engine number in this blog article by Microsoft:

Unter welchen Umständen kann ein System durch diese Lücken angegriffen werden?

Die Voraussetzungen sind für die 3 Sicherheitslecks natürlich unterschiedlich und auch (absichtlich) nicht bis ins letzte Detail beschrieben.
WAS man jedoch herauslesen kann, ist, dass diejenigen, die Rechte nur sehr feingradig und dediziert gesetzt haben, wesentlich weniger angreifbar sind, da teilweise Schemaänderungsrechte im Datenbank-Bereich Voraussetzungen sind.
Daher erinnere ich an dieser Stelle gern daran, dass man weise beraten ist, wirklich nur benötigte Rechte zu vergeben, und Schemas als Sicherheitsbereiche zu verstehen. Die Verwendung von db_owner beispielsweise sollte Tabu für Applikations-Benutzer sein.
Wer solche Regeln bisher beherzigt hat, kann sich auch weiterhin einigermaßen sicher fühlen, da die Möglichkeit, diese Sicherheitslecks auszunutzen somit stark verringert ist.

Deswegen sollte man Best Practices für Sicherheit immer beherzigen.
Eine vulnerability ist eben noch kein exploit.

Under what circumstances can a system be attacked due to these bugs?

The preconditions for the three security bugs differ of course and are (on purpose) not described down to the last detail.

Yet WHAT is possible to read from it is that those who have set up rights only to a fine degree and dedicatedly are significantly less vulnerable since in part schema alteration rights in the database area are a prerequisite.

Therefore, I would like to recall that one is well-advised to really assign only necessary rights and to understand schemas as security areas. The use of db_owner, for example, should be off-limits for application users.

Those who have been following such rules so far may continue to feel fairly secure since the possibility to abuse these security vulnerabilities is thus greatly reduced.

For this reason, one should always take security best practices to heart.

A vulnerability does not yet make an exploit. 

Hier noch ein paar Links in Sachen Security in SQL Server:

Here are a couple of links related to security matters in SQL Server:

(DE)
Seit Anfang Mai ist es offiziell: Satya Nadella hat auf der Microsoft Ignite-Konferenz in Chicago den SQL Server 2016 vorgestellt.
Und ich kann jetzt schon sagen, dass der SQL Server 2016 eines der in meinen Augen spannendsten Releases werden wird. Und zwar weil diesmal Sicherheit ganz klar mit im Fokus steht. Sicherheitsfeatures gehören neben Performance-Features zu den „Key Capabilities“ des kommenden SQL Server, so dass ich den SQL Server 2016 gern ein „Sicherheit & Performance-Release“ nenne.

(EN)
The news broke in early May: That’s when Satya Nadella presented SQL Server 2016 at the Microsoft Ignite-Conference in Chicago.
I can already say that SQL Server 2016 will be one of the most exciting releases in recent years. And that’s because this time it’s clearly focused on security. Next to performance features, security features are among the “Key Capabilities” of the upcoming SQL Server. I like to call SQL Server 2016 a “Security & Performance-Release.”

Die Schlüsselfeatures sind demnach:

Always Encrypted

Mit diesem Feature ist es endlich möglich, eine Datenverschlüsselung anzubieten, bei der SQL Server selber keinen Zugriff auf die Daten im unverschlüsselten Zustand hat – und auch ein Datenbankadministrator Daten nicht entschlüsseln kann. Der Schlüssel wird hierbei in der Applikation bereitgehalten, wo die Verschlüsselung und Entschlüsselung stattfindet. Das war bisher nur mit eigenen Entwicklungen möglich.

Its key features are:

Always Encrypted

With this feature it is finally possible to offer encrypted data where SQL Server itself does not have access to the data in unencrypted form and where the database administrator is incapable of decrypting the data. The key is held within the very application where the encryption and decryption takes place. Up to now encryption/decryption was only possible if users developed solutions on their own.

Stretch Database

Stretched Tables bieten die Möglichkeit, innerhalb einer Tabelle Daten sowohl On-Premise als auch in der Cloud zu halten. Ähnlich wie bei Partitionierung wird hier transparent für die Applikation ein Teil der Daten nach Microsoft Azure ausgelagert, und nur dann geladen, wenn nötig. – Hier sollte man also sicherstellen, mit ungünstigen Abfragen keine Index-Scans in die Cloud zu verursachen.

Stretch Database

Stretched Tables offer the option to store data in a given table both On-Premise and in the Cloud. Similar to partitioning, part of the data will be swapped to Microsoft Azure, transparently to the application, and only accessed when necessary. Here it might be advisable to avoid causing index-scans to the cloud with badly designed queries.

Um die Daten zu schützen funktionieren Stretched Tables auch im Zusammenspiel mit Always Encrypted. Es bleibt abzuwarten, wie dieses Feature die Hemmungen, Daten in die Cloud zu geben, beeinflusst. Microsoft selber hat jedenfalls sicherlich kein Interesse daran, den Zugang zu seinen Systemen für Geheimdienste zu vereinfachen, und Always Encrypted dürfte ein klares Signal sein.

To protect the data, Stretched Tables also work together with Always Encrypted. It remains to be seen, how this feature affects the inhibition to give data to the cloud. Microsoft itself surely has no interest to make access to its systems simple for intelligence, and Always Encrypted should be a clear signal.

Real-time Operational Analytics & In-Memory OLTP

Unter diesem Feature verbirgt sich die Verheiratung der beiden „Performance-Killer“ Features Columnstore Indexe – für OLAP-Szenarien – und Memory Optimized Tabellen für In-Memory OLTP. Das heißt damit sind performante analytische Abfragen auf den frischen OLTP Daten in In-Memory möglich. In meinen Augen ist dieses Feature der Killer überhaupt, da Indexe auf den Tabellen direkt synchron sein müssen, und nicht, wie bei manch anderem Anbieter von In-Memory Datenbanken, erst mit Verzögerung die Daten erhalten. Auf dem PASS Summit 2014 war dieses Feature bereits kurz zu sehen, jedoch erst jetzt ist es offiziell und hat einen Namen.

Real-time Operational Analytics & In-Memory OLTP

Under this feature lies the marriage of the two „performance-killer“ features Columnstore Indexes – for OLAP-Scenarios – and Memory Optimized Tables for In-Memory OLTP. This enables for fast analytical queries on the fresh OLTP data in In-Memory. In my eyes this feature is the ultimate killer, since indexes on the tables have to be synchronous and not, like at some other vendors of In-Memory databases, only receive the data after a certain delay. At PASS Summit 2014 this features was already biefly presented, but only now it is official and has a name.

Das waren die Highlights, jedoch ist die Liste der weiteren neuen Features noch lang, und auch diese haben es in sich. Ich versuche, die wichtigsten einmal aufzulisten:

Those were the highlights, but the list of further new features is still long and highly potential. I try to list the most important ones:

Verbesserungen zu Columnstore Indexes

• Nicht-parallelisierte Abfragen können den Batch-Mode verwenden
• AlwaysOn Readable Secondary unterstützen beschreibbare Columnstore Indexe
• Columnstore Indexe auf einer Memory-Optimized Table möglich - nur im InterOP Mode verwendbar
• Filtered Nonclustered Columnstore Indexes
• Primary und Foreign Keys-Unterstützung

Improvements to Columnstore Indexes

• Single-threaded queries can use batch mode
• AlwaysOn readable secondary support updateable columnstore indexes
• Columnstore indexes possible on memory-optimized table – only works in InterOP mode
• Filtered Nonclustered Columnstore Indexes
• Support for primary and foreign keys

Verbesserungen für In-Memory OLTP

• ALTER TABLE und ALTER Procedure Operationen
  • Z.B. zum Ändern des Bucket counts von Hash Indexen
  • Multithreaded Writes und Merge-Prozess
  • Parallele Pläne
  • Fremdschlüssel auf In-Memory Tabellen
  • Viele weitere Verbesserungen Hinsichtlich T-SQL Support

Improvements for In-Memory OLTP

• ALTER and ALTER Procedure Operations
  • i.e. to change the bucket counts of hash indexes
  • Multithreaded Writes and Merge-process
  • parallel plans
  • foreign keys on In-Memory tables
  • many more improvements in terms of T-SQL support

Und weitere neue Security-Features:

Dynamic Data Masking

Damit lässt sich, basierend auf Policies, definieren, dass je nach User bestimmte Daten nur maskiert angezeigt werden.

And further new Security-Features:

Dynamic Data Masking

Based on Policies allow to define that depending on the user certain data is only shown maked.

Row-Level Security

Row-Level Security (RLS) ist um genau zu sein ein Feature um die Entwicklung von Zeilenbasierten Zugriffsmechanismen wesentlich zu vereinfachen und aus der Applikation in die Datenbank zu verlagern. In der jetzigen Implementation ist es noch recht einfach, über Side-Channel Attacken dennoch an die Daten heranzukommen. Daher muss man, wie eigentlich immer bei Sicherheits-Design, alle Bestandteile der Applikation im Blick haben.

Warum eine wirklich sichere Zeilenbasierte Zugriffskontrolle nicht so trivial ist, kann man unter anderem in diesem Blog-Post von mir nachlesen:

Row-Level Security

Row-Level Security (RLS) is, to be exact, a feature to greatly simplify the development of row-based access-control, and move from the application into the database. In the current implementation it is still quite easy, to get via Side-Channel Attacks to the data. Therefore, as always when designing for security, one has to have an eye on all parts of the application.

Why a truly secure row-based access control is far from trivial, one can find out among others in this blog-post of me:

Weitere Features in verschiedenen Bereichen:

PolyBase

Abfragen von relationalen und Nicht-relationalen Daten in Hadoop-Clustern mit T-SQL über sogenannte External Tables.

Further features in different areas:

PolyBase

Querying of relational and non-relational data in Hadoop-Clusters with T-SQL via so-called External Tables.

Query Store

Dieses Feature wurde schon mehrere Male von Conor Cunningham auf verschiedenen Konferenzen angedeutet. Jetzt kommt es wirklich.
Wenn eingestellt (pro Datenbank), werden im Hintergrund automatisch Abfragen und deren Pläne sowie Ausführungsstatistiken gesammelt, so dass man diese später nachvollziehen kann. Zusätzlich dazu kann man sehr leicht einen identifizierten Abfrageplan wiederverwenden.

Query Store

This feature has been presented several times at different conferences by Conor Cunningham. Now it is finally coming into the product.
Once activated, it automatically captures queries together with their plans and runtime statistics, thus allowing for later review of them. Added to that one can easily re-use an identified query plan.

Live Query Statistics

Im SQL Server 2016 erwecken Ausführungspläne „zum Leben“. Das lässt sich am besten mit einem Bild/Video veranschaulichen:

Live Query Statistics

In SQL Server 2016 query plans „come to live“. This is illustrated best with a picture/video:

Temporal Tables

Mit Temporal Tables werden Daten automatisch versioniert und stehen später für Abfragen nach Zeitraum der Gültigkeit zur Verfügung. Ähnlich, wie man es für Slowly Changing Dimensions/SCDs (Langsam veränderliche Dimensionen) in Datawarehouses selber oft entwirft.

Temporal Tables

With Temporal Tables data is automatically versioned and available for later querying based on time-span of validity. Similar to how one often designs oneself manually for Slowly Changing Dimensions/SCDs in Datawarehouses.

Trace Flag 4199, welches seit SQL Server 2005 SP3 CU 6 für alle SQL Server Installationen relevant ist, da es diverse Fixe des Query Prozessors erst aktiviert soll mit dem SQL Server 2016 kaum noch nötig sein. Warten wir auf Details, um das beurteilen zu können.

Trace Flag 4199, which is relevant for all SQL Server Installations since SQL Server 2005 SP3 CU 6, since it enables certain fixes of the query processor is supposedly mainly unnecessary for SQL Server 2016. Let’s wait for details for a final judgement.

Multiple Tempdb Datendateien

Bei der Installation wird direkt nachgefragt, wie viele TempDB Datendateien angelegt werden sollen. Standardmäßig wird hier die Anzahl der Cores mit einem Maximum von 8 verwendet. Damit werden auch weniger versierte Administratoren auf die Thematik von Latch-Contention in der Tempdb aufmerksam, und eine wichtige Best Practice wird damit standardmäßig angewandt.

Multiple Tempdb Data Files

Already at Setup one has to specify how many TempDB data files will be created. By default the number of cores with a maximum of 8 will be used. BY that that also less experienced administrators will become aware of  Latch-Contention in Tempdb, and an important Best Practice is applied by default.

Hochverfügbarkeit

• 3 Synchrone Replicas
• Unterstützung für Group Managed Service Accounts für AlwaysOn Failover Cluster
• AlwaysOn Availability Groups unterstützen Distributed Transactions und DTC auf Windows Server 2016
• AlwaysOn Availability Group Failover bei Database Offline
• ReadOnly Replikas können im Round-Robin-Verfahren angesprochen werden

High Availability

• 3 synchronous replicas
• Support for Group Managed Service Accounts for AlwaysOn Failover Cluster
• AlwaysOn Availability Groups support Distributed Transactions and DTC on Windows Server 2016
• AlwaysOn Availability Group Failover on Database Offline
• ReadOnly Replicas can be addressed in Round-Robin-manner

T-SQL

• Formatieren von Query Resultsets als JSON – ähnlich wie XML. Und möglicherweise wird es ein ähnliches Nischendasein erleiden. Hier sollte man auch an die, im Verhältnis zu einem Applikation-Server, teuren CPU-Lizenzen denken.

T-SQL

• Formatting of Query Resultsets as JSON – similar to XML. And maybe it will stay niched in a similar manner. Here one should think of the, expensive CPU-licences compared to an Applikation-Server

(en)
Finally I get to write about my conference-talks in 2015.

The year started really great with the German SQL Server Konferenz in Darmstadt with 2 sessions on In-Memory OLTP in SQL Server 2014 – one of them being even a full-day PreCon, which I co-presented with Niko Neugebauer,  who was talking about Clustered ColumnStore Indexes.

(de)
Endlich komme ich dazu, auf meine Konferenz-Vorträge dieses Jahr einzugehen.

Das Jahr begann gleich großartig mit der Deutschen SQL Server Konferenz in Darmstadt mit 2 Sessions zu In-Memory OLTP in SQL Server 2014 – eine davon sogar eine ganztägige PreCon, welche ich mit Niko Neugebauer co-präsentierte, der über Clustered ColumnStore Indexe sprach.

In March I was happy to have been speaker for the second time at the SQLBits in London -
probably THE event outside of PASS in Europe if not worldwide.
There I presented one of my favorite topics on Performancetools: Extended Events

Im März war ich glücklich zum zweiten Mal auf den SQLBits in London als Sprecher zu sein – wahrscheinlich DEM Event außerhalb der PASS in Europe wenn nicht weltweit.
Dort präsentierte ich zu einem meiner Lieblingsthemen zu Performancetools: Extended Events

In April I joined the SQLDay Poland for the first time, giving a session on Locking & Blocking and a second session on Security, namely SQL Injection:

Im April war ich das erste Mal auf dem SQLDay Poland  , mit einer Session zu Sperren & Blockaden und einer zweiten Sessio zu Sicherheit, genauer SQL Injection:

For June I am happy to be able to announce that I will again present on Extended Events at the SQL Saturday #409 Rheinland/Germany. -
In fact I have already presented at the first German SQL Saturday in 2012 on Extended Events (“Tracing with SQL Server 2012 Extended Events”) – I hope this year it will be the final round to get even the last one off from the old & dusty SQL Profiler.

Added to that I am honored to give one of the 2 full day and free of cost PreCons: Together with Patrick Heyde, Microsoft (Technet-Blog), I will talk on SQL Server in Azure Environments. Specifically on how to optimally configure such a deployments to performance and cost-efficiency at the same time. (Also see my blog article “SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time”)

Besides giving a free PreCon and regular session, my Company Sarpedon Quality Lab is again officially sponsoring this home-event. This is part of my commitment to the German PASS Community – especially since my own family starts taking more time, this is a bit of what I can do to support the PASS Deutschland e.V..

Für den Juni darf ich mich glücklich schätzen, bekanntzugeben, dass ich auf dem SQL Saturday #409 Rheinland wieder zu Extended Events präsentieren werde. – Tatsächlich hatte ich bereits auf dem ersten Deutschen SQL Saturday 2012 zu Extended Eventzs präsentiert
(“Tracing with SQL Server 2012 Extended Events”) – Ich hoffe dieses Jahr wird die letzte Runde sein, um auch den Letzten von dem alten & verstaubten SQL Profiler abzuholen.

Zusätzlich dazu fühle ich mich geehrt, eine der 2 ganztägigen und kostenlosen PreCons zu geben: Zusammen mit Patrick Heyde, Microsoft (Technet-Blog), werde ich über SQL Server in Azure Umgebungen sprechen. Und zwar, wie man solche Deployments optimal konfiguriert um sowohl Performance- als auch Kosteneffizient zugleich zu sein. (Siehe auch meinen Blog-Artikel “SQL Server in Microsoft Azure: Wie man durch Flexibilität Leistung gewinnt und zugleich Kosten spart”)

Abgesehen von der kostenblosen PreCon und regulärem Vortrag, ist meine Firma Sarpedon Quality Lab wieder offizieller Sponsor dieses Events. Das ist Teil meines Engagements für die deutsche PASS Community – speziell seit meine eigene Familie mehr Zeit beansprucht, ist das ein wenig dessen, was ich tun kann, den PASS Deutschland e.V. zu unterstützen.

In September I will be at the SQLSaturday #413 Denmark in Copenhagen, organized among other by Regis Baccaro. I will be giving yet another PreCon on In-Memory OLTP and Clustered ColumnStore and a regular session on SQL Server storage.

Im September werde ich auf dem SQLSaturday #413 Denmark in Kopenhagen, organisiert neben anderen von Regis Baccaro, sein. HIer gebe ich wieder eine PreCon zu In-Memory OLTP und Clustered ColumnStore und eine reguläre Session zu SQL Server Speicher.