Start

Unternehmen

ERP / PPS / Prozesse

Business Intelligence

Server-Technologien

Software-Technologien

Technologie-Beratung

Individual-Software

Produkte

Comelio GmbH
Am Fischhof 3
A-1010 Wien
Österreich
Fon: +43-720-2097-97
Fax: +43-720-2097-98
info@comelio.com

Comelio GmbH (Ecos)
Stiglmaierplatz/Dachauer Str. 37
D-80335 München
Deutschland
info@comelio.com

Comelio GmbH
Rellinghauser Straße 10
D-45128 Essen
Deutschland
Fon: 0201-437517-0
Fax: 0201-437517-10
info@comelio.com

Comelio GmbH
Goethestraße 34
D-13086 Berlin
Deutschland
info@comelio.com

Comelio-Blog > MS SQL Server > Verwaltungsarbeiten bei Modulen

Verwaltungsarbeiten

Dieser Abschnitt gibt neben ausführlichen Hinweisen zur Sicherheit von Modulen auch zusätzliche Informationen, wie Informationen über Funktionen und Prozeduren abgerufen werden können.

Kontakt Anrede* Herr Frau Vorname* Nachname* Firma E-Mail* Tel-Nr. Bereich* Comelio-Blog > MS SQL Server > Verwaltungsarbeiten bei Modulen Freitext

Verwaltungsarbeiten

Die Abfrage SELECT * FROM sys.sql_modules liefert eine Aufstellung der in der jeweiligen Datenbank, in der Abfrage ausgeführt wird, vorhandenen Funktionen, Prozeduren und Trigger. Es handelt sich dabei um eine so genannte Katalogsicht, welche Daten über das System in relationaler Form liefern, die ansonsten nur in der Oberfläche abgerufen werden können.

Folgende Spalten sind in der Ergebnismenge enthalten:

• object_id (int) enthält eine in der Datenbank eindeutige ID des Objekts.
• definition (nvarchar(max)) enthält den T-SQL-Quelltext des Moduls oder NULL, wenn es verschlüsselt ist.
• uses_ansi_nulls (bit) gibt an, ob das Modul mit SET ANSI_NULLS ON erstellt wurde und ist immer 0 für Regeln und Standardwerte.
• uses_quoted_identifier (bit) gibt an, ob das Modul mit SET QUOTED_IDENTIFIER ON erstellt wurde.
• is_schema_bound (bit) gibt an ob, das Modul mit der Option SCHEMABINDING erstellt wurde.
• uses_database_collation (bit) liefert 1, wenn die richtige Auswertung der schemagebundenen Definition des Moduls abhängig von der Standardsortierung der Datenbank ist, ansonsten 0. Sollte hier eine Abhängigkeit bestehen, wird eine Änderung der Standardsortierung der Datenbank verhindert.
• is_recompiled (bit) gibt an, ob die Prozedur mit der Option WITH RECOMPILE erstellt wurde.
• null_on_null_input (bit) gibt an, ob das Modul so erstellt wurde, dass die Übergabe von NULL-Werten der Wert NULL folgt.
• execute_as_principal_id (int) liefert die ID des Besitzers in EXECUTE AS. Der Standardwert ist NULL oder EXECUTE AS CALLER. Ansonsten wird die ID des Benutzers bei EXECUTE AS SELF oder EXECUTE AS <user> geliefert. Der Wert -2 entsteht bei EXECUTE AS OWNER.

Die Abfrage SELECT * FROM sys.objects liefert die benutzerdefinierten Objekte und u.a. Funktionen und Prozeduren, aber keine Trigger, da sie nicht schemagebunden sind. Trigger findet man dagegen in sys.triggers.

