---
tags: vaje, opb, varnost
hackmd: https://hackmd.io/Ac6ySjr0ThmTi3RkxHJ3LA
---
# 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 `SELECT`ati 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`](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';
   -- 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!');