changed 3 years ago  
Linked with GitHub jaanos/OPB/zapiski/2022/2022-04-14.md

Osnove podatkovnih baz - vaje 14.4.2022

Varnost in kontrola dostopa

Delali bomo z bazo banka, ki smo jo spoznali na prvih vajah, oziroma vsak s svojo (in sošolčevo) kopijo.

GRANT <pravica | ALL> ON [TABLE | SEQUENCE | DATABASE | ... ] objekt
  TO <PUBLIC | uporabnik [WITH GRANT OPTION]>;

GRANT skupina TO uporabnik;

REVOKE [GRANT OPTION FOR] <pravica | ALL>
  ON [TABLE | SEQUENCE | DATABASE | ... ] objekt
  FROM <PUBLIC | uporabnik>;

REVOKE skupina FROM uporabnik;

Nastavitve baze za projekt:

-- na začetku
GRANT ALL ON DATABASE sem2022_janos TO majg WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO majg WITH GRANT OPTION;
GRANT CONNECT ON DATABASE sem2022_janos TO javnost;
GRANT USAGE ON SCHEMA public TO javnost;

-- po ustvarjanju tabel
GRANT ALL ON ALL TABLES IN SCHEMA public TO janos WITH GRANT OPTION;
GRANT ALL ON ALL TABLES IN SCHEMA public TO majg WITH GRANT OPTION;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO janos WITH GRANT OPTION;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO majg WITH GRANT OPTION;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO javnost;

-- dodatne pravice za uporabo aplikacije
GRANT INSERT ON tabela TO javnost;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO javnost;

Naloga 1: Pravice javnosti

Ustvari pogled stats(posta, kraj, st_ljudi), ki za vsako ime kraja pove, koliko ljudi iz tega kraja je komitentov banke. Takšna informacija je dobra za promocijo v javnosti, zato naj bo pogled za branje dostopen vsem. Poglej v bazo kakšnega od sošolcev in preveri, če res lahko bereš njihov pogled stats. (In jih zafrkavaj, če ga ne moreš.)

CREATE VIEW stats AS
SELECT posta, kraj, count(emso) AS st_ljudi
FROM kraj NATURAL LEFT JOIN oseba
GROUP BY posta;

GRANT SELECT ON stats TO PUBLIC;

Naloga 2: Pravice posameznih uporabnikov

Tvojo banko kontaktirajo iz zavarovalnice, ker jih zanima likvidnost njihovih strank. Informacije o transakcijah so zasebna stvar, lahko pa zavarovalničarjem ustvarimo pogled mesecni_promet(emso, max_prejemek, max_izdatek). Ta za vsako osebo podaja višino največjega prejemka in največjega izdatka na vseh njegovih računih. Zavarovalnici se zdi, da je to dovolj dobra ocena povprečnega mesečnega prometa.

Vlogo zavarovalničarja naj odigra tvoj sošolec. Dostop do pogleda mesecni_promet dovoli samo njemu. Prepričajta se, če res lahko dostopa do informacij in če drugi ne morejo.

Daj zavarovalničarju še pravico, da on sam dodeli pravico do pregledovanja pogleda svojim kolegom.

CREATE VIEW mesecni_promet AS
SELECT lastnik AS emso, max(znesek) AS max_prejemek, min(znesek) AS max_izdatek
FROM racun JOIN transakcija ON stevilka = racun
GROUP BY lastnik;

GRANT SELECT ON mesecni_promet TO majg WITH GRANT OPTION;

Dodatno (naredi na koncu vaj, če bo čas)

Zavarovalnica zdaj želi povprečen mesečni promet na računu, t.j., povprečno mesečno vsoto absolutnih vrednosti vseh transakcij v celotnem komitentskem obdobju. Kot komitentsko obdobje štejemo čas od prve opravljene transakcije tiste osebe do tega trenutka. Pogled naj v izogib bančnemu izkoriščanju vsebuje le tiste osebe, za katere je povprečni mesečni promet manjši od 100000.

Zbriši pogled mesecni_promet in naredi novega, popravljenega. Ali sošolec-zavarovalničar še lahko dostopa do njega?

Naloga 3: Skupine uporabnikov, DO INSTEAD

Marketinški oddelek v tvoji banki želi pošiljati reklame komitentom, vendar jim ne smemo dovoliti vpogleda v vse zasebne informacije o strankah.

  1. Ustvari pogled spam(ime, priimek, naslov). Naslov naj bo oblike Jamova 39, 1000 Ljubljana; dobiš ga s stikanjem (operator ||) ustreznih stolpcev iz tabel oseba in kraj.

    ​​​CREATE VIEW spam AS
​​​SELECT ime, priimek, ulica || ', ' || posta || ' ' || kraj
​​​FROM oseba NATURAL JOIN kraj;

  2. Vlogo marketinškega oddelka naj odigrata dva sošolca. V skupino damjanm_mktg (kjer damjanm spet zamenjaš s svojim up. imenom) vključi dva svoja sošolca. Nato skupini dovoli branje iz pogleda spam. Preverite, če smejo iz pogleda SELECTati res samo člani skupine.

    ​​​GRANT janos_mktg TO majg;