In einigen Spalten werden mit Hilfe von Buchstabenkürzeln Objekttypen angegeben. Einige Objekte sind im DBA-Buch zu finden: AF = Aggregatfunktion (.NET), C = CHECK-Einschränkung, D = DEFAULT (Einschränkung oder eigenständig), F = FOREIGN KEY-Einschränkung, PK = PRIMARY KEY-Einschränkung, P = Gespeicherte SQL-Prozedur, PC = Prozedur (.NET), FN = SQL-Skalarfunktion, FS = Skalarfunktion (.NET), FT = Tabellenwertfunktion (.NET), R = Regel (Version 2000, eigenständig), RF = Replikationsfilterprozedur, SN = Synonym, SQ = Dienstwarteschlange, TA = DML-Trigger (.NET), TR = DML-Trigger, IF = Einfache Tabellenwertfunktion, TF = Tabellenwertfunktion, U = Tabelle (benutzerdefiniert), UQ = UNIQUE-Einschränkung, V = Sicht, X = Erweiterte gespeicherte Prozedur, IT = Interne Tabelle.

Die gleiche Struktur weisen die Sichten sys.system_objects und sys.all_objects auf. Diese Struktur besteht aus folgenden Spalten:

• name (sysname) enthält den Objektnamen.
• object_id (int) enthält die innerhalb der Datenbank eindeutige Objekt-ID.
• schema_id (int) enthält die Schema-ID, in dem das Objekt enthalten ist. Für die in Version 2005 vorhandenen Systemobjekte in einem Schema ist dies immer IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA').
• principal_id (int) enthält die Besitzer-ID, falls es nicht der Schemabesitzer ist, oder NULL bei folgenden Objekten: C, D, F, PK, R, TA, TR, UQ
• parent_object_id (int) enthält die Eltern-Objekt-ID oder 0.
• type (char(2)) enthält den Objekttyp: AF, C, D, F, PK, P, PC, FN, FS, FT, R, RF, SN, SQ, TA, TR, IF, TF, U, UQ, V, X, IT
• type_desc (nvarchar(60)) beschreibt das Objekt mit den folgenden Werten: AGGREGATE_FUNCTION, CHECK_CONSTRAINT, DEFAULT_CONSTRAINT, FOREIGN_KEY_CONSTRAINT, PRIMARY_KEY_CONSTRAINT, SQL_STORED_PROCEDURE, CLR_STORED_PROCEDURE, SQL_SCALAR_FUNCTION, CLR_SCALAR_FUNCTION, CLR_TABLE_VALUED_FUNCTION, RULE, REPLICATION_FILTER_PROCEDURE, SYNONYM, SERVICE_QUEUE, CLR_TRIGGER, SQL_TRIGGER, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_TABLE_VALUED_FUNCTION, USER_TABLE, UNIQUE_CONSTRAINT, VIEW, EXTENDED_STORED_PROCEDURE, INTERNAL_TABLE
• create_date (datetime) enthält das Erstelldatum.
• modify_date (datetime) enthält das letzte Änderungsdatum über ALTER oder durch einen Index.
• is_ms_shipped (bit) gibt an, ob es eine interne SQL Server-Komponente ist.
• is_published (bit) gibt an, ob das Objekt veröffentlicht wurde.
• is_schema_published (bit) gibt an, ob nur das Schema des Objekts veröffentlicht wurde.

Die Sicht sys.procedures liefert eine Zeile für jede Prozedur vom Typ sys.objects.type = P, X, RF und PC. Zusätzlich zu den Spalten von sys.objects gibt es noch folgende Spalten:

• is_auto_executed (bit) liefert bei Prozeduren in der master-DB 1, wenn die Prozedur Serverstart automatisch ausgeführt wird, sonst 0.
• is_execution_replicated (bit) gibt an, ob die Ausführung der Prozedur repliziert wird.
• is_repl_serializable_only (bit) gibt an, ob die Ausführung der Prozedur nur repliziert wird, wenn die Transaktion serialisiert werden kann.
• skips_repl_constraints (bit) gibt an ob, die Prozedur Einschränkungen überspringt, die mit NOT FOR REPLICATION angegeben sind.

Funktionen

Neben den zuvor vorgestellten Katalogsichten gibt es noch verschiedene Funktionen, mit deren Hilfe Objekteigenschaften abgefragt werden können.

Die Funktion OBJECT_ID() liefert die Objekt-ID eines Objekts. Die allgemeine Syntax lautet:

