Sviluppo del tema e per una risposta dettagliata con
La strategia utilizzata implica l'uso del concetto di "Logica aziendale nel DB", descritto in modo più dettagliato qui —
La parte teorica è ben descritta nella documentazione — . Di seguito viene esaminata l'implementazione pratica di un problema aziendale specifico — modello di accesso ai dati.
Nell'articolo non c'è nulla di nuovo, nessun significato nascosto e conoscenze segrete. Solo un abbozzo su un'implementazione pratica di un'idea teorica. Se a qualcuno interessa — leggete. A chi non interessa — non sprecate il vostro tempo.
Definizione del compito
È necessario delimitare l'accesso alla visualizzazione/inserimento/modifica/cancellazione del documento in base al ruolo dell'utente dell'applicazione. Per ruolo si intende una voce nella tabella roles collegata da una relazione molti-a-molti con la tabella users. I dettagli dell'implementazione delle tabelle sono stati omessi per ovvietà. Sono stati omessi anche dettagli specifici dell'implementazione legati al dominio.
Implementazione
Creiamo ruoli, schemi, tabella
Creazione di oggetti DB
CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
id integer , --id del documento
man_id integer , --id del manager del documento
stat_id integer , --id dello stato del documento
...
is_del BOOLEAN DEFAULT FALSE
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;
Creiamo funzioni per implementare RLS
Verifica della possibilità di eseguire SELECT su una riga
check_select
CREA O SOSTITUISCI LA FUNZIONE store.check_select ( current_id store.docs.id%TYPE ) RESTITUISCE boolean AS $$
DECLARE
risultato boolean ;
curr_pid integer ;
curr_stat_id integer ;
doc_man_id integer ;
BEGIN
-- Il DBA ha accesso a tutti i documenti
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
-- Se il documento ha l'etichetta 'cancellato' - non mostrarlo nella selezione
SELECT
is_del
INTO
risultato
FROM
store.docs
WHERE
id = current_id ;
IF risultato = TRUE
THEN
RETURN FALSE ;
END IF ;
--------------------------------
-- Ottenere l'id dell'utente corrente
SELECT
service_function.get_curr_pid ()
INTO
curr_pid ;
--------------------------------
-- Ottenere l'id del manager del documento
SELECT
man_id
INTO
doc_man_id
FROM
store.docs
WHERE
id = current_id ;
--------------------------------
-- Se il manager del documento non è l'utente corrente o il manager non è assegnato
-- aggiungere il documento alla selezione
IF doc_man_id != curr_pid OR doc_man_id IS NULL
THEN
RETURN TRUE ;
ELSE
-- Ottenere lo stato corrente del documento
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;
-- Se lo stato consente di visualizzare il documento - aggiungere il documento alla selezione
IF curr_statid = 4 OR curr_statid = 9
THEN
RETURN TRUE ;
ELSE
-- Altrimenti - escludere il documento dalla selezione
RETURN FALSE ;
END IF ;
END IF ;
--------------------------------
RETURN FALSE ;
END
$$ LINGUAGGIO plpgsql SECURITY DEFINER;
ALTERA FUNZIONE store.check_select( store.docs.id%TYPE ) PROPRIETARIO A store ;
REVOCARE ESECUZIONE SULLA FUNZIONE store.check_select( store.docs.id%TYPE ) DA public;
CONCEDERE ESECUZIONE SULLA FUNZIONE store.check_select( store.docs.id%TYPE ) A service_functions;
Verifica la possibilità di eseguire un'istruzione INSERT di una riga
check_insert
CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
curr_role_id integer ;
BEGIN
--Il DBA può aggiungere una riga in ogni caso
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--Ottenere l'id del ruolo dell'utente attuale
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------
--Se il ruolo consente la creazione di un nuovo documento
--permettere
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 la possibilità di eseguire un'istruzione DELETE di una riga
check_delete
CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN
--Solo il DBA può eliminare una riga
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 la possibilità di eseguire un'istruzione UPDATE di una riga.
update_using
CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN
--I documenti con stato 'eliminato' non possono essere modificati
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
--Il DBA può visualizzare la riga
IF SESSION_USER = 'curr_dba'
THEN
RETURN TRUE ;
END IF ;
--------------------------------
--Ottieni l'id del ruolo dell'utente attuale
SELECT
service_functions.current_rid()
INTO
curr_role_id ;
--------------------------------
--Eliminazione del documento - modifica del flag
IF is_deleted
THEN
--Se il ruolo dell'utente ***
IF current_role_id = 3
THEN
SELECT
stat_id
INTO
curr_statid
FROM
store.docs
WHERE
id = current_id ;
--Il documento in stato *** non può essere eliminato
IF current_status_id = 11
THEN
RETURN FALSE ;
ELSE
--È possibile eliminare il documento in altri stati
RETURN TRUE ;
END IF ;
--Altrimenti, se il ruolo dell'utente ***
ELSIF current_role_id = 5
THEN
--Tutti gli stati del documento
RETURN TRUE ;
ELSE
--Altri utenti non possono eliminare documenti
RETURN FALSE ;
END IF ;
ELSE
--Aggiornamento del documento consentito
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;Attivazione della politica di Row Level Security per la tabella.
ABILITA ROW LEVEL SECURITY
ALTER TABLE store.docs ABILITA 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 permesso di trasferire l'implementazione del modello di ruoli dal livello delle funzioni aziendali al livello di archiviazione dei dati.
Le funzioni possono essere utilizzate come modello per implementare modelli di occultamento dei dati più sofisticati, qualora le esigenze aziendali lo richiedano.
Fonte: habr.com
