Pengembangan tema
Strategi yang digunakan melibatkan penggunaan konsep βLogika Bisnis dalam Databaseβ, yang dijelaskan lebih detail di sini -
Bagian teoretis dijelaskan dengan baik dalam dokumentasi
Tidak ada yang baru dalam artikel tersebut, tidak ada makna tersembunyi atau pengetahuan rahasia. Sekadar sketsa tentang implementasi praktis dari sebuah ide teoretis. Jika ada yang tertarik, bacalah. Jika Anda tidak tertarik, jangan buang waktu Anda.
Pernyataan masalah
Perlu adanya pembatasan akses untuk melihat/memasukkan/memodifikasi/menghapus suatu dokumen sesuai dengan peran pengguna aplikasi. Peran mengacu pada entri dalam tabel peran dikaitkan dengan hubungan banyak ke banyak dengan sebuah tabel Pengguna. Detail implementasi tabel, karena sepele, dihilangkan. Rincian implementasi spesifik yang berkaitan dengan bidang studi juga dihilangkan.
Implementasi
Buat peran, skema, tabel
Membuat Objek Basis Data
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 ;
Membuat fungsi untuk mengimplementasikan RLS
Memeriksa kemampuan untuk melakukan baris SELECT
periksa_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;
Memeriksa kemampuan untuk INSERT baris
periksa_masukkan
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;
Memeriksa kemampuan untuk MENGHAPUS satu baris
periksa_hapus
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;
Memeriksa kemampuan untuk MEMPERBARUI baris.
perbarui_menggunakan
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;
perbarui_periksa
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;
Aktifkan kebijakan Keamanan Tingkat Baris untuk tabel.
AKTIFKAN KEAMANAN TINGKAT BARIS
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 )) );
Total
Berhasil.
Strategi yang diusulkan memungkinkan untuk mentransfer penerapan model peran dari tingkat fungsi bisnis ke tingkat penyimpanan data.
Fungsi-fungsi tersebut dapat digunakan sebagai templat untuk mengimplementasikan model penyembunyian data yang lebih canggih jika kebutuhan bisnis memerlukannya.
Sumber: www.habr.com