datenbank-programmierung

Rechteverwaltung

Referenz https://aws.amazon.com/de/blogs/database/managing-postgresql-users-and-roles/

Grundbefehle

CREATE ROLE
GRANT
REVOKE

CREATE USER

Rollen

Mit CREATE ROLE werden Rollen erstellt. Neu erstellte Rollen haben keine Rechte.

Syntax

CREATE ROLE <Name>;

Beispiel

CREATE ROLE sales;

Mit DROP ROLE werden Rollen gelöscht

Syntax

DROP ROLE <Name>;

Beispiel

DROP ROLE sales;

Rechtevergabe

Die Rechtervergabe wird mit GRANT und REVOKE geregelt.

Syntax

GRANT <Berechtigung> ON <Datenbankobjekt> TO <Rollenname/Nutzername>

Das Datenbankobjekt wird hier aus Schema und Tabellenname zusammengesetzt.

Beispiele

GRANT SELECT ON company.emp_employee TO human_resources;
GRANT ALL ON ALL TABLES IN SCHEMA company TO sales; -- erlaube zugriff auf alle tabellen im schema
GRANT SELECT (first_name, last_name) ON company.employee TO other_role;

sales und employee sind verschiedene Rollen

Berechtigung Beschreibung
SELECT (<Spalten>) Verwendung von SELECT, Spalten sind optional
INSERT (<Spalten>) Verwendung von INSERT, Spalten sind optional
UPDATE (<Spalten>) Verwendung von UPDATE, Spalten sind optional
DELETE Verwendung von DELETE
CREATE Verwendung von CREATE TABLE
ALL Verwendung aller gelisteten

Rollenattribute

Weitere Attribute koennen fuer die Erstellung einer Rolle angegeben werden. Wichtige Attribute sind:

SUPERUSER / NOSUPERUSER (default): neue Rolle ist ein Superuser und hat keine Restriktionen

CREATEDB / NOCREATEDB (default): Rolle kann neue Datenbanken anlegen

CREATEROLE / NOCREATEROLE (default): Rolle kann andere Rollen verwalten

INHERIT (default) / NOINHERIT: Privilegien einer anderen Rolle werden geerbt

LOGIN / NOLOGIN (default): gibt der Rolle die Moeglichkeit sich einzuloggen

PASSWORD <password>: wenn die Rolle LOGIN spezifiziert, kann mit PASSWORD das Passwort des Logins gesetzt werden

IN ROLE <Rollenname>: weisst den angegebenen Rollenname als Elternrolle hinzu. Mit INHERIT erbt die neue Rolle alle Attribute.

Beispiele

CREATE ROLE jonathan WITH LOGIN PASSWORD '1234';

CREATE ROLE superfred WITH LOGIN PASSWORD '1234' CREATEDB CREATEROLE;

CREATE ROLE emmy WITH LOGIN PASSWORD '1234' IN ROLE sales;

https://www.postgresql.org/docs/13/sql-createrole.html

https://www.postgresql.org/docs/13/role-attributes.html

Rechte aufloesen

Mit REVOKE werden vergebene Rechte zurückgenommen.

Syntax

REVOKE <Berechtigung> ON <Datenbankobjekt> FROM <Rollenname/Nutzername>

Beispiel

REVOKE SELECT ON company.employee FROM sales;

Nutzerverwaltung

Nutzer anlegen

Syntax

CREATE USER <Name> WITH PASSWORD BY <Passwort>

Beispiel

CREATE USER fred WITH PASSWORD 'Passwort1234!';
GRANT mentor TO fred;

CREATE USER ist an Alias fuer CREATE ROLE + LOGIN PERMISSION

Mit DROP USER werden Nutzerkonten gelöscht.

Syntax

DROP USER <Nutzername>

Stored Procedures

Mit gespeicherten Prozeduren kann Geschaeftslogik in der Datenbank implementiert werden. Bisher haben wir die Logik in Java / der Applikationsseite realisiert.

Motivation

“Security is a key reason. Banks commonly use stored procedures so that applications and users don’t have direct access to the tables. Stored procedures are also useful in an environment where multiple languages and clients are all used to perform the same operations.”

Quelle

Programmierung

Beispiel

DROP PROCEDURE IF EXISTS insert_data;
CREATE PROCEDURE insert_data(
  IN col_name varchar,
  IN pro_name varchar
)
LANGUAGE SQL
AS $$
  INSERT INTO fao_favorite_color(fao_color_name) VALUES (col_name);
  INSERT INTO pro_project(pro_name) VALUES (pro_name);
$$;

