Theme development
Iro zano rinoshandiswa rinosanganisira kushandiswa kweiyo "Business Logic muDatabase", iyo yakatsanangurwa mune zvishoma zvakadzama pano -
Chikamu che theoretical chinotsanangurwa zvakanaka muzvinyorwa
Hapana chitsva muchinyorwa, hapana chakavanzika chirevo kana ruzivo rwakavanzika. Ingori dhizaini nezvekuita kwekuita kweiyo theoretical pfungwa. Kana paine munhu anofarira, verenga. Kana usiri kufarira, usatambise nguva yako.
Kugadzirwa kwedambudziko
Izvo zvinodikanwa kurambidza kupinda kwekuona / kuisa / kugadzirisa / kudzima gwaro zvinoenderana nebasa remushandisi wekushandisa. Basa rinoreva chinopinda mutafura mabasa yakabatana nehukama hwevakawanda-kune-vakawanda netafura vanozvishandisa. Tsanangudzo yekushandiswa kwematafura, nekuda kwehudiki, inosiiwa. Tsanangudzo dzekushandisa dzakanangana nenzvimbo yenyaya dzakasiiwa zvakare.
Kutevedzera
Gadzira mabasa, zvirongwa, matafura
Kugadzira Database Zvinhu
CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
id integer , --id Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
man_id integer , --id ΠΌΠ΅Π½Π΅Π΄ΠΆΠ΅ΡΠ° Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
stat_id integer , --id ΡΡΠ°ΡΡΡΠ° Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
...
is_del BOOLEAN DEFAULT FALSE
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;
Kugadzira mabasa ekushandisa RLS
Kutarisa kugona kuita SELECT mitsara
check_select
CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
result boolean ;
curr_pid integer ;
curr_stat_id integer ;
doc_man_id integer ;
BEGIN
-- DBA ΠΈΠΌΠ΅Π΅Ρ Π΄ΠΎΡΡΡΠΏ ΠΊΠΎ Π²ΡΠ΅ΠΌ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°ΠΌ
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--ΠΡΠ»ΠΈ Π΄ΠΎΠΊΡΠΌΠ΅Π½Ρ ΠΈΠΌΠ΅Π΅Ρ ΠΌΠ΅ΡΠΊΡ 'ΡΠ΄Π°Π»Π΅Π½' - Π½Π΅ ΠΏΠΎΠΊΠ°Π·ΡΠ²Π°ΡΡ Π² Π²ΡΠ±ΠΎΡΠΊΠ΅
SELECT
is_del
INTO
result
FROM
store.docs
WHERE
id = current_id ;
IF result = TRUE
THEN
RETURN FALSE ;
END IF ;
--------------------------------
--ΠΠΎΠ»ΡΡΠΈΡΡ id ΡΠ΅ΠΊΡΡΠ΅Π³ΠΎ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ
SELECT
service_function.get_curr_pid ()
INTO
curr_pid ;
--------------------------------
--ΠΠΎΠ»ΡΡΠΈΡΡ id ΠΌΠ΅Π½Π΅Π΄ΠΆΠ΅ΡΠ° Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
SELECT
man_id
INTO
doc_man_id
FROM
store.docs
WHERE
id = current_id ;
--------------------------------
--ΠΡΠ»ΠΈ ΠΌΠ΅Π½Π΅Π΄ΠΆΠ΅Ρ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ° Π½Π΅ ΡΠ΅ΠΊΡΡΠΈΠΉ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ ΠΈΠ»ΠΈ ΠΌΠ΅Π½Π΅Π΄ΠΆΠ΅Ρ Π½Π΅ Π½Π°Π·Π½Π°ΡΠ΅Π½
--Π΄ΠΎΠ±Π°Π²ΠΈΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½Ρ Π² Π²ΡΠ±ΠΎΡΠΊΡ
IF doc_man_id != curr_pid OR doc_man_id IS NULL
THEN
RETURN TRUE ;
ELSE
--ΠΠΎΠ»ΡΡΠΈΡΡ ΡΠ΅ΠΊΡΡΠΈΠΉ ΡΡΠ°ΡΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;
--ΠΡΠ»ΠΈ ΡΡΠ°ΡΡΡ ΠΏΠΎΠ·Π²ΠΎΠ»ΡΠ΅Ρ ΠΏΡΠΎΡΠΌΠΎΡΡΠ΅ΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½Ρ - Π΄ΠΎΠ±Π°Π²ΠΈΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½Ρ Π² Π²ΡΠ±ΠΎΡΠΊΡ
IF curr_statid = 4 OR curr_statid = 9
THEN
RETURN TRUE ;
ELSE
--ΠΠ½Π°ΡΠ΅ - ΠΈΡΠΊΠ»ΡΡΠΈΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½Ρ ΠΈΠ· Π²ΡΠ±ΠΎΡΠΊΠΈ
RETURN FALSE ;
END IF ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE ) TO service_functions;
Kutarisa kugona KUPINDA mitsara
check_insert
CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
curr_role_id integer ;
BEGIN
--DBA ΠΌΠΎΠΆΠ΅Ρ Π΄ΠΎΠ±Π°Π²Π»ΡΡΡ ΡΡΡΠΎΠΊΡ Π² Π»ΡΠ±ΠΎΠΌ ΡΠ»ΡΡΠ°Π΅
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--ΠΠΎΠ»ΡΡΠΈΡΡ id ΡΠΎΠ»ΠΈ ΡΠ΅ΠΊΡΡΠ΅Π³ΠΎ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------
--ΠΡΠ»ΠΈ ΡΠΎΠ»Ρ Π΄ΠΎΠΏΡΡΠΊΠ°Π΅Ρ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΡ ΡΠΎΠ·Π΄Π°Π½ΠΈΡ Π½ΠΎΠ²ΠΎΠ³ΠΎ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
--ΡΠ°Π·ΡΠ΅ΡΠΈΡΡ
IF curr_role_id = 3 OR curr_role_id = 5
THEN
RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE ) TO service_functions;
Kutarisa kugona kuDELETE mutsara
check_delete
CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN
--Π’ΠΎΠ»ΡΠΊΠΎ DBA ΠΌΠΎΠΆΠ΅Ρ ΡΠ΄Π°Π»ΡΡΡ ΡΡΡΠΎΠΊΡ
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LANGUAGE plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE ) FROM public;
Kutarisa kugona kwe UPDATE mutsara.
update_using
CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN
--ΠΠΎΠΊΡΠΌΠ΅Π½ΡΡ ΠΈΠΌΠ΅ΡΡΠΈΠ΅ ΡΡΠ°ΡΡΡ 'ΡΠ΄Π°Π»Π΅Π½' - Π½Π΅ ΡΠ΅Π΄Π°ΠΊΡΠΈΡΡΡΡΡΡ
IF is_del
THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_using( store.docs.id%TYPE , boolean ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE , boolean ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_using( store.docs.id%TYPE ) TO service_functions;
update_check
CREATE OR REPLACE FUNCTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
current_rid integer ;
current_statid integer ;
BEGIN
--DBA ΠΌΠΎΠΆΠ΅Ρ ΠΏΡΠΎΡΠΌΠ°ΡΡΠΈΠ²Π°ΡΡ ΡΡΡΠΎΠΊΡ
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--ΠΠΎΠ»ΡΡΠΈΡΡ id ΡΠΎΠ»ΠΈ ΡΠ΅ΠΊΡΡΠ΅Π³ΠΎ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------
--Π£Π΄Π°Π»Π΅Π½ΠΈΠ΅ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ° - ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠ΅ ΠΏΡΠΈΠ·Π½Π°ΠΊΠ°
IF is_deleted
THEN
--ΠΡΠ»ΠΈ ΡΠΎΠ»Ρ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ ***
IF current_role_id = 3
THEN
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;
--ΠΠΎΠΊΡΠΌΠ΅Π½Ρ Π² ΡΡΠ°ΡΡΡΠ΅ *** Π½Π΅Π»ΡΠ·Ρ ΡΠ΄Π°Π»ΠΈΡΡ
IF current_status_id = 11
THEN
RETURN FALSE ;
ELSE
--ΠΠΎΠΆΠ½ΠΎ ΡΠ΄Π°Π»ΠΈΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½Ρ Π² Π΄ΡΡΠ³ΠΈΡ
ΡΡΠ°ΡΡΡΠ°Ρ
RETURN TRUE ;
END IF ;
--ΠΠ½Π°ΡΠ΅ , Π΅ΡΠ»ΠΈ ΡΠΎΠ»Ρ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ ***
ELSIF current_role_id = 5
THEN
--ΠΡΠ΅ ΡΡΠ°ΡΡΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ°
RETURN TRUE ;
ELSE
--ΠΡΡΠ³ΠΈΠ΅ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»ΠΈ Π½Π΅ ΠΌΠΎΠ³ΡΡ ΡΠ΄Π°Π»ΡΡΡ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΡ
RETURN FALSE ;
END IF ;
ELSE
--ΠΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ Π΄ΠΎΠΊΡΠΌΠ΅Π½ΡΠ° ΡΠ°Π·ΡΠ΅ΡΠ΅Π½ΠΎ
RETURN TRUE ;
END IF ;
RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_with_check( storg.docs.id%TYPE , boolean ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.update_with_check( storg.docs.id%TYPE , boolean ) FROM public;
GRANT EXECUTE ON FUNCTION store.update_with_check( store.docs.id%TYPE ) TO service_functions;
Gonesa iyo Row Level Secutiry policy yetafura.
Bvisa ROW LEVEL SECURITY
ALTER TABLE store.docs ENABLE ROW LEVEL SECURITY ;
CREATE POLICY doc_select ON store.docs FOR SELECT TO service_functions USING ( (SELECT store.check_select(id)) );
CREATE POLICY doc_insert ON store.docs FOR INSERT TO service_functions WITH CHECK ( (SELECT store.check_insert(id)) );
CREATE POLICY docs_delete ON store.docs FOR DELETE TO service_functions USING ( (SELECT store.check_delete(id)) );
CREATE POLICY doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.update_using(id , is_del )) );
CREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );
Mugumisiro
Inoshanda.
Iro zano rakarongwa rakaita kuti zvikwanise kuendesa kushandiswa kweiyo muenzaniso kubva padanho rebasa rebhizinesi kusvika padanho rekuchengetedza data.
Iwo mabasa anogona kushandiswa se template kuita mamwe emhando dzekuvanza data kana bhizinesi richida.
Source: www.habr.com