---
tags: vaje, opb, varnost
hackmd: https://hackmd.io/Qi597_qqR4KKWu8-pVsUNw
---
# Osnove podatkovnih baz - vaje 15.4.2021

---

## 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 | ...] ime
  TO <PUBLIC | uporabnik [WITH GRANT OPTION]>;

GRANT skupina TO uporabnik;

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

REVOKE skupina FROM uporabnik;

Nastavitev baze za projekt:

-- na začetku
GRANT ALL ON DATABASE sem2021_janos TO timotejv WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO timotejv WITH GRANT OPTION;
GRANT CONNECT ON DATABASE sem2021_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 timotej 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 timotej WITH GRANT OPTION;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO javnost WITH GRANT OPTION;

-- dodatne pravice za aplikacijo
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 timotejv 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 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 timotejv;
   GRANT janos_mktg TO anamarijab;
   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;
   
   -- poskus spremembe
   UPDATE spam SET priimek = 'Naša' WHERE ime = '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.

      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 timotejv;

* Lastnik naj popravi definicijo pogleda `spam`.

      CREATE OR REPLACE VIEW spam AS
      SELECT ime, priimek, 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 timotejv;
GRANT REFERENCES (emso) ON oseba TO timotejv;

-- študent
CREATE TABLE anketa (
  emso TEXT REFERENCES oseba(emso),
  id_vprasanja INTEGER,
  odgovor TEXT,
  PRIMARY KEY (emso, id_vprasanja)
);