CALL insert_data('pink', 'candy_shop');

Fuegt Datensaetze in die beiden Tabellen hinzu.

Stored procedures wurden in PostgeSQL 11 hizugfuegt und folgen nicht komplett dem SQL-Standard.

Quelle

Stored Procedure in JDBC

public void insertDataProcedure() throws SQLException {
    Connection conn = DriverManager.getConnection(...);

    String sql = "CALL insert_data(?,?)";
    CallableStatement stmt = conn.prepareCall(sql);

    stmt.setString(1, "brown");
    stmt.setString(2, "project x");

    stmt.executeUpdate();

    stmt.close();
    conn.close();
}

Stored Procedures in DBeaver

Functions

Beispielfunktion

CREATE FUNCTION anonymize(name varchar(100))
RETURNS varchar(4)
AS $$
BEGIN
    IF name IS NOT NULL THEN
        RETURN CONCAT(SUBSTRING(name,1,1), '***');
    ELSE
        RETURN NULL;
    END IF;
END;
$$
LANGUAGE plpgsql;

Beispielverwendung

SELECT anonymize(emp_email)
FROM emp_employee;

Gibt eine Liste aller Mitarbeiter zurück, wobei die Email nur den ersten Buchstaben und *** enthält

Quelle

Trigger

Beispiel

CREATE TRIGGER employee_beforeinsert
BEFORE INSERT ON emp_employee
FOR EACH ROW
BEGIN
-- … Methodenrumpf
END

Datenbankseitige Logik

Vorteile

Nachteile

In der Praxis werden Stored Procedures, Funktionen und Trigger daher wenig verwendet

Transaktionen

Beispiel

UPDATE konto
SET kontostand = kontostand – 400
WHERE kontonr = 4;
UPDATE konto
SET kontostand = kontostand + 400
WHERE kontonr = 7;

Beispiel

UPDATE konto
SET kontostand = kontostand – 400
WHERE kontonr = 4;
UPDATE konto
SET kontostand = kontostand + 400
WHERE kontonr = 7;
COMMIT;

Beide Befehle werden erst beim Aufruf von COMMIT auf der Datenbank ausgeführt

Mit ROLLBACK wird die Transaktion abgebrochen.

Transaktionen in DBeaver

Um Transaktionen in DBeaver zu verwenden muss der Transaktionsmodus auf “Manual Commit (Repeatable read)” gestellt werden.

ACID

Bei der Ausführung von Transaktionen werden die ACID Eigenschaften erfüllt

Atomicity (Atomar): Transaktion wird entweder komplett oder gar nicht durchgeführt

Consistency (Konsistenz): Nach einer Transaktion befinden sich alle Daten in einem konsistenten Zustand

Isolation (Isolation): Gleichzeitig ausgeführte Transaktionen beeinflussen sich nicht gegenseitig

Durability (Dauerhaft): Änderungen von Transaktionen verbleiben dauerhaft in der Datenbank

Views

Syntax

CREATE VIEW <Viewname> AS <Selectabfrage>;

Beispiel

CREATE VIEW employee_projects AS
SELECT * FROM emp_employee
LEFT OUTER JOIN pro_project
ON emp_pro_id = pro_id;

Verwendung

SELECT * FROM employee_projects;

Skripte

CREATE ROLE sales;
DROP ROLE sales;

CREATE ROLE emmy WITH INHERIT LOGIN PASSWORD '1234' IN ROLE sales;

-- login with emmy

GRANT ALL ON ALL TABLES IN SCHEMA company TO sales;

DROP ROLE emmy;


-- stored procedure

DROP PROCEDURE IF EXISTS insert_data;
CREATE PROCEDURE insert_data(
    IN col_name varchar,
    IN pro_name varchar
)
LANGUAGE SQL
AS $$
INSERT INTO fao_favorite_color(fao_color_name) VALUES (col_name);
INSERT INTO pro_project(pro_name) VALUES (pro_name);
$$;

CALL insert_data('pink', 'candy_shop');


CREATE FUNCTION anonymize(name varchar(100))
RETURNS varchar(4)
AS $$
BEGIN
    IF name IS NOT NULL THEN
        RETURN CONCAT(SUBSTRING(name,1,1), '***');
    ELSE
        RETURN NULL;
    END IF;
END;
$$
LANGUAGE plpgsql;

SELECT anonymize(emp_email)
FROM emp_employee;


CREATE VIEW employee_projects AS
SELECT * FROM emp_employee
LEFT OUTER JOIN pro_project
ON emp_pro_id = pro_id;

SELECT * FROM employee_projects;