---
tags: vaje, opb, varnost
hackmd: https://hackmd.io/7h7HuYytQFiKKFNWopgrJA
---
# Osnove podatkovnih baz - vaje 16.4.2020

---

## 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 ON objekt TO uporabnik [WITH GRANT OPTION];

REVOKE [GRANT OPTION FOR] pravica ON objekt FROM uporabnik;

Nastvitev baze za projekt:

GRANT ALL ON DATABASE sem2020_janos TO andrazp;
GRANT ALL ON SCHEMA public TO andrazp;
GRANT ALL ON ALL TABLES IN SCHEMA public TO andrazp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO andrazp;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO javnost;
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(*) AS st_ljudi
FROM oseba NATURAL JOIN kraj
GROUP BY posta, kraj;

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 racun.lastnik AS emso,
    max(transakcija.znesek) AS max_prejemek,
    min(transakcija.znesek) AS max_izdatek
   FROM racun
     JOIN transakcija ON racun.stevilka = transakcija.racun
  GROUP BY racun.lastnik;

GRANT SELECT ON mesecni_promet TO andrazp 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, COALESCE(ulica||', ')||posta||' '||kraj AS naslov
   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 `SELECT`ati res samo člani skupine.

   GRANT janos_mktg TO andrazp;
   GRANT janos_mktg TO barbaral;
   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`](http://www.postgresql.org/docs/current/static/rules-update.html) 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;

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.

    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`?

    CREATE TABLE antispam (
      ime TEXT,
      priimek TEXT,
      PRIMARY KEY (ime, priimek)
    );
    
    GRANT SELECT, UPDATE ON antispam TO andrazp;

* Lastnik naj popravi definicijo pogleda `spam`.

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

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

    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 andrazp;
GRANT REFERENCES (emso) ON oseba TO andrazp;

-- študent
CREATE TABLE anketa (
  emso TEXT REFERENCES oseba(emso),
  id_vprasanja INTEGER,
  odgovor TEXT NOT NULL,
  PRIMARY KEY (emso, id_vprasanja)
);
GRANT SELECT ON anketa TO andrazp;