Sviluppo del tema
La strategia utilizzata prevede l'uso del concetto di "Business Logic nel Database", che Γ¨ stato descritto un po' piΓΉ in dettaglio qui -
La parte teorica Γ¨ ben descritta nella documentazione
Non c'Γ¨ nulla di nuovo nell'articolo, non c'Γ¨ significato nascosto o conoscenza segreta. Solo uno schizzo sull'implementazione pratica di un'idea teorica. Se qualcuno Γ¨ interessato, lo legga. Se non sei interessato, non perdere tempo.
Formulazione del problema
Γ necessario limitare l'accesso alla visualizzazione/inserimento/modifica/eliminazione di un documento in base al ruolo dell'utente dell'applicazione. Il ruolo si riferisce a una voce in una tabella ruoli associato a una relazione molti-a-molti con una tabella utenti. I dettagli relativi all'implementazione delle tabelle, per banalitΓ , vengono omessi. Vengono inoltre omessi dettagli implementativi specifici relativi all'area in oggetto.
implementazione
Crea ruoli, schemi, tabelle
Creazione di oggetti di 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 ;
Creazione di funzioni per implementare RLS
Verifica della capacitΓ di eseguire righe SELECT
controlla_seleziona
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;
Verifica della capacitΓ di INSERIRE righe
controlla_inserisci
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;
Verifica della possibilitΓ di ELIMINARE una riga
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;
Verifica della possibilitΓ di AGGIORNARE una riga.
aggiorna_usando
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;
aggiornamento_controllo
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;
Abilita la policy di sicurezza a livello di riga per una tabella.
ABILITA LA SICUREZZA A LIVELLO DI RIGA
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 )) );
risultato
Funziona
La strategia proposta ha consentito di trasferire l'implementazione del modello di ruolo dal livello delle funzioni aziendali al livello di archiviazione dei dati.
Le funzioni possono essere utilizzate come modello per implementare modelli piΓΉ sofisticati di occultamento dei dati se i requisiti aziendali lo richiedono.
Fonte: habr.com