Audit trigger PL/pgSQL

priklady

Audit trigger PL/pgSQL

Ide o trigger, ktorý zaznamenáva všetky zmeny (INSERT, DELETE, UPDATE) v tabuľke výplat. Pokiaľ potrebujeme vedieť kto, čo a kedy upravil, či pridal do tabuľky, použijeme trigger, ktorý bude zapisovať každú akciu vykonanú v tabuľke. Od Postgres verzie 9.3, môžeme auditovať aj DDL(Data definition language) zmeny v databáze použitím event triggeru. V tomto konkrétnom príklade budeme zapisovať aktivitu spojenú s Data Manipulation Language (DML).

Dostupné online:
REXtester

Štruktúra DB - Diagram

audit_trigger_diagram

Vytvorenie DB a tabuliek

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE vyplaty (
  meno_zamestnanca TEXT PRIMARY KEY,
  plat   INTEGER NOT NULL
);
CREATE TABLE vyplaty_zmeny_log (
  pozmenene_uzivatelom TEXT      DEFAULT CURRENT_USER,
  datum_zmeny TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  druh_operacie  TEXT,
  meno_zamestnanca   TEXT,
  stary_plat INTEGER,
  novy_plat INTEGER
);

Prístupové práva

Obyčajne nechceme aby užívatelia mohli meniť tabuľku s logmi. Toto privilégium bude mať iba manažment.

  • tabuľka s logmi nebude verejná
  • manažéri spoločnosti máju prístup k tabuľke
    1
    2
    REVOKE ALL ON vyplaty_zmeny_log FROM PUBLIC;
    GRANT ALL ON vyplaty_zmeny_log TO manazery;

Funkcia

Funkcia spustená triggerom, ktorá sa stará o zápis údajov do tabuľky zmien pri udalosti:

  • INSERT
  • UPDATE
  • DELETE
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE OR REPLACE FUNCTION log_vyplaty_zmeny()
      RETURNS TRIGGER AS $$
    BEGIN
      IF TG_OP = 'INSERT'
      THEN
        INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, novy_plat)
        VALUES (TG_OP, NEW.meno_zamestnanca, NEW.plat);
      ELSIF TG_OP = 'UPDATE'
        THEN
          INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, stary_plat, novy_plat)
          VALUES (TG_OP, NEW.meno_zamestnanca, OLD.plat, NEW.plat);
      ELSIF TG_OP = 'DELETE'
        THEN
          INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, stary_plat)
          VALUES (TG_OP, NEW.meno_zamestnanca, OLD.plat);
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;

Trigger

Trigger, ktorý zavolá funkciu po každej zmene, či pridaní záznamu do tabuľky výplaty:

1
2
3
CREATE TRIGGER audit_vyplaty_zmena
AFTER INSERT OR UPDATE OR DELETE ON vyplaty
FOR EACH ROW EXECUTE PROCEDURE log_vyplaty_zmeny();

Test

Pridanie výplaty zamestnanca:

1
INSERT INTO vyplaty VALUES ('Rob', 1000);

Zmena výplaty zamestnanca:

1
2
3
UPDATE vyplaty
SET plat = 1100
WHERE meno_zamestnanca = 'Rob';

1
2
3
INSERT INTO vyplaty VALUES ('Maria', 1000);
UPDATE vyplaty
SET plat = plat + 200;

Výpis tabuľky výplat:

1
2
SELECT *
FROM vyplaty;

Každá zmena vykonaná v tabuľke výplaty, bude uložená do loggovacej tabuľky na účely auditu.
Výpis zmien

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT *
FROM vyplaty_zmeny_log;
-[ RECORD 1 ]--------------------------
pozmenene_uzivatelom | Postgres
datum_zmeny          | 2017-03-15 18:06:21
druh_operacie        | INSERT
meno_zamestnanca     | Rob
stary_plat           |
novy_plat            | 1000
-[ RECORD 2 ]--------------------------
pozmenene_uzivatelom | Postgres
datum_zmeny          | 2017-03-15 18:06:22
druh_operacie        | UPDATE
meno_zamestnanca     | Rob
stary_plat           | 1000
novy_plat            | 1100
-[ RECORD 3 ]--------------------------
pozmenene_uzivatelom | Postgres
datum_zmeny          | 2017-03-15 18:06:24
druh_operacie        | INSERT
meno_zamestnanca     | Maria
stary_plat           |
novy_plat            | 1000
-[ RECORD 4 ]--------------------------
pozmenene_uzivatelom | Postgres
datum_zmeny          | 2017-03-15 18:06:27
druh_operacie        | UPDATE
meno_zamestnanca     | Rob
stary_plat           | 1100
novy_plat            | 1300
-[ RECORD 5 ]--------------------------
pozmenene_uzivatelom | Postgres
datum_zmeny          | 2017-03-15 18:06:29
druh_operacie        | UPDATE
meno_zamestnanca     | Maria
stary_plat           | 1000
novy_plat            | 1200