The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Создание Virtual Private Database в базе данных Oracle. (oracle virtual)


<< Предыдущая ИНДЕКС Правка src / Печать Следующая >>
Ключевые слова: oracle, virtual,  (найти похожие документы)
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 SQL> -- посмотрим лог SQL> connect tz/tz@test Connected. SQL> select * from secure_log order by logdate; 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 4 2 23-MAR-06 04.35.08.828000 PM do_logon() user: TZ1_1 5 2 23-MAR-06 04.35.08.828000 PM 1 select_predicate: "(basecompanyid IS NULL OR basecompanyid = 1)" insert_predicate: "basecompanyid = 1" 6 3 23-MAR-06 04.37.08.625000 PM do_logon() user: TZ2_1 7 3 23-MAR-06 04.37.08.640000 PM 2 select_predicate: "(basecompanyid IS NULL OR basecompanyid = 2)" insert_predicate: "basecompanyid = 2" 8 4 23-MAR-06 04.42.31.046000 PM do_logon() user: TZ3_1 9 4 23-MAR-06 04.42.31.046000 PM select_predicate: "1=2" insert_predicate: "1=2" 10 5 23-MAR-06 04.50.21.750000 PM do_logon() user: TZ 11 6 23-MAR-06 04.50.21.750000 PM select_predicate: "" insert_predicate: "" 11 rows selected. (c) LionSoftware 2006-03-23

<< Предыдущая ИНДЕКС Правка src / Печать Следующая >>

Обсуждение [ RSS ]
  • 1, sabitov (??), 04:41, 27/03/2006 [ответить]  
  • +/
    Есть еще вот такая чудная статья на эту же тему:
    http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Data

    О том же самом, на чуть ином примере и другими словами.

     
  • 2, Lion (??), 05:48, 27/03/2006 [ответить]  
  • +/
    Материала на самом деле навалом, но вот на www.opennet.ru не пробегало.
     
  • 3, alex (??), 10:37, 19/12/2006 [ответить]  
  • +/
    Не совсем понятно, для чего Оракл ввел дополнительный параметр update_check в процедуре ADD_POLICY, его значение влияет только на операцию INSERT. На update не влияет, что, кстати, проитиворечит документации.

    Пример:
    drop table doc;
    create table doc (doc_name,distr) as select rownum||'-й район',rownum from all_tables where rownum<=10;

    create or replace function FUNCT (object_schema IN VARCHAR2, object_name VARCHAR2)
    RETURN VARCHAR2 as
    begin
    return 'DISTR=3';
    end;
    /

    exec DBMS_RLS.DROP_POLICY('Q','DOC','MY_POLICY');
    exec DBMS_RLS.ADD_POLICY('Q','DOC','MY_POLICY','Q','FUNCT','update',false);
    update doc set doc_name='NEWNAME' where distr=2;
    update doc set doc_name='NEWNAME' where distr=3;

    select * from doc;

    ведет себя так же, как и
    exec DBMS_RLS.ADD_POLICY('Q','DOC','MY_POLICY','Q','FUNCT','update',true);
    update doc set doc_name='NEWNAME' where distr=2;
    update doc set doc_name='NEWNAME' where distr=3;
    select * from doc;

     

     Добавить комментарий
    Имя:
    E-Mail:
    Заголовок:
    Текст:




    Спонсоры:
    Inferno Solutions
    Hosting by Hoster.ru
    Хостинг:

    Закладки на сайте
    Проследить за страницей
    Created 1996-2022 by Maxim Chirkov
    Добавить, Поддержать, Вебмастеру