Archive for 28. märts 2019

MS SQL: viewd on teie sõbrad…

märts 28, 2019

Arusaamatul põhjusel inimesed kardavad view’sid. Võin öelda, et olles MS SQL andmebaasiga sõber alates 2000 aastast juba, ütlen view’d on teie sõbrad. Toon ühe lihtsa näite, mida mina kasutanud. Viewd sai mitu probleemi lahendatud:

  1. Kasutajad ei pääse otse ühelegi tabelile ligi, kõik toimub läbi view’de. Õigused omistate vaid viewle !

  2. Viewdes saab where lauses piirata kirjete nähtavust õigustega. MSSQL lubab viewd kasutada kui “tabelit” kui view genereeritud stiilis (ei tohi olla select osas teiste tabelite välju !) SELECT minutabel.* FROM minutabel JOIN Osakonnad  o ON o.id = minutabel.osakondid AND o.tootajalogin = suser_name()

  3. Viewdes saab kuvada vaid nö live kirjeid, üheski natukene tõsisemas süsteemis reaalselt kirjeid ei kustutata. Põhjus lihtne, kui mingi probleem, saab läbi kustutatud kirjete näha, mida on korda saadetud. Stiilis … WHERE kustutatud = 0

Koheselt tuleb saalis köhatus, aga mis siis juhtub, kui lähtetabelisse tekivad uued väljad, view ju neid ei kuva. Ärge muretsege: lihtsalt üks väike lisaliigutus, ei pea VIEW’d droppima ja uuesti looma. Viimase tegevusega kaoks ka kõik õigused.

Vaid üks käsk: sp_refreshview supervaade_vw

Panen siia ka koodinäite:

CREATE TABLE tmp_kasutaja
(
id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
eesnimi VARCHAR(35),
pereknimi VARCHAR(35),
lisajaid INTEGER,
lisatudkp DATETIME,
muutjaid INTEGER,
muudetudkp DATETIME,
kustutajaid INTEGER,
kustutatudkp DATETIME,
kustutatud BIT
)

 

CREATE VIEW tmp_kasutaja_vw
AS
SELECT * FROM tmp_kasutaja WHERE kustutatud = 0

Kuna kustutamine pole kunagi hea mõte, sest tihti kasutajatel tekib kollektiivne mäluauk ja alati infosüsteemid süüdi. Siis sellised kirjete logid stiilis, et kustutatud kirje reaalselt ei kustu ja muutja ning lisaja salvestatakse, see tegevus parandab tihti kollektiivset mäluauku.  Nii mõnelegi kasutajale järsku tuleb meelde, jaaa ma vist kustutasin midagi.  Teine variant teha tervilik auditlogi, kus igasugust muutmist logitakse, aga tihti ka sellisest “lihtsast” auditlogist täiesti piisab (kes muutis, kes lisas, kes kustutas).

Selleks teeme ühe toreda INSTED OF TRIGGERI

CREATE TRIGGER tmp_kasutaja_upt_del_trg ON dbo.tmp_kasutaja INSTEAD OF DELETE
AS
BEGIN

DECLARE @tootajaid INT;

SET NOCOUNT ON;
SELECT @tootajaid = id
FROM Tootaja
WHERE login = SUSER_NAME()

UPDATE t
SET kustutatud=1, kustutajaid=@tootajaid, kustutatudkp=GETDATE()
FROM tmp_kasutaja t
INNER JOIN deleted d ON
t.id = d.id

END

 

CREATE TRIGGER tmp_kasutaja_upt_trg on tmp_kasutaja
FOR UPDATE, INSERT
AS
BEGIN
DECLARE @tootajaid INT;

SET NOCOUNT ON;
SELECT @tootajaid = id
FROM Tootaja
WHERE login = SUSER_NAME()

IF EXISTS(SELECT * FROM DELETED)
BEGIN
IF UPDATE(kustutatud) return
UPDATE t
SET muutjaid=@tootajaid, muudetudkp=GETDATE()
FROM tmp_kasutaja t
INNER JOIN DELETED d ON
d.id = t.id
END ELSE
BEGIN
UPDATE t
SET lisajaid=@tootajaid, lisatudkp=GETDATE()
FROM tmp_kasutaja t
INNER JOIN INSERTED i ON
i.id = t.id
END
END

 

Advertisements

PostgreSQL kontrollige alati public rolli õigusi

märts 7, 2019

Nooremprogrammeerijatele ei tohi kunagi anda admin õigusi, selle tõestuseks oli üks firma, kus avastasin, et GRANT ALL oli publicule antud. Siis kui küsisid, et miks te nii tegite, aga Googles oli mingi rida selline. Peale seda kahjuks pidin 10 minutit pead vastu lauda taguma ja mõtlema, mida koolides õpetatakse. Normaalne õiguste haldus käib nii, et tehakse Group role’d ja õigusi juhitakse läbi selle rolli.

Esmalt tehke enda baasis päring, vaadake üle, mis publicul lubatud

SELECT grantee, privilege_type ,table_name
FROM information_schema.role_table_grants
WHERE grantee = ‘PUBLIC’ and table_name NOT LIKE ‘pg_%’

Ja sequence õigused saate järgneva päringuga:

SELECT cl.oid, relname, pg_get_userbyid(relowner) AS seqowner, relacl, description,
(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=cl.oid) AS labels,
(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=cl.oid) AS providers
FROM pg_class cl
LEFT OUTER JOIN pg_description des ON (des.objoid=cl.oid AND des.classoid=’pg_class’::regclass)
WHERE relkind = ‘S’
ORDER BY relname

Mina antud juhul eemaldasin publicult õigusi ükshaaval, et saaks vaadata kas mõni noorem programmeerijate süsteem kukub kokku või mitte:

REVOKE TRUNCATE ON ALL TABLES IN SCHEMA public FROM public;
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM public;
REVOKE UPDATE ON ALL TABLES IN SCHEMA public FROM public;
REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM public;
REVOKE REFERENCES ON ALL TABLES IN SCHEMA public FROM public;
REVOKE TRIGGER ON ALL TABLES IN SCHEMA public FROM public;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM public;

Normaalses maailma tehakse nö Group role töötajatele

CREATE ROLE tootajate_roll NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

https://www.postgresql.org/docs/9.1/sql-grant.html

Aga siin peate ise otsustama, mis tegevusi töötaja roll tegelikkuses tohiks teha ja võimalik,
osade tabelite õigusi pärast eraldi muuta rollis. See lihtsalt näide

GRANT INSERT,SELECT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO tootajate_roll;