📖💡 DCL - Data Control Language

SQL Server - Berechtigungskonzept

Bei SQL Server erfolgt der Zugriff auf Datenbanken in zwei Stufen: Mit dem Server Login meldet man sich auf dem SQL Server an, mit dem Database User greift man auf eine Datenbank zu. In jeder einzelnen Datenbank, auf die ein Anwender zugreifen möchte, wird ein separater User benötigt. Dieser User wird beim Anlegen einem Login zugeordnet. So kommt ein Endanwender lediglich mit dem Login in Kontakt. Ein Passwort ist nur für das Login erforderlich. Damit in der Praxis die Zugriffsverwaltung übersichtlich bleibt und erleichtert wird, ist es ratsam, dem einem Login zugeordneten User jeweils denselben Namen zu vergeben.

Die Authentifizierung am SQL Server kann entweder über ein Windows-Konto oder über ein SQL Server-Konto erfolgen. Bei der Windows-Authentifizierung werden Windows-Domänenkonten als Logins auf dem SQL Server registriert. Diese Variante hat für den Endanwender den Vorteil, dass dieser sich keinen weiteren Kontonamen samt Passwort für die Anmeldung merken muss. Für alle anderen Anwender ausserhalb der Windows-Domäne muss ein eigenes SQL Server-Login samt Passwort erstellt werden.

Principal	Beschreibung	Beispiele von Rollen
Server Login	Zuweisung von Berechtigungen auf Serverebene. In der Regel keine direkte Zuweisung von Berechtigungen, sondern Mitgliedschaft in bestimmten Serverrollen. Login-Berechtigungen werden in der System-Datenbank master gespeichert	sysadmin serveradmin securityadmin
Database User	Zuweisungen von Berechtigungen innerhalb einer Datenbank. Mit der Zuweisung von bereits vordefinierten Datenbankrollen kann eine einfache Berechtigungsverwaltung umgesetzt werden. User, Datenbankrollen und die an sie erteilten Berechtigungen werden in der jeweiligen Datenbank gespeichert.	db_datareader db_datawriter

Principal erstellen und einer Rolle zuweisen

-- SQL Server-Login erstellen
USE master;
CREATE LOGIN anmelde_name
WITH PASSWORD = 'passwort', CHECK_POLICY = OFF;

-- Dem Login eine Serverrolle zuweisen
ALTER SERVER ROLE rollen_name
ADD MEMBER anmelde_name;

-- Einem Login ein User zuordnen
USE mydatabase;
CREATE USER user_name FOR LOGIN anmelde_name;

-- Dem User eine Datenbankrolle zuweisen
ALTER ROLE rollen_name ADD MEMBER user_name;

Das Server-Login muss in der Datenbank master erstellt werden. Vor dem Zuordnen des User zum Login muss man sich dann in der entsprechenden DB befinden. Jeder neu angelegte User befindet sich automatisch in der Datenbankrolle public.

Option	Beschreibung
DEFAULT_DATABASE = database	Setzt die Standarddatenbank auf database
CHECK_EXPIRATION = { ON / OFF }	Ob Passwort ablaufen soll (Standard OFF)
CHECK_POLICY = { ON / OFF }	Ob Passwort Richtlinen entsprechen muss (Standard ON)

Principial löschen

-- Server Login löschen
DROP LOGIN anmelde_name;

-- Datenbank User löschen
drop user user_name;

DCL - Verwalten von Berechtigungen

Objektberechtigungen erlauben den Zugriff auf Objekte innerhalb der Datenbank. Anweisungsberechtigungen werden "gewöhnlichen" Datenbankbenutzern in der Regel nicht gewährt. Sie beziehen sich nicht auf bestehende Objekte, sondern legen fest, wer Datenbankobjekte erstellen, verwalten und sichern darf.

Anweisungsberechtigungen	Objektberechtigungen
CREATE DATABASE	SELECT
CREATE DEFAULT	INSERT
CREATE FUNCTION	DELETE
CREATE PROCEDURE	REFERENCES
CREATE TABLE	UPDATE
CREATE VIEW	EXECUTE
BACKUP DATABASE
BACKUP LOG

Berechtigungen vergeben

Dies geschieht mit dem Befehl GRANT.

GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO administrator;

	Anweisungsberechtigung	Objektberechtigung
Syntax	GRANT statement TO user;	GRANT berechtigung ON objekte TO user [WITH GRANT OPTION];
Beispiel Tabelle	GRANT CREATE TABLE TO user1;	GRANT SELECT, UPDATE ON tabelle1 TO user1, user2;
Beispiel Schema		GRANT SELECT, UPDATE ON SCHEMA::<schema> TO user1

Rechte an alle vergeben: TO PUBLIC. WITH GRANT OPTION: User darf diese Berechtigung weitergeben.

Datenbanken sind Hochsicherheitstrakte: Alles was nicht explizit erlaubt wird, ist verboten.

Berechtigungen entziehen

Dies geschieht mit dem Befehl REVOKE.

	Anweisungsberechtigung	Objektberechtigung
Syntax	REVOKE statement FROM user;	REVOKE [GRANT OPTION FOR] berechtigung ON objekte FROM user [CASCADE];
Beispiel	REVOKE CREATE TABLE FROM user1;	REVOKE SELECT ON tabelle1 FROM user1;

REVOKE bedingt dann CASCADE, wenn WITH GRANT OPTION vergeben wurde.

Berechtigungen verweigern

Mit DENY kann man Berechtigungen explizit verweigern, damit diese nicht indirekt über Rollenmitgliedschaften erlangt werden können.

	Anweisungsberechtigung	Objektberechtigung
Syntax	DENY statement TO user;	DENY berechtigung ON objekte TO user;
Beispiel	DENY CREATE TABLE TO user1;	DENY SELECT ON tabelle1 TO user1, user2;

💡 Aufgabe 10

Öffne die Datei in Azure Data Studio und löse die Aufgaben.

2KB

M105_Aufgabe10_Zugriffe_verwalten.ipynb

Aufgabe 10 - Zugriffe verwalten