OBJECT_ID ( 
    '[ database_name . [ schema_name ] . | schema_name . ] 
       object_name' [ ,'object_type' ] )

Das nächste Beispiel prüft auf Existenz und ermittelt dann weitere Daten.

IF OBJECT_ID (N'AdventureWorks.Production.
                usp_GetProduct', N'P') IS NOT NULL
BEGIN
 SELECT OBJECT_ID(N'AdventureWorks.Production.
                    usp_GetProduct') AS 'Object ID'
 SELECT name, type_desc
   FROM sys.procedures
  WHERE [Object_ID] = OBJECT_ID(N'AdventureWorks.
                                  Production.usp_GetProduct')
END

Man erhält als Ergebnis:

Object ID
-----------
320720195
name              type_desc
----------------- ----------------------
usp_GetProduct    SQL_STORED_PROCEDURE

Sicherheit

Die Ausführung von gespeicherten Prozeduren beinhaltet verschiedene sicherheitsrelevante Aspekte, da hier kleine Softwarebausteine direkt in der Datenbank ausgeführt werden, deren gespeicherte Anweisungen verschiedene Schema-Objekte ansprechen können. Dies betrifft grundsätzlich alle Datenbanken, in denen Prozeduren erstellt werden können. Hier muss man sich lediglich vorstellen, dass ein Benutzer einem anderen Benutzer die Ausführrechte an seiner Prozedur erteilt, welche in ihren Anweisungen eigentlich für den ausführenden Benutzer verbotene Schema-Objekte benutzt.

Im Normalfall greift eine Reihe von T-SQL-Anweisungen nacheinander auf mehrere Objekte zu. Wenn diese Objekte nicht nur Tabellen sind, sondern auch Prozeduren und Funktionen, ist es leicht vorstellbar, dass innerhalb dieser Module wiederum auf anderen Module bzw. wenigstens auf Tabellen oder Sichten zugegriffen wird. Dies wird als Kette bezeichnet, da ein Objekt das nächste aufruft. Dabei gelten besondere Sicherheitsregeln, die nicht denen entsprechen, als hätte ein Benutzer, der die Kette angestoßen hat, selbst die einzelnen Objekte angesprochen. Mit dem Begriff der Besitzkette wird nun das Prinzip beschrieben, dass die von einem Objekt nachfolgend aufgerufenen Objekte mit Hilfe einer speziellen Sicherheitsverwaltung tatsächlich aufrufbar sind. So soll ein Leistungsabfall vermieden werden, der entstehen würde, wenn permanent einzelne Berechtigungsprüfungen durchgeführt werden, wie dies bei einem getrennten, nacheinander erfolgenden Aufruf der Fall wäre.
Wird innerhalb einer Kette aufgerufen, dann prüft der MS SQL Server zunächst, ob der Besitzer (= Benutzer) des aufrufenden Objekts auch tatsächlich der Besitzer des aufgerufenen Objekts ist. Ist dies der Fall, hat der Besitzer (=Benutzer) auch die entsprechenden Berechtigungen am nachfolgend aufgerufenen Objekt und die Berechtigungen werden nicht weiter ausgewertet. Was soll allerdings geschehen, wenn dies nicht der Fall ist? Der MS SQL Server 2000 prüfte hier zunächst danach, ob die gespeicherte Prozedur und die angesprochenen Objekte im gleichen Schema liegen, ob die Aktivität statisch ist und damit kein dynamisches SQL enthält und ob schließlich die Aktivität nur DML-Operationen (SELECT, INSERT, UPDATE und DELETE) enthält oder eine andere gespeicherte Prozedur aufruft. Trafen alle Fälle zu, so konnte ein anderer Benutzer, der nur die Ausführrechte einer Prozedur, aber keine direkten Rechte an den durch die Prozedur bearbeiteten Objekten besaß, sehr wohl die Prozedur benutzen. Im MS SQL Server 2005 wurde dieses Berechtigungskonzept zunächst übernommen, sodass hier Abwärtskompatibilität und Vergleichbarkeit vorherrscht. Allerdings bietet die neue Version auch nun verfeinerte Vorgabemöglichkeiten bei der Prozedurerstellung.

Schließlich ist es auch möglich, datenbankübergreifende Besitzverkettungen zu ermöglichen. Sie ist standardmäßig deaktiviert.
In der Abbildung soll das Grundproblem noch einmal grafisch dargestellt werden:

Verschiedene Objekte haben verschiedene Besitzer, wobei Benutzer B selbst gar kein Besitzer irgendeines Objekts ist, sondern er nur von Benutzer A die Berechtigungen erhalten hat, die Prozedur von Benutzer A auszuführen. In (1) ist er autorisiert, die Prozedur auszuführen, weil er die Berechtigung von A erhalten hat. Diese Prozedur ruft in (2) eine Sicht von Benutzer C ab, wobei nun allerdings die vollständigen Berechtigungen abgerufen werden, weil sich beide Besitzer unterscheiden. Sofern hier auch Benutzer B für die Sicht autorisiert ist, werden die Daten zurückgeliefert. Diese Sicht wirkt sich nun wiederum in (3) auf eine Tabelle aus, deren Besitzer Benutzer D ist. Da hier erneut ein Besitzerwechsel stattfindet, müssen die gesamten Berechtigungen abgerufen werden, und auch Benutzer B wird auf Nutzungsberechtigung dieser Tabelle überprüft. Schließlich ist in (4) auch noch die datenbankübergreifende Besitzverkettung dargestellt, die hier funktioniert, weil sie entsprechend aktiviert wurde. Benutzer B hat die Berechtigung von Benutzer A an dieser Sicht und darf daher sogar von einer anderen DB die Daten abrufen.

Die Besitzkette ist also mehrfach unterbrochen, weil sich die Besitzer unterscheiden. Unter eine fortlaufenden Besitzkette versteht man dagegen den verketteten Aufruf von Objekten eines einzigen Benutzers.

Unter Einsatz der neuen Klausel EXECUTE AS kann man nun innerhalb einer Prozedur den Sicherheitskontext einer Prozedur genau festlegen. Folgende allgemeine Syntax existiert für Funktionen, Prozeduren und Trigger, wobei die Syntax für Trigger noch zusätzliche Erweiterungen besitzt:

Funktionen (außer inline table-valued-Funktionen), Prozeduren und DML-Trigger

  { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

Die Bedeutung der verschiedenen Einstellungen ergeben sich fast schon aufgrund ihres Namens:

• CALLER (Standardwert) legt fest, dass die Prozedur im Sicherheitskontext des Aufrufenden, d.h. des Benutzers, ausgeführt wird. Dies ist auch der Standard unter der Vorgänverversion MS SQL Server 2000.
• SELF legt fest, dass die Prozedur im Sicherheitskontext des Besitzers der Prozedur oder desjenigen, der den ALTER-Befehl abgesetzt hat, ausgeführt wird. Dies entspricht der <user_name>-Option, wobei hier als Benutzername automatisch der erstellende oder ändernde Benutzer eingetragen wird.
• OWNER legt fest, dass die Prozedur nur im Sicherheitskontext des Besitzers ausgeführt wird. Hier ist keine Rolle oder Gruppe möglich, nur ein einzelnes Benutzerkonto.
• <user_name> legt einen speziellen Benutzer fest, in desse Sicherheitskontext die Prozedur ausgeführt werden soll. Dabei darf der Benutzer kein(e) Gruppe, Rolle, Zertifikat, Schlüssel oder integriertes Konto sein.

Schließlich soll noch in Transact SQL kurz erklärt werden, wie die Ausführberechtigung erteilt und wieder entzogen wird. Die beiden Befehle GRANT und REVOKE werden im Administrationsbuch noch ausführlich erläutert, sodass hier nur eine Kurzfassung folgt. Folgende Rechte können für Prozeduren und Funktionen erteilt und entzogen werden. Sie werden gleichzeitig über die ALL-Option zusammengefasst.

• Skalarfunktionen: EXECUTE, REFERENCES.
• Tabellenwertfunktionen: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
• Prozeduren: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.