Eine dieser berühmt-berüchtigten "Kleinigkeiten", über die ich mir bisher wenig Gedanken gemacht habe, ist die Sichtbarkeit von Metadaten innerhalb einer SQL Server Datenbank. Sie waren bisher einfach immer und überall verfügbar, sprich abfragbar. Bis jetzt, als wir dann eine Applikation troubleshooten mussten, die Datenmodifikationen nicht mehr korrekt trackte. Man stelle sich einmal folgendes Szenario vor:

Eine einigermassen gesicherte Datenbank, in der die Berechtigungen auf die Basistabellen eingeschränkt, bzw. entzogen wurden und Zugriff auf die Daten durch Gespeicherte Prozeduren geregelt wird. Aus Gründen, die für diesen Beitrag keine Rolle spielen, wird innerhalb dieser Prozeduren auf die Metadaten zugegriffen.

Was passiert? Nun, in SQL Server 2000 und früher nichts. Die Prozeduren lieferten das erwartete Ergebnis, da die Metadaten einfach der Public Rolle zur Verfügung stehen und um Berechtigungen zum Zugriff auf diese Daten musste man sich keine Gedanken machen.

Dies ist mit SQL Server 2005 anders geworden. Bei einer gesicherten Datenbank muss man sich hier auch Gedanken zum Berechtigungskonzept für den Zugriff auf Metadaten machen. Das macht die gesamte Entwicklung natürlich etwas komplexer, macht aber Sinn, wenn man sich ernsthaft damit befasst. Schauen wir uns einmal folgendes Szenario an:

Zunächst einmal erstellen wir eine neue Datenbank, mit der man experimentieren kann:

USE master
GO

IF EXISTS
	(SELECT 1
	FROM sys.databases
	WHERE [name] = 'mw')
	DROP DATABASE mw
GO

CREATE DATABASE mw
GO

Nun switchen wir den Datenbankkontext auf diese neue Datenbank und innerhalb dieser Datenbank erstellen wir nun einen User "MyDummyUser".

USE mw
CREATE USER MyDummyUser WITHOUT LOGIN;
GO

Jetzt erstellen wir eine Tabelle, fügen einen einzelnen Datensatz ein und widerrufen das SELECT Recht auf diese Tabelle von dem MyDummyUser.

CREATE TABLE dbo.t (c1 int)
GO
INSERT INTO dbo.t SELECT 1

REVOKE SELECT ON OBJECT::dbo.t FROM MyDummyUser;
GO

Folgerichtig läuft ein SELECT Statement auf Tabelle dbo.t welches im Kontent von MyDummyUser ausgeführt wird auf einen Fehler:

EXEC AS USER = 'MyDummyUser'
SELECT * FROM dbo.t
REVERT

Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 't', database 'mw', schema 'dbo'.

Zum Vergleich hier das gleiche Statement nun aber ausgeführt im Kontext des Datenbankbesitzers:

EXEC AS USER = 'dbo'
SELECT * FROM dbo.t
REVERT
GO

c1
-----------
1

(1 row(s) affected)

Nachdem nun gewährleistet ist, das der direkte Zugriff auf die Tabelle dbo.t nicht ohne weiteres möglich ist, können wir uns darum kümmern, den Zugriff auf die Daten durch Gespeicherte Prozeduren zu steuern.

CREATE PROCEDURE dbo.test1
AS
SET NOCOUNT ON
SELECT * FROM dbo.t
RETURN
GO

GRANT EXECUTE ON OBJECT::dbo.test1 TO MyDummyUser;
GO

Was passiert nun, wenn man diese Prozedur im Kontent von MyDummyUser ausführt?

EXEC AS USER = 'MyDummyUser'
EXEC dbo.test1
REVERT
GO

Die Prozedur wird fehlerfrei ausgeführt und liefert das gewünschte Ergebnis zurück:

c1
-----------
1

Was passiert nun aber, wenn innerhalb einer solchen Gespeicherten Prozedur auf die Metadaten der Tabelle dbo.t zurückgegriffen werden soll? In der folgenden Prozedur soll auf die Metadatenfunktion OBJECT_ID() zugegriffen weren, die die interne ID, welche SQL Server für die spezifizierte Tabelle vergeben hat, zurückgibt:

CREATE PROCEDURE dbo.test
AS
SET NOCOUNT ON
SELECT OBJECT_ID('dbo.t')
RETURN
GO
GRANT EXECUTE ON OBJECT::dbo.test TO MyDummyUser;
GO

Zunächst einmal zu Demonstrationszwecken wird die Prozedur im Kontext des Datenbankbesitzers ausgeführt:

EXEC AS USER = 'dbo'
EXEC dbo.test
REVERT
GO

So in etwa soll das Ergebnis aussehen:

-----------
2073058421

Nun der gleiche Aufruf im Kontext unseres MyDummyUsers:

EXEC AS USER = 'MyDummyUser'
EXEC dbo.test
REVERT
GO

… mit einem überraschenden Ergebnis:

-----------
NULL

Was passiert hier? Nun, das Ergebnis ist NULL, aber warum? Betrachtet man das CREATE PROCEDURE Statement sieht man keine weiteren Angaben zum Sicherheitskontext, welchen man innerhalb einer Prozedur über EXECUTE AS steuern kann. Sofern nicht anders angegeben, wird eine Prozedur im Kontext des aufrufenden Benutzers ausgeführt. Das heisst, der Benutzer MyDummyUser versucht über die Prozedur per OBJECT_ID() Metadaten der Tabelle dbo.t anzufragen. Da MyDummyUser aber keinerlei Berechtigung auf dbo.t hat, wird nicht die ID, sondern NULL zurückgegeben. Verlässt man sich nun darauf, daß ein "anderer" Wert zurückgegeben wird, ohne NULL in Betracht zu ziehen, hat man schlechte Karten und muss seinen Code umschreiben.

Allerdings gibt es natürlich auch einen Workaround und der liegt in Context Switching mit Hilfe der WITH EXECUTE AS OWNER Angabe:

DROP PROCEDURE dbo.test
GO

CREATE PROCEDURE dbo.test
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
SELECT OBJECT_ID('dbo.t')
RETURN
GO

Noch mal als Referenz der Aufruf als dbo:

EXEC AS USER = 'dbo'
EXEC dbo.test
REVERT
GO

-----------
2073058421

Und hier ausgeführt als MyDummyUser:

EXEC AS USER = 'MyDummyUser'
EXEC dbo.test
REVERT
GO

-----------
2073058421

Jetzt, da innerhalb der Prozedur der Kontext auf den Besitzer der Prozedur, also dbo, geändert wird, erhält man das gewünschte Ergebnis, da dbo natürlich über Rechte auf dbo.t verfügt. Nun sollte man aber Context Switching nicht als übliches Mittel zur Entwicklung einsetzen, sondern vielmehr mit Bedacht und Umsicht, nachdem man verstanden hat, was dabei eigentlich passiert und was für Konsequenzen damit einhergehen.

Wer mehr zu diesem Thema wissen möchte, kann das unter "Sichtbarkeit von Metadaten" in den Books Online nachlesen.