​​​GRANT janos_mktg TO jakab;
​​​GRANT SELECT ON spam to janos_mktg;

  3. Marketingarje včasih pokličejo sveže poročene stranke, ki so spremenile priimek. Najbolj praktično bi bilo, če bi lahko spremembo v bazo vnesli kar takoj, brez posredovanja skrbnika baze. Omogoči jim takšne spremembe.

    • dodaj potrebne pravice skupini za marketing
    • dodaj ustrezno pravilo DO INSTEAD pogledu spam.

    Predpostavi, da je oseba enolično določena z imenom in priimkom. V praksi tega ne bi naredili, temveč bi v tabeli oseba vpeljali dodaten ID (različen od EMŠO, zato ni hudega, če marketing te IDje pozna) in ga dodali v pogled spam.

    ​​​GRANT UPDATE ON spam to janos_mktg;
​​​
​​​CREATE RULE spam_update AS
​​​ON UPDATE TO spam DO INSTEAD
​​​UPDATE oseba SET priimek = NEW.priimek
​​​WHERE ime = OLD.ime AND priimek = OLD.priimek;
​​​
​​​-- poskus spremembe
​​​UPDATE spam SET priimek = 'Naša' WHERE ime = 'Nona';
​​​-- spremenilo bi vrstio z ime = 'Nona', priimek = 'Moja'
​​​
​​​-- kar se bo dejansko zgodilo
​​​UPDATE oseba SET priimek = 'Naša'
​​​WHERE ime = 'Nona' AND priimek = 'Nona';

  4. Rešitev s priimki krasno dela in marketing si je nekaj podobnega zaželel tudi za obravnavo strank, ki se preselijo (zamenjajo naslov). Kako bi jim lahko pomagal? Implementacija ni potrebna.

  5. Vse stranke niso ravno navdušene nad veliko količino pošte, ki jim jo pošilja naša banka, zato nekateri zahtevajo, da se jih briše s spam seznama. Smemo to kot lastnik pogleda spam dovoliti? Kako bi pomagali oglaševalcem? Ta scenarij naredite v parih; en je lastnik baze, drugi pa iz marketinga:

    • Lastnik naj dovoli oddelku marketinga, da v njegovi bazi kreira tabele.

      ​​​​​​-- lastnik
​​​​​​GRANT CREATE ON SCHEMA public TO janos_mktg;

    • Marketingar naj si ustvari tabelo antispam, ki bo hranila ljudi, ki nočejo prejemati pošte, in dovoli branje lastniku. Katere stolpce mora imeti antispam?

      ​​​​​​-- marketing
​​​​​​CREATE TABLE antispam (
​​​​​​    ime TEXT,
​​​​​​    priimek TEXT,
​​​​​​    PRIMARY KEY (ime, priimek)
​​​​​​);
​​​​​​
​​​​​​GRANT SELECT, UPDATE ON antispam TO majg;

    • Lastnik naj popravi definicijo pogleda spam.

      ​​​​​​-- lastnik
​​​​​​CREATE OR REPLACE VIEW spam AS
​​​​​​SELECT ime, priimek, ulica || ', ' || posta || ' ' || kraj
​​​​​​FROM oseba NATURAL JOIN kraj
​​​​​​WHERE (ime, priimek) NOT IN (
​​​​​​    SELECT ime, priimek FROM antispam
​​​​​​);

    • Ali tvoja rešitev dobro deluje, ko stranka spremeni priimek? Če ni tako, dodaj ustrezno pravilo DO ALSO.

    ​​​​​-- lastnik
​​​​​CREATE RULE antispam_update AS
​​​​​ON UPDATE TO oseba DO ALSO
​​​​​UPDATE antispam
​​​​​SET ime = NEW.ime, priimek = NEW.priimek
​​​​​WHERE ime = OLD.ime AND priimek = OLD.priimek;

Opomba: DO ALSO/INSTEAD je Postgresova razširitev standarda. Omejitev, da pogledi ne omogočajo avtomatično operacij DELETE in UPDATE, pa je tudi Postgresova posebnost in je v neskladju s standardom.

Naloga 4: Pravica sklicevanja (REFERENCES)

Banka je zaposlila študente, da bodo zanjo med strankami izvajali anketo. Študentje seveda ne smejo izvedeti ničesar o strankah, niti tega, kdo stranke sploh so. Od vsakega anketiranca dobijo samo njegovo številko stranke (v našem primeru EMŠO, lahko pa bi imeli kakšen manj informativen interni ID) ter odgovore na vprašanja. Odgovore hranijo v tabeli anketa(emso, id_vprasanja, odgovor).

Dodaj anketarju (sošolcu) dovolj pravic, da bo lahko naredil tabelo in vanjo vstavljal odgovore strank, ne pa tudi dobil seznam strank.

-- banka
GRANT CREATE ON SCHEMA public TO majg;
GRANT REFERENCES (emso) ON oseba TO majg;

-- študent
CREATE TABLE anketa (
    emso TEXT REFERENCES oseba (emso),
    id_vprasanja INTEGER,
    odgovor TEXT,
    PRIMARY KEY (emso, id_vprasanja)
);
GRANT SELECT ON anketa TO majg;
INSERT INTO anketa VALUES ('0802986500666', 1, 'Zelo dobro!');
Select a repo