From: Игорь Благодетелев <LionSoftware@mail.ru.>
Newsgroups: email
Date: Mon, 26 Mar 2006 14:31:37 +0000 (UTC)
Subject: Создание Virtual Private Database в базе данных Oracle.
Предыстория.
------------
Компания создала филиалы, филиалы имеют доступ к центральной базе
данных. Возникла необходимость ограничить доступ филиальных пользователей к
некоторым записям в существующих таблицах базы данных. Например, чтобы
менеджеры из разных филиалов не видили продаж других филиалов, ибо незачем
им. Переписывать весь софт лениво, да и незачем, так как есть VPD.
ВНИМАНИЕ!
ВНИМАНИЕ! Не проводите эксперименты в production базе данных!
ВНИМАНИЕ! Сделайте export и import в development database!
ВНИМАНИЕ!
Дано:
- Oracle 9/10.
- Схема TZ с таблицами.
- Создается таблица филиалов (basecompany).
- Пользователи делятся по филиалам, для этого создается новая таблица "Пользователи" (secure_user).
- Создается таблица (test) для экспериментов.
- Создается лог для... а просто для себя (secure_log).
Нужно:
- Обеспечить доступ на select, update, delete, insert нужным пользователям,
при этом обеспечить доступ исключительно к своим строкам таблицы.
- Обеспечить возможность добавления строк, которые будут видны всем филиалам.
Приступим:
Входим как SYSDBA и создаем пользователей
CONNECT SYS@TEST AS SYSDBA
-- Основной пользователь которому принадлежат все объекты и т.д.
-- под ним вход будет разрешен только DB админу
CREATE USER "TZ" IDENTIFIED BY "tz" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "TZ";
GRANT "RESOURCE" TO "TZ";
ALTER USER "TZ" DEFAULT ROLE ALL;
-- Пользователь 1 из первого филиала
CREATE USER "TZ1_1" IDENTIFIED BY "tz" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "TZ1_1";
ALTER USER "TZ1_1" DEFAULT ROLE ALL;
-- Пользователь 1 из второго филиала
CREATE USER "TZ2_1" IDENTIFIED BY "tz" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "TZ2_1";
ALTER USER "TZ2_1" DEFAULT ROLE ALL;
-- Пользователь 2 из второго филиала
CREATE USER "TZ2_2" IDENTIFIED BY "tz" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "TZ2_2";
ALTER USER "TZ2_2" DEFAULT ROLE ALL;
-- Пользователь 1 из третьего филиала, которого пока нет
CREATE USER "TZ3_1" IDENTIFIED BY "tz" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "TZ3_1";
ALTER USER "TZ3_1" DEFAULT ROLE ALL;
Входим как админ TZ и создаем необходимые для теста таблицы
CONNECT tz/tz@TEST
-- Таблица "Базовая компания", которая обозначает один из филиалов
CREATE SEQUENCE basecompany_seq;
CREATE TABLE basecompany (
basecompanyid INT NOT NULL PRIMARY KEY
, name VARCHAR2(255) NOT NULL
);
GRANT select ON basecompany TO PUBLIC;
INSERT INTO basecompany VALUES (basecompany_seq.nextval, 'Sydney'); -- 1
INSERT INTO basecompany VALUES (basecompany_seq.nextval, 'Adelaide'); -- 2
INSERT INTO basecompany VALUES (basecompany_seq.nextval, 'Melbourne'); -- 3
INSERT INTO basecompany VALUES (basecompany_seq.nextval, 'Brisbane'); -- 4
COMMIT;
-- Пользователи базы данных, которых создавали как CREATE USER выше
CREATE SEQUENCE secure_user_seq;
CREATE TABLE secure_user (
id INT NOT NULL PRIMARY KEY
, name VARCHAR2(255) NOT NULL
, basecompanyid REFERENCES basecompany
);
GRANT select ON secure_user TO PUBLIC;
INSERT INTO secure_user VALUES (secure_user_seq.nextval, 'TZ1_1', 1); -- 1
INSERT INTO secure_user VALUES (secure_user_seq.nextval, 'TZ2_1', 2); -- 2
INSERT INTO secure_user VALUES (secure_user_seq.nextval, 'TZ2_2', 2); -- 3
COMMIT;
-- Создаем тестовую таблицу над которой будем проводить эксперименты
CREATE SEQUENCE test_seq;
GRANT select ON test_seq TO public;
CREATE TABLE test (
id INT NOT NULL PRIMARY KEY
, txt VARCHAR2(255)
, basecompanyid REFERENCES basecompany
);
GRANT select,update,delete,insert ON test TO PUBLIC;
INSERT INTO test VALUES (test_seq.nextval, 'open to all #1', null);
INSERT INTO test VALUES (test_seq.nextval, 'show to 1 #1', 1);
INSERT INTO test VALUES (test_seq.nextval, 'for 2 #1', 2);
INSERT INTO test VALUES (test_seq.nextval, 'open to all #2', null);
INSERT INTO test VALUES (test_seq.nextval, 'for 2 #2', 2);
INSERT INTO test VALUES (test_seq.nextval, 'open to all #3', null);
INSERT INTO test VALUES (test_seq.nextval, 'show to 1 #2', 1);
INSERT INTO test VALUES (test_seq.nextval, 'show to 1 #3', 1);
COMMIT;
--
-- А теперь основное
--
-- Лог действий
CREATE TABLE secure_log (
id INT NOT NULL PRIMARY KEY
, session_id INT NOT NULL -- внутренний идентификатор сессии
, logdate TIMESTAMP NOT NULL -- дата события
, user_ref REFERENCES secure_user(id) -- ссылка на пользователя
, message VARCHAR2(2000) -- сообщение
);
GRANT INSERT ON secure_log TO PUBLIC;
-- Счетчик записей лога
CREATE SEQUENCE secure_log_seq;
GRANT SELECT ON secure_log_seq TO PUBLIC;
-- Счетчик сессий
CREATE SEQUENCE secure_session_seq;
GRANT SELECT ON secure_session_seq TO PUBLIC;
-- Пакет, содержащий процедуры и функции обеспечения безопасности
CREATE OR REPLACE PACKAGE secure_package
AS
-- Счетчик сессий
session_id secure_log.session_id%TYPE;
-- Предикат для выполнения SELECT, UPDATE, DELETE
select_predicate VARCHAR2(2000);
-- Предикат для выполнения INSERT
insert_predicate VARCHAR2(2000);
-- Процедура входа
PROCEDURE do_logon;
-- Возвращает select_predicate
FUNCTION get_select_predicate
(obj_schema VARCHAR2, obj_name VARCHAR2) return VARCHAR2;
-- Возвращает insert_predicate
FUNCTION get_insert_predicate
(obj_schema VARCHAR2, obj_name VARCHAR2) return VARCHAR2;
END secure_package;
-- Тело пакета
CREATE OR REPLACE PACKAGE BODY secure_package
AS
-- Процедура входа
PROCEDURE do_logon IS
-- Идентификатор пользователя из secure_user
user_id secure_user.id%TYPE;
BEGIN
-- Если имя пользователя начинается на TZ, то только в этом случае
-- производятся все действия, к другим пользователям не лезем
-- и не мешаем другим жить
IF SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER'),1,2) = 'TZ' THEN
-- Если logon уже был выполнен, то ругнемся и выходим
IF session_id IS NOT NULL THEN
INSERT INTO secure_log (id, session_id, logdate, user_ref, message)
VALUES (
secure_log_seq.nextval
, session_id
, SYSTIMESTAMP
, user_id
, 'Attempt to execute do_logon() once again'
);
RETURN;
END IF;
-- Автоматически меняем текущую сехму на TZ - для удобства
-- и чтобы не создавать кучу синонимов
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = TZ';
-- Получаем идентификатор сессии, сохраняем в session_id
SELECT secure_session_seq.nextval INTO session_id FROM dual;
-- Первое сообщение в лог о входе
INSERT INTO secure_log (id, session_id, logdate, user_ref, message)
VALUES (
secure_log_seq.nextval
, session_id
, SYSTIMESTAMP
, NULL
, 'do_logon() user: ' || SYS_CONTEXT('USERENV','SESSION_USER')
);
-- Поиск идентификатора пользователя
BEGIN
SELECT id INTO user_id
FROM secure_user
WHERE UPPER(name) = SYS_CONTEXT('USERENV','SESSION_USER');
EXCEPTION WHEN NO_DATA_FOUND THEN -- Если пользователь не найден, то
user_id := NULL; -- пользователь нам неизвестен
END;
-- Если вошел администратор, то ему разрешено все
IF SYS_CONTEXT('USERENV','SESSION_USER') = 'TZ' THEN
select_predicate := ''; -- Разрешаем смотреть все записи
insert_predicate := ''; -- Разрешаем добавлять для всех
ELSE
DECLARE
basecompany_id basecompany.basecompanyid%TYPE;
BEGIN
-- Поиск идентификатора базовой компании
SELECT basecompanyid INTO basecompany_id
FROM secure_user
WHERE UPPER(name) = SYS_CONTEXT('USERENV','SESSION_USER');
-- Составление предиката для выполнения запросов
-- SELECT, UPDATE, DELETE
select_predicate :=
'(basecompanyid IS NULL ' -- для всех
|| 'OR basecompanyid = ' -- или кому-то конкретно
|| basecompany_id
|| ')';
-- С предикатом для INSERT все проще - разрешаем
-- добавлять только для своей базовой компании
insert_predicate := 'basecompanyid = ' || basecompany_id;
EXCEPTION WHEN NO_DATA_FOUND THEN -- Если пользователь не найден, то
select_predicate := '1=2'; -- Не разрешаем смотреть записи
insert_predicate := '1=2'; -- Не разрешаем добавлять записи
END;
END IF;
-- Отчет в лог
INSERT INTO secure_log (id, session_id, logdate, user_ref, message)
VALUES (
secure_log_seq.nextval
, session_id
, SYSTIMESTAMP
, user_id
, 'select_predicate: "'
|| select_predicate
|| '" insert_predicate: "'
|| insert_predicate
|| '"'
);
END IF;
END do_logon;
-- Возвращает select_predicate
FUNCTION get_select_predicate
(obj_schema VARCHAR2, obj_name VARCHAR2) return VARCHAR2 IS
BEGIN
RETURN select_predicate;
END get_select_predicate;
-- Возвращает insert_predicate
FUNCTION get_insert_predicate
(obj_schema VARCHAR2, obj_name VARCHAR2) return VARCHAR2 IS
BEGIN
RETURN insert_predicate;
END get_insert_predicate;
END secure_package;
-- Разрешаем выполнять пакет
GRANT EXECUTE ON secure_package TO PUBLIC;
Входим как SYSDBA
CONNECT SYS@TEST AS SYSDBA
-- Вешаем триггер на вход
CREATE OR REPLACE TRIGGER secure_vpd
AFTER logon ON database
BEGIN
-- Вызываем соответствующую процедуру для регистрации входа
TZ.secure_package.do_logon;
END;
-- Вот, ради чего все делалось - добавление Row Level Security (RLS)
BEGIN
DBMS_RLS.ADD_POLICY (
'tz' -- схема, где находится защищаемый объект
, 'test' -- защищаемый объект
, 'test_select_policy' -- название Policy
, 'tz' -- схема, откуда будем вызывать процедуру
, 'secure_package.get_select_predicate' -- процедура, возвращающая предикат
, 'select,update,delete'); -- список действий от которых защищаемся
END;
-- Для удаления RLS нужно будет выполнить:
-- BEGIN DBMS_RLS.DROP_POLICY('TZ','TEST','test_select_policy'); END;
BEGIN
DBMS_RLS.ADD_POLICY (
'tz' -- схема, где находится защищаемый объект
, 'test' -- защищаемый объект
, 'test_insert_policy' -- название Policy
, 'tz' -- схема, откуда будем вызывать процедуру
, 'secure_package.get_insert_predicate' -- процедура, возвращающая предикат
, 'insert' -- защищаемся от несанкционированного INSERT
, TRUE); -- ВНИМАНИЕ! TRUE здесь ОБЯЗАТЕЛЬНО!
END;
-- Для удаления RLS нужно будет выполнить:
-- BEGIN DBMS_RLS.DROP_POLICY('TZ','TEST','test_insert_policy'); END;
Теперь проверяем что получилось
CONNECT tz/tz@TEST
[lion@lion ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 23 16:28:31 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect tz/tz@TEST
Connected.
SQL> select * from secure_log; --> смотрим что после входа TZ
ID SESSION_ID LOGDATE USER_REF MESSAGE
1 1 23-MAR-06 04.30.58.734000 PM do_logon() user: TZ
2 1 23-MAR-06 04.30.58.734000 PM select_predicate: ""
insert_predicate: ""
SQL> begin secure_package.do_logon; end; --> попробуем сделать logon еще раз
2 /
PL/SQL procedure successfully completed.
SQL> select * from secure_log; --> logon еще раз сделать не получилось
ID SESSION_ID LOGDATE USER_REF MESSAGE
1 1 23-MAR-06 04.30.58.734000 PM do_logon() user: TZ
2 1 23-MAR-06 04.30.58.734000 PM select_predicate: ""
insert_predicate: ""
3 1 23-MAR-06 04.31.32.468000 PM Attempt to execute
do_logon() once again
SQL> select * from test; --> видны все записи
ID TXT BASECOMPANYID
1 open to all #1
2 show to 1 #1 1
3 for 2 #1 2
4 open to all #2
5 for 2 #2 2
6 open to all #3
7 show to 1 #2 1
8 show to 1 #3 1
8 rows selected.
SQL> -- новые записи создаются нормально:
SQL> INSERT INTO test VALUES (test_seq.nextval, 'INSERT TEST FOR ALL', NULL);
1 row created.
SQL> INSERT INTO test VALUES (test_seq.nextval, 'INSERT TEST FOR Sydney', 1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> -- попробуем от имени филиала:
SQL> connect tz1_1/tz@test
Connected.
SQL> select * from secure_log; --> лог посмотреть нельзя - это нормально
select * from secure_log
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> -- А теперь пробуем test:
SQL> select * from test;
ID TXT BASECOMPANYID
1 open to all #1
2 show to 1 #1 1
4 open to all #2
6 open to all #3
7 show to 1 #2 1
8 show to 1 #3 1
9 INSERT TEST FOR ALL
10 INSERT TEST FOR Sydney 1
8 rows selected. --> нет записей, относящихся ко вторму филиалу
SQL> update test set txt = txt || '_updated_';
8 rows updated. --> только те строки, к которым есть доступ
SQL> -- попробуем удалить не принадлежащую нам
SQL> -- строку, указав ее по индексу
SQL> delete from test where id = 3;
0 rows deleted. --> ее как будто не существует
SQL> COMMIT;
Commit complete.
SQL> -- попробуем от имени другого филиала:
SQL> connect tz2_1/tz@test
Connected.
SQL> select * from test;
ID TXT BASECOMPANYID
1 open to all #1_updated_
3 for 2 #1 2
4 open to all #2_updated_
5 for 2 #2 2
6 open to all #3_updated_
9 INSERT TEST FOR ALL_updated_
6 rows selected. --> нет записей от первого филиала
SQL> insert into test VALUES (test_seq.nextval, 'Insert for Adelaide', 2);
1 row created.
SQL> -- Попробуем добавить данные для первого филиала
SQL> insert into test VALUES (test_seq.nextval, 'Insert for Sydney', 1);
insert into test VALUES (test_seq.nextval, 'Insert for Sydney', 1)
*
ERROR at line 1:
ORA-28115: policy with check option violation
SQL> -- Попробуем добавить данные для всех
SQL> insert into test VALUES (test_seq.nextval, 'Insert for ALL', NULL);
insert into test VALUES (test_seq.nextval, 'Insert for ALL', NULL)
*
ERROR at line 1:
ORA-28115: policy with check option violation
SQL> commit;
Commit complete.
SQL> connect tz3_1/tz@test
Connected.
SQL> -- Если пользователь не в списке secure_user, то ему ничего нельзя
SQL> select * from test;
no rows selected
SQL> -- insert тоже нельзя
SQL> insert into test values (test_seq.nextval, 'xxx', NULL);
insert into test values (test_seq.nextval, 'xxx', NULL)
*
ERROR at line 1:
ORA-28115: policy with check option violation
Vova
