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;
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;
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;
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?
DO INSTEADMarketinški oddelek v tvoji banki želi pošiljati reklame komitentom, vendar jim ne smemo dovoliti vpogleda v vse zasebne informacije o strankah.
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;
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 andrazp;
GRANT janos_mktg TO barbaral;
GRANT SELECT ON spam TO janos_mktg;
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.
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;
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.
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:
GRANT CREATE ON SCHEMA public TO janos_mktg;
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;
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
);
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.
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;