Pangembangan tema
Strategi sing digunakake kalebu nggunakake konsep "Logika Bisnis ing Database", sing diterangake kanthi rinci ing kene -
Bagean teoretis diterangake kanthi apik ing dokumentasi
Ora ana sing anyar ing artikel kasebut, ora ana makna sing didhelikake utawa kawruh rahasia. Mung sketsa babagan implementasi praktis saka ide teoretis. Yen ana sing kasengsem, maca. Yen sampeyan ora kasengsem, aja mbuwang wektu.
Formulasi masalah
Sampeyan kudu matesi akses kanggo ndeleng / nglebokake / ngowahi / mbusak dokumen miturut peran pangguna aplikasi. Peran nuduhake entri ing tabel peran digandhengake karo hubungan akeh-kanggo-akeh karo tabel kedhaftar. Rincian implementasine tabel, amarga ora pati penting, diilangi. Rincian implementasi khusus sing ana gandhengane karo area subyek uga diilangi.
Π Π΅Π°Π»ΠΈΠ·Π°ΡΠΈΡ
Nggawe peran, skema, tabel
Nggawe Obyek Database
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 ;
Nggawe fungsi kanggo ngleksanakake RLS
Priksa kemampuan kanggo nindakake baris PILIH
mriksa_pilih
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;
Priksa kemampuan kanggo INSERT baris
mriksa_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;
Priksa kemampuan kanggo Mbusak baris
mriksa_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;
Priksa kemampuan kanggo UPDATE baris.
nganyari_nggunakake
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;
nganyari_priksa
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;
Aktifake kabijakan Keamanan Tingkat Row kanggo tabel.
AKTIFKAN KEAMANAN TINGKAT ROW
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 )) );
Asile
Kerjane.
Strategi sing diusulake bisa nransfer implementasine model peran saka level fungsi bisnis menyang level panyimpenan data.
Fungsi kasebut bisa digunakake minangka cithakan kanggo ngetrapake model ndhelikake data sing luwih canggih yen syarat bisnis mbutuhake.
Source: www.habr.com