Развіццё тэмы
Выкарыстаная стратэгія мае на ўвазе выкарыстанне канцэпцыі «Бізнес-логіка ў БД», што было крыху падрабязней апісана тут.
Тэарэтычная частка выдатна апісана ў дакументацыі
У артыкуле нічога новага, няма схаванага сэнсу і таемных ведаў. Проста замалёўка аб практычнай рэалізацыі тэарэтычнай ідэі. Калі каму цікава - чытайце. Каму не цікава - не марнуйце свой час дарма.
Пастаноўка задачы
Неабходна размежаваць доступ на прагляд/устаўку/змяненне/выдаленне дакумента ў адпаведнасці з роляй карыстальніка прыкладання. Пад роляй маецца на ўвазе запіс у табліцы ролі звязанай стаўленнем многія-да-многім з табліцай карыстальнікі. Дэталі рэалізацыі табліц, з прычыны трывіяльнасці, апушчаны. Таксама апушчаны канкрэтныя дэталі рэалізацыі звязаныя з прадметнай вобласцю.
Рэалізацыя
Ствараем ролі, схемы, табліцу
Стварэнне аб'ектаў БД
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 ;
Ствараем функцыі для рэалізацыі RLS
Праверка магчымасці выканаць SELECT радкі
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;
Праверка магчымасці выканаць INSERT радкі
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;
Праверка магчымасці выканаць DELETE радкі
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;
Праверка магчымасці выканаць UPDATE радкі.
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;
Уключэнне палітыкі Row Level Secutiry для табліцы.
ENABLE 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 )) );
Вынік
Гэта працуе.
Прапанаваная стратэгія дазволіла перанесці рэалізацыю ролевай мадэлі з узроўня бізнес-функцый на ўзровень захоўвання дадзеных.
Функцыі могуць быць скарыстаны ў якасці шаблону для рэалізацыі больш выдасканаленых мадэляў утойвання дадзеных, калі таго патрабуюць бізнэс-патрабаванні.
Крыніца: habr.com