Книги, научные публикации
Pages: | 1 | ... | 16 | 17 | 18 | 19 | 20 | ... | 24 | Oracle для профессионалов Том Кайт DiaSoft 2003 торгово-издательский дом Москва Х Санкт-Петербург Х Киев УДК 681.3. 06(075) Б Б К 32.973.2 К 91 КАЙТ ТОМ К 91 Oracle для профессионалов. Пер. с ...
-- [ Страница 18 ] --Глава Созданное представление можно использовать: scott@TKYTE816> s e l e c t dname, d.emps 2 from dept_or d 3/ DNAME ACCOUNTING EMPS(EMPNO, ENAME, JOB, MGR, HIREDATE, S EMP_TAB_TYPE(EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, NOLL), EMP_TYPE(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-81', 5000, NULL), EMP_TYPE(7934, 'MILLER', 'CLERK', 7782, '23-JAN-82', 1300, NULL)) EMP_TAB_TYPE(EMP_TYPE(7369, 'SMITH', 'CLERK', 7902, '17-DEC-80', 800, NULL), EMP_TYPE(7566, 'JONES', 'MANAGER', 7839, '02-APR-81', 2975, NULL), EMP_TYPE(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-82', 3000, NULL), EMP_TYPE(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-83', 1100, NULL), EMP_TYPE(7902, 'FORD', 'ANALYST', 7566, '03-DEC-81', 3000, NULL)) EMP_TAB_TYPE(EMP_TYPE(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-81', 1600, 300), EMP_TYPE(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-81', 1250, 500), EMP_TYPE(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400), EMP_TYPE(7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-81', 2850, NULL), EMP_TYPE(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-81', 1500, 0), EMP_TYPE(7900, 'JAMES', 'CLERK', 7698, '03-DEC-81', 950, NULL)) EMP_TAB_TYPE() RESEARCH SALES OPERATIONS 4 rows selected. scott@TKYTE816> select deptno, dname, loc, count(*) 2 from dept_or d, table (d.emps) 3 group by deptno, dname, loc 4/ DEPTNO DNAME 10 ACCOUNTING 20 RESEARCH 30 SALES 3 rows selected. Итак, у нас есть реляционные таблицы и объектно-реляционное представление. Пользователю трудно определить, где Ч представление, а где Ч таблицы. Все возможLOC NEW YORK DALLAS CHICAGO COUNT(*) 3 5 Использование объектно-реляционных средств ности объектной таблицы доступны: из нее можно выбрать ссылки на объекты, вложенная таблица создана и т.д. Преимущество такой реализации в том, что мы явно указываем, как соединять таблицы ЕМР и DEPT, используя естественное отношение Ч главный/подчиненный. Итак, создано объектно-реляционное представление, позволяющее запрашивать данные. Но изменять его данные пока нельзя: scott@TKYTE816> update TABLE (select p.emps 2 from dept_or p 3 where deptno = 20) 4 set ename = lower(ename) 5/ set ename = lower(ename) * E R R at l i n e 4: RO ORA-25015: cannot perform DML on this nested table view column scott@TKYTE816> declare 2 l_emps emp_tab_type;
3 begin 4 select p.emps into l_emps 5 from dept_or p 6 where deptno = 10;
7 8 for i in 1.. l_emps.count 9 loop 10 l_emps(i).ename := lower(l_emps(i).ename);
11 end loop;
12 13 update dept_or 14 set emps = l_emps 15 where deptno = 10;
16 end;
17 / declare * ERROR at line 1: ORA-01733: virtual column not allowed here ORA-06512: at line 13 Необходимо заставить представление "обновляться". У нас реализовано сложное сопоставление реляционных данных объектно-реляционным (на самом деле оно может иметь любую степень сложности). Так как же заставить представление "обновляться"? Сервер Oracle обеспечивает для этого соответствующий механизм Ч триггеры INSTEAD 0F. Можно реализовать алгоритм, который должен выполняться сервером Oracle вместо (INSTEAD OF) стандартного при изменении содержимого представления. Чтобы продемонстрировать это, давайте обеспечим возможность изменения показанного ранее представления. Сервер Oracle позволяет создать триггер INSTEAD OF по представлению DEPT_OR и по любому типу вложенной таблицы, входящей в это представление. Создав триггер Глава по столбцам вложенной таблицы, можно изменять столбец вложенной таблицы так, будто это обычная таблица. Соответствующий триггер может иметь следующий вид: scott@TKYTE816> create or replace trigger EMPS_IO_UPDATE 2 instead of UPDATE on nested table emps of dept_or 3 begin 4 if (:new.empno = :old.empno) 5 then 6 update emp 7 set ename = :new.ename, job = :new.job, mgr = :new.mgr, 8 hiredate = :new.hiredate, sal = :new.sal, -> comm = :new.comm 9 where empno = :old.empno;
10 else 11 raise_application_error(-20001,'Значение столбца empno -> изменять нельзя');
12 end if;
13 end;
14 / Trigger created. Как видите, триггер будет срабатывать при изменении (INSTEAD OF UPDATE) столбца типа вложенной таблицы, EMPS, представления DEPT_OR. Он будет срабатывать для каждой изменяемой строки вложенной таблицы и имеет доступ к значениям до и после изменения (:OLD и :NEW), как и "обычный" триггер. В данном случае понятно, что надо делать, Ч изменить строку таблицы ЕМР с соответствующим значением EMPNO, задав ее столбцам новые значения. В этом триггере я принудительно запрещаю изменять первичный ключ (мы используем объектно-реляционные средства, но это не значит, что можно нарушать основные принципы проектирования реляционных баз данных). Теперь, выполним следующие операторы: scott@TKYTE816> update TABLE (select p.emps 2 from dept_or p 3 where deptno = 20) 4 set ename = lower(ename) 5/ 5 rows updated. scott@TKYTE816> select ename from emp where deptno = 20;
ENAME smith jones scott adams ford scott@TKYTE816> select ename 2 from TABLE(select p.emps 3 from dept_or p 4 where deptno = 20);
Использование объектно-реляционных средств ENAME smith jones scott adams ford Как видите, изменение вложенной таблицы успешно преобразовано в изменение реляционной таблицы, как и ожидалось. Так же легко написать триггеры на события INSERT и DELETE, поскольку UPDATE Ч самый сложный случай. Так что на этом и остановимся. Если сейчас выполнить следующее изменение: scott@TKYTE816> declare 2 l_emps emp_tab_type;
3 begin 4 select p.emps into l_emps 5 from dept_or p 6 where deptno = 10;
7 8 for i in 1.. l_emps.count 9 loop 10 l_emps(i).ename := lower(l_emps(i).ename);
11 end loop;
12 13 update dept_or 14 set emps = l_emps 15 where deptno = 10;
16 end;
17 / declare * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view ORA-06512: at line 13 то окажется, что при выполнении выдается сообщение об ошибке. Странно. Не должен ли сработать созданный ранее триггер? На самом деле Ч нет. Только изменения вложенной таблицы, связанные с извлечением ее данных, вызовут срабатывание триггера. Триггер срабатывает, только если с вложенной таблицей работают как с обычной таблицей. Мы же не выполняем операцию над большим количеством данных вложенной таблицы, а только изменяем столбец представления DEPT_OR. Чтобы поддержать работу подобного кода (и изменений других скалярных столбцов представления DEPT_OR), необходимо создать триггер INSTEAD OF для представления DEPT_OR. Этот триггер будет обрабатывать значения :OLD.EMPS и :NEW.EMPS как множества следующим образом. 1. Удалять из таблицы ЕМР все записи, значение EMPNO которых было в наборе :OLD, но отсутствует в наборе :NEW. Для этого прекрасно подходит реляционный оператор MINUS.
Глава 2. зменять в таблице ЕМР каждую запись, значение столбца EMPNO которой входит во множество значений EMPNO, соответствующие :NEW-записи которых отличаются от :OLD-записей. Это множество легко найти с помощью оператора MINUS. 3. Вставлять в таблицу ЕМР все записи :NEW, значение :NEW.EMPNO которых не находится во множестве значений EMPNO :OLD-записи. Вот как это реализуется: scott@TKYTE816> create or replace trigger DEPT_OR_IO_UPDATE 2 instead of update on dept_or 3 begin 4 if (:new.deptno = :old.deptno) 5 then 6 if updating('DNAME') or updating('LOC') 7 then 8 update dept 9 set dname = :new.dname, loc = :new.loc 10 where deptno = :new.deptno;
11 end if;
12 13 if (updating('EMPS')) 14 then 15 delete from emp 16 where empno in 17 (select empno 18 from TABLE(cast(:old.emps as emp_tab_type)) 19 MINUS 20 select empno 21 from TABLE(cast(:new.emps as emp_tab_type)) 22 );
23 dbms_output.put_line('удалено ' || sql%rowcount);
Первый оператор MINUS возвращает множество значений EMPNO, которые были в наборе :OLD, но отсутствуют в наборе :NEW. Эти записи надо удалить из таблицы ЕМР, поскольку в наборе их больше нет. Изменим те записи набора, которые были обновлены: 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 update emp E set (deptno, ename, job, mgr, hiredate, sal, comm) = (select :new.deptno, ename, job, mgr, hiredate, sal, comm from TABLE(cast(:new.emps as emp_tab_type)) T where T.empno = E.empno ) where empno in (select empno from (select * from TABLE(cast(:new.emps as emp_tab_type)) MINUS select * Использование объектно-реляционных средств 39 40 41 from TABLE(cast(:old.emps as emp_tab_type)) ) );
dbms_output.put_line('изменено ' || sql%rowcount);
Этот оператор MINUS возвращает все значения из :NEW, кроме совпадающих со значениями из :OLD;
это дает множество измененных записей. Оно используется в подзапросе для получения множества значений EMPNO, соответствующие записи для которых в таблице ЕМР надо изменить, после чего эти значения определяются с помощью коррелированного подзапроса. Наконец, добавляем новые записи: 43 44 insert into emp 45 (deptno, empno, ename, job, mgr, hiredate, sal, comm) 46 select :new.deptno,empno,ename,job,mgr,hiredate,sal,comm 47 from (select * 48 from TABLE(cast(:new.emps as emp_tab_type)) 49 where empno in 50 (select empno 51 from TABLE(cast(:new.emps as -> emp_tab_type)) 52 MINOS 53 select empno 54 from TABLE(cast(:old.emps as -> emp_tab_type)) 55 ) 56 );
57 dbms_output.put_line('вставлено ' II sql%rowcount);
58 else 59 dbms_output.put_line('обработка вложенной таблицы -> пропущена');
60 end if;
61 else 62 raise_application_error(-20001,'значение deptno изменять -> нельзя');
63 end if;
64 end;
65 / Trigger created. Оператор MINUS генерирует множество значений EMPNO из набора :NEW, отсутствующих в наборе :OLD;
оно представляет список строк, которые надо добавить в таблицу ЕМР. Триггер кажется огромным, но на самом деле он простой. Вначале он проверяет, не изменены ли скалярные столбцы представления DEPT_OR. Если Ч да, выполняются соответствующие изменения в таблице DEPT. Затем, если был изменен столбец вложенной таблицы (заменены все ее значения), эти изменения вносятся в таблицу ЕМР. Чтобы внести все необходимые изменения, надо: 1. удалить из таблицы ЕМР записи, которые были удалены из столбца вложенной таблицы EMPS;
Глава 2. изменить в таблице ЕМР те записи, значения которых были изменены в столбце вложенной таблицы EMPS;
3. вставить в таблицу ЕМР записи, добавленные в столбец вложенной таблицы EMPS. К счастью, SQL-оператор MINUS и возможность преобразовать столбец типа вложенной таблицы в реальную таблицу упрощает реализацию триггера. Теперь мы можем обрабатывать данные так: scott@TKYTE816> declare 2 l_emps emp_tab_type;
3 begin 4 select p.emps into l_emps 5 from dept_or p 6 where deptno = 10;
7 8 for i in 1.. l_emps.count 9 loop 10 l_emps(i).ename := lower(l_emps(i).ename);
11 end loop;
12 13 update dept_or 14 set emps = l_emps 15 where deptno = 10;
16 end;
17 / удалено 0 изменено З вставлено 0 PL/SQL procedure successfully completed. scott@TKYTE816> declare 2 l_emps emp_tab_type;
3 begin 4 select p.emps into l_emps 5 from dept_or p 6 where deptno = 10;
7 8 9 for i in 1.. l_emps.count 10 loop 11 if (l_emps(i).ename = 'miller') 12 then 13 l_emps.delete(i);
14 else 15 l_emps(i).ename := initcap(l_emps(i).ename);
16 end if;
17 end loop;
18 19 l_emps.extend;
20 l_emps(l_emps.count) := 21 emptype(1234, 'Tom', 'Boss', Использование объектно-реляционных средств 22 null, sysdate, 1000, 500);
23 24 update dept_or 25 set emps = l_emps 26 where deptno = 10;
27 end;
28 / удалено 1 изменено 2 вставлено 1 PL/SQL procedure successfully completed. scott@TKYTE816> update dept_or set dname = initcap(dname);
Обработка вложенной таблицы пропущена Обработка вложенной таблицы пропущена Обработка вложенной таблицы пропущена Обработка вложенной таблицы пропущена 4 rows updated. scott@TKYTE816> commit;
Commit complete. Триггер преобразует наши действия с экземпляром объекта в соответствующие изменения базовых реляционных таблиц. Возможность работать с реляционными данными через объектно-реляционные представления позволяет максимально использовать преимущества как реляционной, так и объектно-реляционной модели. Реляционная модель сильна тем, что с ее помощью можно ответить практически на любой вопрос о базовых данных. Рассматриваются ли данные по отделам (надо запросить информацию об отделе и его сотрудниках) или по сотрудникам (надо задать номер сотрудника и получить информацию об отделе, в котором он работает), всегда есть возможность выполнить соответствующий запрос. Реляционные таблицы можно использовать непосредственно или сгенерировать модель на основе объектного типа Ч все необходимые данные будут объединены и представлены в удобном для использования виде. Рассмотрим результаты следующих запросов: scott@TKYTE816> select * from dept_or where deptno = 10;
DEPTNO DNAME HIREDATE, S LOC EMPS(EMPNO, ENAME, JOB, MGR, EMP_TAB_TYPE(EMP_TYPE(7782, 'Clark', ХMANAGER1, 7839, '09-JUN-81', 2450, NULL), EMP_TYPE(7839, 'King', 'PRESIDENT', NULL, '17-NOV-81', 5000, NULL), EMP_TYPE(1234, 'Tom', 'Boss', NULL, '25-MAR-01', 1000, 500)) scott@TKYTE816> select dept.*, empno, ename, job, mgr, hiredate, sal, comm 2 from emp, dept 3 where emp.deptno = dept.deptno 4 and dept.deptno = 10 5/ 10 Accounting NEW YORK 1430 Глава DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COM 10 Accounting NEW YORK 7782 Clark MANAGER 7839 09-JUN-81 2450 10 Accounting NEW YORK 7839 King PRESIDENT 17-NOV-81 5000 10 Accounting NEW YORK 1234 Tom Boss 25-MAR-01 1000 500 Они возвращают похожие данные. Первый запрос в сжатом виде выдает всю информацию об отделе в виде одной строки. Он может возвращать несколько вложенных таблиц, для чего в классическом языке SQL пришлось бы выполнить несколько запросов. На сервере можно выполнить много действий, формируя ответ и возвращая его в виде одной строки. Когда работа выполняется в среде, где лишних обменов данными по сети следует избегать (из-за большого времени ожидания), это дает существенные преимущества. Не говоря уже о том, что один простой оператор SELECT * FROM T может выполнить действия нескольких SQL-операторов. Учтите также, что в объектном представлении нет повторяющихся столбцов данных. Значения столбцов DEPTNO, DNAME и LOC не повторяются для каждого сотрудника;
они возвращаются только один раз, что для многих приложений более удобно. Второй запрос требует от разработчика более глубокого знания данных, и эту особенность следует учитывать. Надо знать, как соединять данные;
если же соединяется много таблиц, возможно, потребуются дополнительные запросы, результаты которых придется обрабатывать самостоятельно. Поясню. Предположим, в модели необходимо учесть бюджет отдела на финансовый год. Он хранится в следующей реляционной таблице: scott@TKYTE816> create table dept_fy_budget 2 (deptno number(2) references dept, 3 fy date, 4 amount number, 5 constraint dept_fy_budget_pkprimary key(deptno,fy) 6) 7/ Table created. В этой таблице хранятся данные о бюджете отделов за этот и несколько предыдущих финансовых лет. Для работы приложения необходимо представление данных по отделам, включающее все скалярные данные (название, местонахождение). Кроме того, необходима информация о сотрудниках (столбец типа EMP_TAB_TYPE), а также данные о бюджете за последние финансовые годы. Для получения этих данных по реляционной модели разработчику приложения придется выполнять следующие операторы: scott@TKYTE816> s e l e c t dept.*, ettpno, ename, job, mgr, hiredate, sal, comn 2 from emp, dept 3 where emp.deptno = dept.deptno 4 and dept.deptno = 10 5/ DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COM 10 Accounting NEW YORK 7782 Clark MANAGER 7839 09-JON-81 2450 10 Accounting NEW YORK 7839 King PRESIDENT 17-NOV-81 5000 10 Accounting NEW YORK 1234 Tom Boss 25-MAR-01 1000 Использование объектно-реляционных средств 3 rows s e l e c t e d. scott@TKYTE816> s e l e c t fy, amount 2 from dept_fy_budget 3 where deptno = 10 4/ FY 01-JAN-99 Ol-JAN-00 01-JAN-01 AMOUNT 500 750 3 rows selected. Нельзя написать один реляционный запрос, выдающий все эти данные. Можно использовать ряд расширений Oracle (функцию CURSOR в SQL) для возврата строк, каждая из которых содержит результирующее множество: scott@TKYTE816> select 2 dept.deptno, dept.dname, 3 cursor(select empno from emp where deptno = dept.deptno), 4 cursor(select fy, amount from dept_fy_budget where deptno = dept.deptno) 5 from dept 6 where deptno = 10 7/ DEPTNO DNAME 10 ACCOUNTING CURSOR STATEMENT : 3 EMPNO 7782 7839 7934 3 rows selected. CURSOR STATEMENT : 4 FY 01-JAN-99 Ol-JAN-00 Ol-JAN-01 1 row selected. В данном случае была выбрана одна строка, вернувшая клиенту еще два курсора. Клиентское приложение выбрало данные из этих двух курсоров и выдало результаты. Это прекрасно работает, но требует знания особенностей базовых данных и способов AMOUNT 500 750 1000 CURSOR(SELECTEMPNOFR CURSOR(SELECTFY,AMOU CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 rows selected.
1432 Глава их объединения (как написать коррелированные подзапросы для генерации курсоров). Эту модель можно реализовать с помощь объектно-реляционных расширений, пересоздав представление следующим образом: scott@TKYTE816> c r e a t e or replace type dept_budget_type 2 as object 3 (fy date, 4 amount number 5) 6/ Type created. scott8TKYTE816> create or replace type dept_budget_tab_type 2 as table of dept_budget_type 3/ Type created. scott@TKYTE816> create or replace type dept_type 2 as object 3 (deptno number(2), 4 dname varchar2(14), 5 loc varchar2(13), 6 emps emp_tab_type, 7 budget dept_budget_tab_type 8) 9 / Type created. scott6TKYTE816> create or replace view dept_or 2 of dept_type 3 with object identifier(deptno) 4 as 5 select deptno, dname, loc, 6 cast (multiset ( 7 select empno, ename, job, mgr, hiredate, sal, conm 8 from emp 9 where emp.deptno = dept.deptno) 10 as emp_tab_type) emps, 11 cast (multiset ( 12 select fy, amount 13 from dept_fy_budget 14 where dept_fy_budget.deptno = dept.deptno) 15 as dept_budget_tab_type) budget 16 from dept 17 / View created. Теперь учтите, что представленные выше действия выполняются один раз, и все сложности от приложения скрыты. В приложении можно просто написать: scott@TKYTE816> select * from dept_or where deptno = 10 2/ Использование объектно-реляционных средств DEPTNO DNAME LOC EMPS(EMPNO, ENAME, J BUDGET(FY, AMOUNT) 10 Accounting NEW YORK EMP_TAB_TYPE(EMP_TYP DEPT_BUDGET_TAB_TYPE E(7782, 'Clark', (DEPT_BUDGET_TYPE('0 'MANAGER', 7839, l-JAN-99', 500), '09-JUN-81', 2450, DEPT_BUDGET_TYPE('01 NULL), -JAN-00', 750), EMP_TYPE(7839, DEPT_BUDGET_TYPE('01 'King', 'PRESIDENT', -JAN-01', 1000)) NULL, '17-NOV-81', 5000, NULL), EMP_TYPE(1234, 'Tom', 'Boss', NULL, '25-MAR-01', 1000, 500)) 1 row selected. Снова мы получаем одну строку, один экземпляр объекта, представляющего данные в нужном виде. Это весьма удобно. Сложность базовой физической модели скрыта, и легко понять, как создать графический интерфейс для представления этих данных пользователю. При программировании на языке Java (с помощью интерфейса JDBC), VisuaI Basic (с помощью объектов OO4O Ч Oracle Objects for Ole), PL/SQL, использовании библиотеки OCI (Oracle Call interface) и прекомпилятора Pro*C объектно-реляционные расширения легко применить. Использовать реляционную модель становится все труднее по мере добавления сложных отношений один ко многим. При использовании объектно-реляционной модели все несколько проще. Придется, конечно, изменить триггеры INSTEAD OF для поддержки изменения базовых реляционных данных, так что приведенный пример неполон, но идею вы, надеюсь, уловили.
Резюме В этой главе мы изучили основные способы использования объектных типов и расширений сервера Oracle. Из четырех возможных способов мы детально рассмотрели три. Мы рассмотрели использование объектных типов для расширения стандартного набора типов системы. С помощью нового типа ADDRESS_TYPE мы смогли не только задать общую систему именования и использования адресов, но и обеспечить специализированные методы и средства работы с ними. Мы также рассмотрели использование объектно-реляционных расширений для естественного развития возможностей языка PL/SQL. Мы взяли стандартный пакет и реализовали для него интерфейс на уровне объектного типа. Это позволяет защититься от изменений в реализации стандартного пакета, а также обеспечивает более "объектноориентированный" стиль программирования на PL/SQL, подобный использованию классов в языках С++ или Java. Кроме того, было показано, как, используя наборы, можно выбирать данные из PL/SQL-функции с помощью оператора SELECT. Одной этой возможности достаточно, чтобы оправдать использование объектно-реляционных расширений.
Глава Наконец, мы изучили, как использовать эти средства для создания объектно-реляционных представлений реляционных, по сути, данных. Как оказалось, можно легко создать специализированные объектные представления реляционных данных для любого количеств различных приложений. Основное преимущество этого подхода состоит в том, что в приложении можно применять практически тривиальные SQL-операторы. Не нужно выполнять соединения и делать несколько запросов, чтобы получить все необходимые данные. Все необходимое возвращается при выборке всего одной строки. Четвертый способ использования, создание объектных таблиц по хранимому типу данных, был рассмотрен в главе 6, посвященной таблицам. Поскольку объектные таблицы по "внешним" свойствам аналогичны объектным представлениям (или скорее, наоборот Ч объектные представления аналогичны объектным таблицам), их использование тоже, фактически, рассмотрено. Я предпочитаю не использовать объектные таблицы. По многим описанным ранее причинам, мне больше нравятся объектные представления реляционных таблиц. Основная причина в том, что, в конечном итоге, почта всегда необходимо поддерживать реляционное представление данных, как обеспечивающее различные способы использования данных в приложениях. Объектно-реляционные представления прекрасно подходят для моделирования специфических представлений данных в приложениях.
Тщательный контроль доступа Тщательный контроль доступа (Fine Grained Access Control Ч FGAC) в Oracle 8i позволяет во время выполнения динамически добавлять условие (конструкцию WHERE) ко всем запросам, обращенным к таблице или представлению базы данных. Теперь можно процедурно изменять запрос во время выполнения, другими словами, динамически создавать представление. Можно проверить, кто выполнял запрос, с какого терминала и когда (например, по времени суток) он выполнялся, а затем создать условие на основе этой информации. С помощью контекстов приложений можно безопасно добавлять в среду информацию (например, роль пользователя в отношении приложения) и обращаться к этой информации в процедуре или условии. В различных публикациях средства FGAC описываются под различными названиями. Обычно используются следующие: Х тщательный контроль доступа (Fine Grained Access Control);
Х виртуальная приватная база данных (Virtual Private Database Ч VPD);
Х защита на уровне строк (Row Level Security), или пакет DBMS_RLS (этот PL/SQLпакет реализует соответствующие возможности). Чтобы выполнять представленные в этой главе примеры, необходим сервер Oracle версии 8.1.5 или выше. Кроме того, средства тщательного контроля доступа доступны только в редакциях Enterprise и Personal Edition;
в Standard Edition эти примеры работать не будут. В этой главе мы рассмотрим следующее. Х Преимущества использования средств тщательного контроля доступа Ч простота сопровождения, реализация этих средств на сервере, возможность развития приложений и упрощение их разработки и т.д.
Глава Х Два примера в разделе "Как реализованы средства тщательного контроля доступа", демонстрирующие применение правил защиты (security policies) и контекстов приложений. Х Проблемы и нюансы, которые необходимо учитывать, в частности особенности функционирования средств тщательного контроля доступа при наличии требований целостности ссылок, кэширование курсоров, особенности экспортирования и импортирования данных и тонкости отладки. Х Ошибки, с которыми можно столкнуться при реализации тщательного контроля доступа в приложениях.
Пример Предположим, существуют правила защиты, определяющие, какие строки могут просматривать различные группы пользователей. Правила защиты позволяют разработать условие проверки, учитывающее, кто зарегистрирован и какую роль он имеет в системе. Средства тщательного контроля доступа позволяют переписать простой запрос SELECT * FROM EMP следующим образом: Пользователь Запрос переписывается так... зарегистрирован как... Сотрудник select * from (select * from emp where ename = USER) Комментарии Рядовые сотрудники могут просматривать только собственные записи, Руководитель подразделения select Х Руководители from (select Х подразделений могут from emp просматривать свои записи where mgr = и записи сотрудников (select empno своего подразделения, from emp where ename = USER) or ename = USER ) select * from (select * from emp where deptno = SYS_CONTEXT('OurApp', ) Сотрудники отдела кадров могут видеть все записи в данном подразделении. В этом примере представлен 'ptno') способ получения значений переменных из контекста приложения с помощью встроенной функции SYS_CONTEXT().
Сотрудники отдела кадров.
Тщательный контроль доступа Когда использовать это средство?
В этом разделе рассмотрены причины и варианты использования средств тщательного контроля доступа.
Простота сопровождения Средства тщательного контроля доступа позволяют с помощью одной таблицы и одной хранимой процедуры справиться с задачей, для решения которой могло бы понадобиться несколько представлений или триггеров, или большой объем специализированной обработки в приложениях. Подход с использованием нескольких представлений достаточно типичен. Разработчики приложений создают несколько учетных записей в базе данных, например EMPLOYEE, MANAGER, HR_REP, и устанавливают в каждой из соответствующих схем полный набор представлений, выбирающих только необходимые данные. Для рассматриваемого примера в каждой схеме создается отдельное представление ЕМР со специализированным условием выбора данных для соответствующей группы пользователей. Для управления тем, что конечные пользователи могут просматривать, создавать, изменять и удалять, придется создавать до четырех различных представлений для таблицы ЕМР - для операторов SELECT, INSERT, UPDATE и DELETE. Это быстро приводит к запредельному увеличению количества объектов базы данных Ч каждый раз при добавлении новой группы пользователей придется создавать и поддерживать новый набор представлений. Если правила защиты изменятся (например, если необходимо разрешить руководителям просматривать записи не только непосредственных подчиненных, но и подчиненных следующего уровня), придется пересоздать представление в базе данных, делая недействительными все объекты, которые на него ссылаются. Такой подход приводит не только к увеличению количества представлений в базе данных, но и требует, чтобы пользователи регистрировались от имени нескольких совместно используемых учетных записей, что осложняет контроль работы пользователей. Кроме того, этот подход требует дублирования значительного объема кода в базе данных. При наличии хранимой процедуры, работающей с таблицей ЕМР, придется устанавливать ее в каждой из задействованных схем. Это относится и ко многим другим объектам (триггерам, функциям, пакетам и т.д.). Теперь при внесении изменений в программное обеспечение придется каждый раз изменять N схем, чтобы все пользователи выполняли один и тот же код. Еще один подход связан с использованием, кроме представлений, триггеров базы данных. Вместо создания отдельных представлений для операторов SELECT, INSERT, UPDATE и DELETE, для построчного просмотра выполняемых пользователем изменений используется триггер, принимающий или отвергающий эти изменения. Эта реализация не только приводит к созданию большого количества дополнительных представлений, но и влечет расходы ресурсов на поддержку срабатывания триггера (иногда весьма сложного) для каждой изменяемой строки. Наконец, можно всю защиту реализовать в приложении, будь то клиентское приложение в случае архитектуры клиент-сервер или сервер приложений. Приложение будет учи Глава тывать, кто к нему обращается, и выполнять соответствующий запрос. Приложение по сути реализует собственный механизм тщательного контроля доступа. Серьезным недостатком такого подхода (и вообще любого подхода, использующего для доступа к данным специфические средства приложения) является то, что данные в базе могут использоваться только соответствующим приложением. Нельзя использовать никакие средства создания запросов, средства генерации отчетов и т.п., поскольку данные не защищены, если доступ к ним идет не через приложение. Когда защита встроена в приложение, усложняется развитие приложения и добавление новых интерфейсов, т.е. снижается полезность данных. Средства тщательного контроля доступа позволяют справиться с этими трудностями и избежать потери функциональных возможностей с помощью всего двух объектов исходной таблицы (или представления) и пакета (или функции) базы данных. Пакет можно изменить в любой момент, разработав новые правила защиты. Вместо поддержки десятков представлений, реализующих правила защиты для объекта, всю соответствующую информацию можно задавать в одном месте.
Контроль доступа выполняется на сервере С учетом сложности создания и поддержки большого количества представлений разработчики часто реализуют алгоритмы защиты в самом приложении, как было описано выше. Приложение анализирует, кто зарегистрировался и что он запрашивает, а затем отправляет на сервер соответствующий запрос. Это позволяет защитить данные только при доступе к ним через приложение, а вероятность неавторизованного доступа к данным увеличивается, поскольку для этого достаточно подключиться к базе данных с помощью любого инструментального средства, кроме приложения, и выполнить запрос. При тщательном контроле доступа алгоритмы защиты, определяющие, какие данные может "видеть" пользователь, помещаются в базу данных. При этом гарантируется защита данных, независимо от используемого средства доступа к ним. Потребность в таких средствах вполне объяснима. В начале и середине 1990-х годов преимущественно использовалась модель клиент-сервер (а еще раньше нормой считалось централизованное выполнение приложений). Большинство клиент-серверных приложений (и практически все централизованные) включали алгоритмы, проверяющие уровень доступа к приложению. Сегодня очень модно использовать серверы приложений и размещать на них все прикладные алгоритмы. По мере переноса клиент-серверных приложений на новую архитектуру разработчики начали переносить алгоритмы защиты с клиентской части и встраивать их в серверы приложений. Это привело к двойной реализации алгоритмов защиты (некоторые клиент-серверные приложения продолжают использоваться), так что теперь поддерживать и отлаживать эти алгоритмы надо в двух местах. Ситуация станет еще хуже при появлении следующей парадигмы программирования. Что случится, когда серверы приложений выйдут из моды? Что делать, если пользователям необходимо инструментальное средство сторонних производителей, обращающееся к данным непосредственно? Если вся защита сосредоточена на промежуточном сервере приложений, это становится невозможным. Если же защита реализуется сервером баз данных, вы готовы к применению любой технологии, как существующей, так и еще не придуманной.
Тщательный контроль доступа Упрощение разработки приложений Средства тщательного контроля доступа позволяют отделить алгоритмы защиты от других алгоритмов работы приложения. Разработчик приложения может заняться прикладными алгоритмами, а не алгоритмами безопасного доступа к данным. Поскольку тщательный контроль доступа выполняется полностью на сервере баз данных, эти алгоритмы немедленно наследуются всеми приложениями. Раньше разработчикам приходилось включать алгоритмы защиты в приложения, что усложняло разработку и дальнейшее сопровождение приложений. Если приложения должны обеспечивать защиту доступа к данным, а доступ к одним и тем же данным выполняется во многих компонентах приложения, изменение правил защиты повлияет на десятки модулей приложения. При использовании средств тщательного контроля доступа все соответствующие модули автоматически наследуют новые правила доступа без каких-либо изменений в коде.
Эволюционная разработка приложений Во многих средах правила защиты изначально строго не сформулированы и со временем могут меняться. При слиянии компаний или ужесточении правил доступа к данным, или появлении новых законов о невмешательстве в частную жизнь правила защиты придется изменять. Если средства контроля доступа реализованы как можно ближе к данным, эти изменения легко учесть при минимальном изменении приложений и инструментальных средств. Новые алгоритмы защиты реализуются в одном месте, а все приложения и средства доступа к базе данных автоматически наследуют новые алгоритмы.
Отказ от совместно используемых учетных записей При использовании средств тщательного контроля доступа каждый пользователь может регистрироваться от имени уникальной учетной записи. Это позволяет выполнять полный учет и проверку действий на уровне пользователей. В прошлом многие разработчики приложений, столкнувшись с необходимостью обеспечивать разное представление данных для различных пользователей, создавали совместно используемые учетные записи. Например, все сотрудники для доступа к кадровой информации использовали учетную запись EMPLOYEE;
все руководители Ч учетную запись MANAGER и т.д. Это не позволяло контролировать действия на уровне отдельных пользователей. Невозможно было понять, регистрировался ли пользователь TKYTE Ч в системе работало несколько сеансов от имени учетной записи EMPLOYEE (кто бы из сотрудников ни подключался). При желании можно использовать средства тщательного контроля доступа вместе с такими совместно используемыми учетными записями. Однако эти средства позволяют избежать необходимости создания и использования таких учетных записей.
Поддержка совместно используемых учетных записей Это дополнение к предыдущему разделу. Средства тщательного контроля доступа не требуют обязательного использования для каждого пользователя отдельной учетной за Глава писи;
они только позволяют это сделать. С помощью контекста приложения можно использовать средства тщательного контроля доступа и в "однопользовательской" среде, например, используемой при организации пула подключений сервера приложений. Некоторые пулы подключений требуют при регистрации использования одной учетной записи базы данных. Средства тщательного контроля доступа прекрасно работают и в таких средах.
Предоставление доступа к приложению как к службе Средства тщательного контроля доступа позволяют поставщику прикладных служб (Application Service Provider Ч ASP), не изменяя существующее приложение, предоставить к нему как к службе доступ множеству клиентов. Предположим, имеется приложение по учету кадров, доступ к средствам которого хотелось бы за деньги предоставлять клиентам по сети Internet. Поскольку клиентов предполагается много и все они требуют гарантий конфиденциальности данных, необходимо придумать способ защиты данных одного клиента от доступа других. Возможны следующие варианты: Х установить, сконфигурировать и поддерживать отдельные экземпляры базы данных для каждого клиента;
Х переписать все используемые приложением хранимые процедуры так, чтобы они работали с правами вызывающего (это будет описано в главе 23), и создать для каждого клиента отдельную схему;
Х использовать один экземпляр базы данных и одну схему со средствами тщательного контроля доступа. Первый вариант крайне нежелателен. Неизбежные расходы ресурсов на поддержку отдельного экземпляра базы данных для каждого клиента, имеющего всего десяток сотрудников, не позволяют реально его использовать. Для крупных клиентов с сотнями или тысячами пользователей он вполне оправдан. Для массы же мелких клиентов, каждый из которых добавляет пять-шесть конечных пользователей, создавать отдельную базу данных слишком расточительно. Второй вариант потенциально требует переписать приложение. Целью является создание для каждого клиента отдельной схемы со своим набором таблиц. Любую хранимую процедуру надо создавать так, чтобы она работала с таблицами, доступными только для текущего зарегистрированного пользователя (клиента). Обычно хранимым процедурам доступны те же объекты, что и создателю процедуры Ч надо только убедиться, что используются подпрограммы, работающие с правами вызывающего и что в приложении нигде явно не указаны схемы. Например, нельзя использовать оператор SELECT * FROM SCOTT.EMP - только SELECT * FROM ЕМР. Это касается не только PL/SQL-процедур. Для любого внешнего кода на языках Java или Visual Basic тоже должны соблюдаться эти правила (и не использоваться имена схем). Поэтому второй вариант также нежелателен, да еще и связан с необходимостью поддержки многих сотен пользовательских схем.
Тщательный контроль доступа Третий вариант Ч использование средств тщательного контроля доступа Ч наиболее безболезненный и простой. Можно, например, добавить в каждую таблицу, требующую защиты, столбец с идентификатором организации. Для поддержки значений в этом столбце надо использовать триггер (чтобы не пришлось изменять приложение). Триггер будет брать соответствующее значение из контекста приложения, устанавливаемого в системном триггере ON LOGON. Правила защиты будут задавать условие выбора строк только соответствующей организации. При этом можно ограничивать доступ к данным не только по идентификатору клиента, но и по любым другим необходимым условиям. В нашей системе кадрового учета добавлять можно не только условие WHERE COMPANY = ЗНАЧЕНИЕ, но и дополнительные условия, в зависимости от того, работает ли с системой рядовой сотрудник, руководитель или сотрудник одела кадров. Можно пойти еще дальше и добавить условия фрагментации, чтобы физически отделить данные крупных клиентов с целью обеспечения надежности хранения и высокой доступности.
Как реализованы средства тщательного контроля доступа Средства тщательного контроля доступа в Oracle 8i реализуются с помощью двух конструкций. Х Контекст приложения. Это пространство имен с соответствующим набором пар атрибут/значение. Например, в контексте OurApp можно обращаться к переменным DeptNo, Mgr и т.д. Контекст приложения всегда привязан к PL/SQL-пакету. Этот пакет Ч единственный метод установки значений в контексте. Чтобы установить значение атрибута DeptNo в нашем контексте OurApp, необходимо обратиться к соответствующему пакету, связанному с контекстом OurApp. Этот пакет может корректно устанавливать значения в контексте OurApp (вы сами его написали, поэтому и считается, что он будет правильно устанавливать значения в контексте). Это предотвращает установку значений в контексте приложений злонамеренными пользователями с целью получения несанкционированного доступа к информации. Читать значения в контексте приложения может кто угодно, но устанавливать их может только один пакет. Х Правила защиты. Это созданная разработчиком функция, возвращающая условие для динамического фильтрования данных при выполнении запроса. Эту функцию можно привязывать к определенной таблице или представлению, и вызываться она может для всех или только для некоторых операторов, обращающихся к таблице. Это означает, что можно задать одни правила для оператора SELECT, другие Ч для INSERT и третьи Ч для операторов UPDATE и DELETE. Обычно в этой функции используются значения атрибутов в контексте приложений для создания соответствующего условия (например, проверяется, кто зарегистрировался и что он пытается сделать и создается соответствующее подмножество строк для работы). Следует помнить, что для пользователя SYS (или INTERNAL) правила защиты никогда не применяются (соответствующие функции просто никогда не вызываются);
эти пользователи всегда могут читать и изменять все данные.
Х Глава Также следует упомянуть средства сервера Oracle 8i, расширяющие возможности средств тщательного контроля доступа. Функция SYS_CONTEXT. Эта функция используется в языках SQL и PL/SQL для доступа к значениям в контексте приложения. Подробное описание этой функции и список стандартных значений в автоматически устанавливаемом сервером Oracle контексте USERENV можно найти в руководстве Oracle SQL Reference. Из этого контекста можно получить имя пользователя, организовавшего сеанс, IPадрес клиента и другую полезную информацию.
Х Триггеры на событие регистрации в базе данных. Они позволяют выполнять любой код при регистрации пользователя в базе данных. Это очень удобно для настройки первоначального, стандартного контекста приложения. Х Пакет DBMS_RLS. Этот пакет обеспечивает функциональный интерфейс для добавления, удаления, изменения, включения и отключения правил защиты. Соответствующие подпрограммы можно вызвать из любого языка программирования или среды, из которых можно подключиться к СУБД Oracle. Чтобы использовать средства тщательного контроля доступа, разработчику, помимо стандартных ролей CONNECT и RESOURCE (или соответствующих им привилегий), необходимы следующие привилегии. Х EXECUTE_CATALOG_ROLE. Эта роль позволяет разработчику выполнять подпрограммы пакета DBMS_RLS. Достаточно также, подключившись как SYS, предоставить пользователю привилегию на выполнение пакета DBMS_RLS.
Х CREATE ANY CONTEXT. Эта привилегия позволяет разработчику создавать контексты приложений. Контекст приложения создается с помощью SQL-оператора: SQL> c r e a t e or replace context OurApp using Our_Context_Pkg;
Здесь OurApp Ч имя контекста, a Our_Context_Pkg Ч PL/SQL-пакет, которому разрешается устанавливать значения атрибутов контекста. При реализации средств тщательного контроля доступа контексты приложений существенны по двум причинам. Х Они обеспечивают надежный способ установки значений переменных в пространстве имен. Устанавливать значения в контексте можно только с помощью PL/SQLпакета, связанного с этим контекстом. Это гарантирует целостность значений в контексте. Поскольку контекст используется для ограничения или обеспечения доступа к данным, необходимо гарантировать целостность значений в контексте. Х Ссылки на значения атрибутов контекста в SQL-запросе обрабатываются как связываемые переменные. Например, если установить значение атрибута DeptNo в контексте OurApp и реализовать правило защиты, возвращающее конструкцию WHERE deptno = SYS_CONTEXT('OurApp','DeptNo'), соответствующий оператор не будет в разделяемом пуле уникальным, поскольку обращение к функции SYS_CONTEXT аналогично deptno = :bl. В сеансах можно будет задавать различные значения атрибута Deptno, но все они будут повторно использовать одни и те же оптимизированные планы запросов.
Тщательный контроль доступа Пример 1: Реализация правил защиты Чтобы продемонстрировать возможности средств тщательного контроля доступа, будут реализованы очень простые правила зашиты. Зададим следующие правила. Х Если текущий пользователь является владельцем таблицы (OWNER), он может обращаться ко всем ее строкам. Х В противном случае пользователь может обращаться только к строкам, у которых значение в столбце OWNER совпадает с его именем. Х Кроме того, добавлять можно только строки, в столбце OWNER которых указано имя обращающегося пользователя. Попытки добавить строку с другим значением в этом столбце отвергаются. Для реализации этих правил необходимо создать следующую PL/SQL-функцию: tkyte@TKYTE816> c r e a t e or replace 2 function security_policy_function(p_schema in varchar2, 3 p_object in varchar2) 4 return varchar2 5 as 6 begin 7 if (user = p_schema) then 8 return '';
9 else 10 return 'owner = USER';
11 end i f ;
12 e n d ;
13 / Function c r e a t e d. Этот пример показывает общую структуру функции, реализующей правила защиты. Эта функция всегда возвращает значение типа VARCHAR2. Возвращаемое значение представляет собой условие, которое будет добавлено к запросу. Фактически это условие будет добавляться к таблице или представлению, к которым применяется это правило защиты, с помощью подставляемого представления (inline view): 3anpoc: SELECT * FROM T Будет переписан к а к : SELECT * FROM (SELECT * FROM T WHERE owner = USER) или: SELECT * FROM (SELECT * FROM T) Кроме того, все функции, реализующие правила защиты, должны принимать два параметра в режиме IN: имя схемы, которой принадлежит объект, и имя объекта, к которому применяется функция. Их значения можно при необходимости использовать в функции, реализующей правила защиты. Итак, в нашем примере условие owner = USER будет динамически добавляться ко всем запросам к таблице, с которой связана эта функция, эффективно ограничивая множество строк, доступных пользователю. Пустое условие будет возвращаться только в том случае, если текущий зарегистрированный пользователь является владельцем таблицы. Вернуть пустое условие Ч то же самое, что вернуть условие 1=1 или True. Воз Глава врат значения Null равносилен возвращению пустого условия. В представленном выше примере вместо пустой строки можно было с тем же результатом возвращать Null. Чтобы связать функцию с таблицей, используется рассматриваемая далее PL/SQLпроцедура DBMS_RLS.ADD_POLICY. В нашем примере имеется следующая таблица, а сеанс выполняется от имени пользователя TKYTE: tkyte@TKYTE816> c r e a t e t a b l e data_table 2 (some_data varchar2(60), 3 OWNER varchar2(30) default USER 4) 5 / Table created. tkyte@TKYTE816> grant all on data_table to publicGrant succeeded. tkyte@TKYTE816> create public synonym data_table for data_table;
Synonym created. tkyte0TKYTE816> insert into data_table (some_data) values ('Некие данные');
1 row created. tkyte@TKYTE816> insert into data_table (some_data, owner) 2 values ('Некие данные, принадлежащие пользователю SCOTT', 'SCOTT');
1 row created. tkyte0TKYTE816> Commit c o m p l e t e. tkyte0TKYTE816> s e l e c t * from d a t a _ t a b l e ;
SOME_DATA Некие данные Данные, принадлежащие п о л ь з о в а т е л ю SCOTT OWNER TKYTE SCOTT commit;
Теперь привяжем написанную ранее функцию защиты к этой таблице с помощью следующего обращения к пакету DBMS_RLS: tkyte@TKYTE816> begin 2 dbms_rls.add_policy 3 (object_schema => 'TKYTE', 4 object_name => 'data_table', 5 policy_name => 'MY_POLICY', 6 function_schema => 'TKYTE', 7 policy_function => 'security_policy_function', 8 statement_types => 'select, insert, update, delete', 9 update_check => TRUE, 10 enable => TRUE 11 );
12 end;
13 / PL/SQL procedure successfully completed.
Тщательный контроль доступа Процедура ADD_POLICY Ч одна из ключевых процедур пакета DBMS_RLS. Именно она позволяет добавить правило зашиты для таблицы. Мы передали процедуре следующие параметры. Х OBJECT_SCHEMA. Имя владельца таблицы или представления. Если оставить стандартное значение Null, оно будет интерпретироваться как имя текущего зарегистрированного пользователя. Для полноты рассмотренного выше примера я передал имя пользователя. Х OBJECT_NAME. Имя таблицы или представления, для которого добавляется правило. Х POLICY_NAME. Уникальное имя для этого правила. Это имя используется в дальнейшем для включения, отключения, изменения или удаления правила. Х FUNCTION_SCHEMA. Имя владельца функции, возвращающей условие. Оно обрабатывается аналогично параметру OBJECT_SCHEMA. Если оставлено стандартное значение Null, используется имя текущего зарегистрированного пользователя. Х POLICY_FUNCTION. Имя функции, возвращающей условие. Х STATEMENT_TYPES. Список типов операторов, к которым применяется правило. Может представлять собой любое сочетание INSERT, UPDATE, SELECT и DELETE, перечисленных через запятую. Стандартное значение Ч все четыре оператора. Для наглядности я задал список явно. Х UPDATE_CHECK. Этот параметр влияет только на обработку операторов INSERT и UPDATE. Если параметр имеет значение True (стандартное значение Ч False), будет выполняться проверка, доступны ли вставленные или измененные данные текущему пользователю в соответствии с заданным условием. Другими словами, если задано значение True, нельзя вставить данные, которые нельзя будет выбрать из таблицы в соответствии с возвращаемым функцией условием. Х ENABLE. Задает, включено это правило или нет. Стандартное значение Ч True. Теперь, после вызова процедуры ADD_POLICY, ко всем операторам ЯМД, применяемым к таблице DATA_TABLE, будет добавляться условие, возвращаемое функцией SECURITY_POLICY_FUNCTION, независимо от того, из какой среды поступил оператор ЯМД. Другими словами, независимо от приложения, обращающегося к данным. Чтобы увидеть это в действии, выполним: tkyte@TKYTE816> connect system/manager system@TKYTE816> select * from data_table;
no rows selected system@TKYTE816> connect scott/tiger scott@TKYTE816> select * from data_table;
SOME_DATA Данные, принадлежащие пользователю SCOTT OWNER SCOTT Глава Итак, этот пример показывает, что строки фильтруются Ч пользователь SYSTEM не получает из этой таблицы никаких данных. Причина в том, что условие WHERE OWNER = USER не выполняется ни для одной из существующих строк данных. При регистрации от имени пользователя SCOTT, однако, можно получить единственную строку, принадлежащую пользователю SCOTT. Продолжим пример и попытаемся применить к таблице ряд операторов ЯМД: sys@TKYTE816> connect scott/tiger scott@TKYTE816> insert into data_table (some_data) 2 values ('Новые данные');
1 row created. scott@TKYTE816> insert into data_table (some_data, owner) 2 values ('Новые данные, принадлежащие пользователю SYS', 'SYS') 3/ insert into data_table ( some_data, owner ) * ERROR at line 1:
ORA-28115: policy with check option violation scott@TKYTE816> select * from data_table;
SOME_DATA Данные, принадлежащие п о л ь з о в а т е л ю SCOTT Новые данные OWNER SCOTT SCOTT Можно создавать данные, которые будут доступны, но если они недоступны, возвращается сообщение об ошибке ORA-28115, поскольку при добавлении правила в вызове процедуры dbms_rls.add_policy было передано значение: 9 update_check => TRUE);
Это аналогично созданию представления с конструкцией CHECK OPTION. Разрешается создавать только те данные, которые можно потом выбрать. По умолчанию можно создавать данные, не выбираемые в соответствии с правилами защиты. Теперь, в соответствии с реализованным правилом защиты, владелец таблицы видит все строки и имеет возможность создавать любую строку. Чтобы убедиться в этом, регистрируемся как пользователь TKYTE и пытаемся выполнить следующие действия: scott@TKYTE816> connect tkyte/tkyte tkyte@TKYTE816> insert into data_table (some_data, owner) 2 values ('Новые данные, принадлежащие пользователю SYS', 'SYS') 3/ 1 row created. tkyte@TKYTE816> select * from data_table 2/ Тщательный контроль доступа SOME_DATA Некие данные Данные, принадлежащие пользователю SCOTT Новые данные Новые данные, принадлежащие пользователю SYS OWNER TKYTE SCOTT SCOTT SYS Итак, пример показывает, что на пользователя TKYTE правило защиты не распространяется. Интересно отметить, что в случае регистрации от имени пользователя SYS наблюдается следующая особенность: tkyte@TKYTE816> Connected. SOME_DATA Некие д а н н ы е Данные, п р и н а д л е ж а щ и е п о л ь з о в а т е л ю SCOTT Новые д а н н ы е Новые д а н н ы е, п р и н а д л е ж а щ и е п о л ь з о в а т е л ю SYS connect sys/change_on_install sys@TKYTE816> s e l e c t * from d a t a _ t a b l e ;
OWNER TKYTE SCOTT SCOTT SYS Правила зашиты не применяются для специального пользователя SYS (а также при регистрации от имени INTERNAL или как SYSDBA). Это сделано специально. Учетные записи с привилегиями SYSDBA предназначены для решения задач администрирования и соответствующим пользователям доступны все данные. Это особенно важно учитывать при экспортировании данных. Если только экспортирование не выполняется с привилегиями SYSDBA, применяются правила защиты. При использовании учетной записи без привилегий SYSDBA и обычном экспорте вы получите не все данные!
Пример 2: Использование контекстов приложений В этом примере мы реализуем правила защиты информации о сотрудниках (для отдела кадров крупной компании). Будем использовать простые таблицы ЕМР и DEPT, принадлежащие пользователю SCOTT, и добавим таблицу с информацией о сотрудниках, которые отвечают за кадровые вопросы в том или ином отделе. При этом необходимо реализовать следующие правила защиты. Руководитель отдела может: Х просматривать свою собственную запись, а также записи для всех своих подчиненных;
Х изменять записи своих непосредственных подчиненных. Рядовой сотрудник может: Х просматривать свою собственную запись. Ответственный за кадровые вопросы в отделе может: Х читать все записи для отдела (предполагается, что ответственный за кадровые вопросы в определенный момент времени работает только в одном отделе);
1450 Глава Х изменять все записи для отдела;
Х вставлять записи для соответствующего отдела;
Х удалять записи для соответствующего отдела. Как было сказано, приложение будет использовать копии существующих таблиц ЕМР и DEPT из схемы пользователя SCOTT и дополнительную таблицу, HR_REPS, позволяющую назначить ответственного за кадровые вопросы в отделе. При регистрации желательно автоматически назначать пользователю соответствующую роль в приложении. Так что, например, при регистрации ответственного за кадровые вопросы он сразу бы получал соответствующую роль в приложении. Для начала необходимо создать в базе данных ряд учетных записей. Эти учетные записи создаются для владельца приложения и пользователей. В данном случае владелец приложения Ч пользователь TKYTE, и соответствующая схема будет содержать копии таблиц ЕМР и DEPT из демонстрационной схемы SCOTT. Пользователи именуются так же, как сотрудники в таблице EMP (KING, BLAKE и т.д.). Для создания и настройки всех этих учетных записей использовался следующий сценарий. Сначала удаляем и пересоздаем пользователя TKYTE, предоставляя ему роли CONNECT и RESOURCE: sys@TKYTE816> User dropped. sys@TKXTE816> create user tkyte identified by tkyte 2 default tablespace data 3 temporary tablespace temp;
User created. sys@TKYTE816> grant connect, resource to tkyte;
Grant succeeded. Теперь предоставим пользователю минимальные привилегии, необходимые для организации тщательного контроля доступа. Вместо привилегии EXECUTE ON DBMS_RLS можно предоставить роль EXECUTE_CATALOG: sys@TKYTE816> grant execute on dbms_rls to tkyte;
Grant succeeded. sys@TKYTE816> grant create any context to tkyte;
Grant succeeded. Следующая привилегия необходима для создания триггера на событие регистрации в базе данных, который надо будет создать в дальнейшем: sys@TKYTE816> grant administer database trigger to tkyte;
Grant succeeded. Теперь создадим учетные записи сотрудников и руководителей для пользователей приложения. Для каждого сотрудника в таблице ЕМР (кроме SCOTT) будет создана учетная запись, имя которой совпадает со значением в столбце ENAME. Во многих базах данных учетная запись SCOTT уже существует: drop user t k y t e cascade;
Тщательный контроль доступа sys@TKYTE816> begin 2 for x in (select ename 3 from scott.emp where ename <> 'SCOTT') 4 loop 5 execute immediate 'grant connect to ' || x.ename || 6 ' identified by ' || x.ename;
7 end loop;
8 end;
9 / PL/SQL procedure successfully completed. sys@TKYTE816> connect scott/tiger scott@TKYTE816> grant select on emp to tkyte;
Grant succeeded. scott@TKYTE816> grant select on dept to tkyte;
Grant succeeded. Приложением используется следующая простая схема. Сначала копируются таблицы ЕМР и DEPT из схемы пользователя SCOTT. Для этих таблиц мы также добавим декларативные требования целостности ссылок: scott@TKYTE816> connect tkyte/tkyte tkyte@TKYTE816> create table dept as select * from scott.dept;
Table created. tkyte@TKYTE816> alter table dept add constraint dept_pk primary key(deptno);
Table altered. tkyte@TKYTE816> create table emp_base_table as select * from scott.emp;
Table created. tkyte@TKYTE816> alter table emp_base_table add constraint 2 emp_pk primary key(empno);
Table altered. tkyte@TKYTE816> alter table emp_base_table add constraint emp_fk_to_dept 2 foreign key (deptno) references dept(deptno);
Table altered. Теперь добавим несколько индексов и требований. Создадим индексы, которые будут использоваться функциями контекста приложения для повышения их производительности. Мы должны иметь возможность быстро определять, является ли данный пользователь руководителем отдела:
tkyte@TKYTE816> Index created.
create index emp_mgr_deptno_idx on emp_base_table(mgr);
Необходимо также быстро получать по имени пользователя значение EMPNO и обеспечить уникальность имен пользователей в приложении:
Глава tkyte@TKYTE816> a l t e r t a b l e emp_base_table 2 add constraint 3 emp_ename_unique unique(ename);
Table altered. Теперь создадим представление ЕМР для таблицы EMP_BASE_TABLE. Правила защиты будут задаваться для этого представления, а приложение будет обращаться к нему для получения, вставки, изменения и удаления данных. Зачем используется представление, я объясню чуть позже: tkyte@TKYTE816> create view emp as select * f r o m emp_base_table;
View created. Теперь создадим таблицу для хранения информации о сотрудниках, ответственных за кадровые вопросы в отделах. Для этого используем таблицу, организованную по индексу. Поскольку к таблице будет выполняться единственный запрос, SELECT * FROM HR_REPS WHERE USERNAME = :X AND DEPTNO = :Y, таблица традиционной структуры нам не нужна: tkyte@TKYTE816> create table hr_reps 2 (username varchar2(30), 3 deptno number, 4 primary key(username,deptno) 5) 6 organization index;
Table created. Теперь зададим ответственных за кадровые вопросы в отделах: tkyte@TKYTE816> insert into hr_reps values ('KING', 10);
1 row created. tkyte@TKYTE816> insert into hr_reps values( 'KING', 20);
1 row created. tkyte@TKYTE816> insert into hr_reps values ('KING', 30);
1 row created. tkyte@TKYTE816> insert into hr_reps values ('BLAKE', 10);
1 row created. tkyte@TKYTE816> insert into hr_reps values ('BLAKE', 20);
1 row created. tkyte@TKYTE816> commit;
Commit complete. Теперь, когда таблицы приложения, ЕМР, DEPT и HR_REPS, созданы, создадим процедуру, которая позволит устанавливать контекст приложения. Он будет содержать три атрибута: номер (EMPNO) текущего зарегистрированного пользователя, имя пользователя (USERNAME) и роль пользователя в приложении (ЕМР, MGR или HR_REP). При создании конструкции WHERE для выполняемых пользователем операторов фун Тщательный контроль доступа кция, динамически формирующая условие, будет использовать роль, хранящуюся в контексте приложения. Для определения роли используется информация из таблиц EMP_BASE_TABLE и HR_REPS. Вот вам и ответ на вопрос, зачем создавать таблицу EMP_BASE_TABLE и отдельное представление ЕМР как SELECT * FROM EMP_BASE_TABLE. Для этого имеются две причины: Х данные в таблице сотрудников используются для реализации правил защиты;
Х данные из этой таблицы считываются при попытке задать контекст приложения. Для чтения данных о сотрудниках необходимо настроить контекст приложения, но для настройки контекста приложения необходимо получить данные о сотрудниках. Это проблема определения, что первично: курица или яйцо? Мы создадим представление (ЕМР), к которому будут обращаться все приложения, и обеспечим защиту этого представления. Исходная таблица EMP_BASE_TABLE будет использоваться функцией, реализующей правила защиты. По таблице EMP_BASE_TABLE можно определить, кто является руководителем отдела и кто у него в подчинении. Приложения и пользователи никогда не будут обращаться к таблице EMP_BASE_TABLE Ч только функция, реализующая правила защиты. Для этого мы не будем предоставлять другим пользователям прав доступа к базовой таблице;
при этом невозможность работы с ней обеспечит сервер. В этом примере мы выбрали автоматическую установку контекста приложения при регистрации. Это стандартная процедура, если ее можно использовать, для автоматической настройки контекста приложения. Иногда этого недостаточно. Если при регистрации не хватает информации для определения того, каким должен быть контекст, придется установить его атрибуты вручную с помощью вызова соответствующей процедуры. Это часто приходится делать при использовании сервера приложений, подключающего всех пользователей к базе данных через одну совместно используемую учетную запись. Сервер приложений вызовет процедуру базы данных, передав ей имя "реального" пользователя для правильной настройки контекста. Ниже представлена написанная нами процедура для установки значений атрибутов контекста. Мы знаем все особенности ее работы, поэтому можем ей доверять. Она позволяет реализовать необходимые правила защиты, устанавливая в контексте только соответствующее имя пользователя, название роли и номер сотрудника. В дальнейшем при обращении к этим значениям можно быть уверенными в том, что они заданы правильно и безопасно. Процедура будет автоматически выполняться триггером ON LOGON. B такой реализации процедура позволяет поддерживать З-уровневые приложения, использующие пул подключений и всего одну учетную запись в базе данных. Необходимо предоставить право на выполнение этой процедуры учетной записи, используемой пулом подключений, а затем на сервере приложений выполнять эту процедуру, причем не используя стандартное значение Ч имя текущего пользователя, подключившегося к базе данных, а передавая имя пользователя как параметр. tkyte@TKYTE816> create or replace 2 procedure set_app_role(p_username in varchar2 3 default sys_context('userenv','session_user')) 4 as Глава 5 l_empno number;
6 l_cnt number;
7 1 ctx varchar2(255) default 'Hr_App_Ctx';
8 begin 9 dbms_session.set_context(l_ctx, 'UserName', p_username);
10 begin 11 select empno into l_empno 12 from emp_base_table 13 where ename = p_username;
14 dbms_session.set_context(l_ctx, 'Empno', l_empno);
15 exception 16 When NO_DATA_FOUND then 17 Ч Пользователя нет в таблице emp - это, должно быть, -> ответственный за кадры. 18 NULL;
19 end;
20 21 22 Ч Сначала посмотрим, не является ли этот пользователь -> ответственным за кадры;
если нет 23 Ч вдруг это руководитель;
в противном случае устанавливаем -> пользователю роль ЕМР. 24 25 select count(*) into l_cnt 26 from dual 27 where exists 28 (select NULL 29 from hr_reps 30 where username = p_username 31 );
32 33 if (l_cnt о 0) 34 then 35 dbms_session.set_context(l_ctx, 'RoleName', 'HR_REP');
36 else 37 Ч Проверим, не является ли пользователь руководителем. 38 Ч Если нет, он получает роль ЕМР. 39 40 select count(*) into l_cnt 41 from dual 42 where exists 43 (select NULL 44 from emp_base_table 45 where mgr = to_number(sys_context(l_ctx,'Empno')) 46 );
47 if (l_cnt о 0) 48 then 49 dbms_session.set_context(l_ctx, 'RoleName', 'MGR');
50 else 51 Ч Роль ЕМР может получить каждый. 52 dbms_session.set_context(l_ctx, 'RoleName', 'ЕМР') ;
53 end if;
Тщательный контроль доступа 54 55 end if;
end;
/ Procedure created. Создадим контекст приложения. Контекст будет иметь имя HR_APP_CTX (совпадающее с именем предыдущей процедуры). Создавая контекст, обратите внимание, как он привязывается к только что созданной процедуре, Ч только она сможет устанавливать значения атрибутов в этом контексте: tkyte@TKYTE816> 2/ Context c r e a t e d. Чтобы автоматизировать настройку контекста, используем триггер базы данных на событие регистрации, в котором будет вызываться процедура, устанавливающая значения контекста: tkyte@TKYTE816> c r e a t e or replace t r i g g e r APP_LOGON_TRIGGER 2 a f t e r logon on database 3 begin 4 set_app_role;
5 end;
6/ Trigger c r e a t e d. Итак, мы создали процедуру, задающую роль для текущего зарегистрированного пользователя. Эта процедура будет вызываться не более одного раза за сеанс, гарантируя, что атрибут RoleName однократно получает значение при регистрации, которое затем не изменяется. Поскольку в зависимости от значения RoleName функция, реализующая правила защиты, будет возвращать различные значения, в версиях Oracle 8.1.6 и 8.1.6 нельзя разрешать пользователям изменять свою роль после установки. В противном случае возникнет потенциальная проблема с кэшированными курсорами и "старыми" условиями (см. описание соответствующей проблемы в разделе "Проблемы" далее в этой главе;
в версии 8.1.7 проблема в основном решена). Кроме того, мы находим значение EMPNO для текущего пользователя. Это дает нам два преимущества. Х Возможность проверить, является ли пользователь сотрудником. Получение сообщения об ошибке NO_DATA_FOUND позволяет судить, что подключившийся пользователь не является сотрудником. Поскольку его атрибут EMPNO не получает значения, этот пользователь не увидит данных, если только не является ответственным за кадры. Х Часто используемое значение помещается в контекст приложения. Теперь можно быстро обратиться к таблице ЕМР по значению EMPNO для текущего пользователя, что мы и будем делать далее в функции, формирующей условия. Затем мы создали объект (контекст приложения) и связали его с созданной ранее процедурой SET_APP_ROLE. В результате только эта процедура может устанавливать значения в данном контексте. Вот почему контекст приложения можно безопасно использовать и доверять получаемым результатам. Мы точно знаем, какой фрагмент кода может c r e a t e or replace context Hr_App_Ctx using SET_APP_ROLE Глава устанавливать значения в контексте, и мы уверены, что они устанавливаются правильно (ведь мы же сами написали эту процедуру). Следуюший пример показывает, что произойдет при попытке установить значения в контексте из другой процедуры: tkyte@TKYTE816> begin 2 dbms_session.set_context('Hr_App_ctx', 3 'RoleName', 'MGR');
4 end;
5/ begin * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 58 ORA-06512: at line 2 Чтобы проверить логику работы процедуры, попытаемся использовать ее от имени различных пользователей и посмотрим, какие роли мы можем устанавливать и какие значения устанавливаются в контексте. Начнем с пользователя SMITH. Это рядовой сотрудник. Он никем не руководит и не отвечает за кадры. Для получения значений, установленных в контексте, воспользуемся общедоступным представлением SESSION_CONTEXT: tkyte@TKYTE816> connect smith/smith smith@TKYTE816> column namespace format a10 smith@TKYTE816> column attribute format a10 smith@TKYTE816> column value format a10 smith@TKYTE816> select * from session_context;
NAMESPACE ATTRIBUTE VALUE HR_APP_CTX ROLENAME HR_APP_CTX USERNAME HR_APP_CTX EMPNO EMP SMITH Как видите, все работает, как и предполагалось. Пользователь SMITH успешно получил соответствующие значения атрибутов USERNAME, EMPNO и ROLENAME в контексте HR_APP_CTX. Подключившись от имени другого пользователя, мы видим, как работает процедура, попутно используя другой способ проверки значений в контексте приложения: smith@TKYTE816> connect blake/blake blake@TKYTE816> d e c l a r e 2 l_AppCtx dbms_session.AppCtxTabTyp;
3 l_size number;
4 begin 5 dbms_session.list_context(l_AppCtx, l _ s i z e ) ;
6 for i in 1.. l_size loop 7 dbms_output.put(l_AppCtx(i).namespace | | '. ' ) ;
8 dbms_output.put(l_AppCtx(i).attribute || ' = ' ' ) ;
9 dbms_output.put_line(l_AppCtx(i).value);
10 end loop;
11 end;
Тщательный контроль доступа 12 / HR_APP_CTX.ROLENAME = HR_REP HR_APP_CTX.USERNAME = BLAKE HR_APP_CTX.EMPNO = 7698 PL/SQL p r o c e d u r e successfully completed. На этот раз мы зарегистрировались как пользователь BLAKE, который является руководителем отдела 30 и ответственным за кадры в отделах 10 и 30. После регистрации видно, что контекст установлен правильно: для пользователя установлена роль HR_REP, имя пользователя и номер сотрудника. При этом показано, как получить пары атрибут/ значение из контекста сеанса с помощью процедуры DMBS_SESSION.LIST_CONTEXT. Этот пакет общедоступен, поэтому все пользователи смогут проверять значения атрибутов своего контекста с помощью этого метода, в дополнение к рассмотренному ранее представлению SESSION_CONTEXT. Убедившись, что контекст сеанса устанавливается так, как предполагалось, можно переходить к созданию функций, реализующих правила защиты. Эти функции будут вызываться сервером во время выполнения для динамического добавления условия к операторам. Такие динамически формируемые условия ограничивают множество данных, которые пользователь может читать или записывать. Мы создадим отдельные функции для операторов SELECT, операторов UPDATE и операторов INSERT/DELETE. Дело в том, что каждый из этих операторов может обращаться к различным подмножествам строк. Выбирать можно больше данных, чем изменять (например, сотрудник может просматривать свою запись, но не может ее менять). Только специально назначенные пользователи могут вставлять и удалять строки, поэтому условия для этих операторов тоже отличаются:
blake@TKYTE816> connect tkyte/tkyte tkyte@TKYTE816> create or replace package hr_predicate_pkg 2 as 3 4 5 6 7 8 9 10 function select_function(p_schema in varchar2, p_object in varchar2) return varchar2;
function update_function(p_schema in varchar2, p_object in varchar2) return varchar2;
function insert_delete_function(p_schema in varchar2, p_object in varchar2) return varchar2;
11 end;
12 / Package created.
Ниже представлена реализация пакета HR_PREDICATE_PKG. Начнем с глобальных переменных: tkyte@TKYTE816> c r e a t e or replace package body hr_predicate_pkg 2 as 3 4 g_app_ctx constant varchar2(30) d e f a u l t 'Hr_App_Ctx';
Глава 6 g_sel_pred varchar2(1024) default NULL;
7 g_upd_pred varchar2(1024) default NULL;
8 g_ins_del_pred varchar2(1024) default NULL;
9 Константа G_APP_CTX содержит имя контекста приложения. Если когда-либо потребуется переименовать контекст, можно изменить значение этой, используемой в ocтальномкоде константы. Если придется это имя менять, достаточно будет поменять значение константы в одном месте и перекомпилировать тело пакета. Остальные три глобальные переменные будут содержать условия. Этот пример создавался для версии Oracle 8.1.6. В этой версии есть проблема, связанная с кэшированием курсоров и средствами тщательного контроля доступа (подробнее см. в разделе "Проблемы" далее). Начиная с версии Oracle 8.1.7, этот прием программирования использовать необязательно. В данном случае реализуется правило, запрещающее менять роль после регистрации. Однажды сгенерированные в сеансе условия возвращаются для всех запросов. Мы не будем заново генерировать их для каждого запроса, так что любые изменения роли не повлияют на результат, пока пользователь не завершит сеанс и не зарегистрируется снова (или не сбросит состояние сеанса с помощью вызова DBMS_SESSION.RESET_PACKAGE). Теперь перейдем к первой из генерирующих условие функций. Она генерирует условие для операторов SELECT, которые обращаются к представлению ЕМР. Обратите внимание, что она просто устанавливает значение глобальной переменной пакета G_SEL_PRED (Global SELect PREDicate Ч глобальное условие для SELECT) в зависимости от значения атрибута контекста RoleName. Если атрибут контекста не установлен, функция возбуждает исключительную ситуацию, которая приводит к неудачному завершению запроса: 10 11 function select_function(p_schema in varchar2, 12 p_object in varchar2) return varchar2 13 is 14 begin 15 16 if (g_sel_pred is NULL) 17 then 18 if (sys_context(g_app_ctx, 'RoleName') = 'EMP') 19 then 20 g_sel_pred:= 21 ' empno=sys_context ( ' || g_app_ctx || ''',''EmpNo '')';
'' 22 elsif (sys_context(g_app_ctx, 'RoleName') = 'MGR') 23 then 24 g_sel_pred := 25 'empno in (select empno 26 from emp_base_table 27 start with empno = 28 sys_context('''||g_app_ctx||''',''EmpNo'') 29 connect by prior empno = mgr)';
30 31 e l s i f (sys_context(g_app_ctx, 'RoleName') = 'HR_REP') Тщательный контроль доступа 32 then 33 g_sel_pred := 'deptno in 34 (select deptno 35 from hr_reps 36 where username = 37 sys_context('''||g_app_ctx||''',''UserName''))';
38 39 else 40 raise_application_error(-20005, 'Роль не установлена');
41 end if;
42 end if;
43 44 return g_sel_pred;
45 end;
46 Теперь перейдем к функции, возвращающей условие для операторов изменения. Алгоритм ее очень похож на алгоритм предыдущей функции, но условие возвращается другое. Обратите на использование условия 1=0, например, если атрибут RoleName имеет значение ЕМР. Рядовые сотрудники ничего изменять не могут. Руководители могут изменять записи своих подчиненных (но не собственную запись). Ответственные за кадры могут изменять записи всех сотрудников отдела, которым они занимаются: 47 function update_function(p_schema in varchar2, 48 p_object in varchar2) return varchar2 49 is 50 begin 51 if (g_upd_pred is NULL) 52 then 53 if (sys_context(g_app_ctx, 'RoleName') = 'EMP') 54 then 55 g_upd_pred := 'l=0';
56 57 elsif (sys_context(g_app_ctx, 'RoleName') = 'MGR') 58 then 59 g_upd_pred := 60 ' empno in (select empno 61 from emp_base_table 62 where mgr = 63 sys_context('''||g_app_ctx|| 64 ''',"EmpNo''))';
65 66 elsif (sys_context(g_app_ctx, 'RoleName') = 'HR_REP') 67 then 68 g_upd_pred := 'deptno in 69 (select deptno 70 from hr_reps 71 where username = 72 sys_context('''||g_app_ctx||''',''UserName''))';
73 74 else 75 raise_application_error(-20005, 'Роль не установлена') Глава 76 end if;
77 end if;
78 79 return g_upd_pred;
80 end;
Наконец, рассмотрим функцию для операторов INSERT и DELETE. B этом случае условие 1=0 возвращается для пользователей с ролями ЕМР и MGR: никто из них не может создавать и удалять записи Ч это прерогатива ответственных за кадры (пользователей с ролью HR_REPS): 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 function insert_delete_function(p_schema in varchar2, p_object in varchar2) return varchar2 is begin if (g_ins_del_pred is NULL) then if (sys_context(g_app_ctx, 'RoleName' ) in ( 'EMP', 'HGR')) then g_ins_del_pred := 'l=0';
elsif (sys_context(g_app_ctx, 'RoleName') = 'HR_REP') then g_ins_del_pred := 'deptno in (select deptno from hr_reps where username = sys_context('''||g_app_ctx ||''',''UserName''))' ;
else raise_application_error(-20005, 'Роль не установлена');
end if;
end if;
return g_ins_del_pred;
end;
end;
/ Package body created. До появления средств тщательного контроля доступа обеспечить применение этих трех условий при работе с одной таблицей можно было только за счет использования многочисленных представлений Ч по одному для операторов SELECT, UPDATE и INSERT/DELETE для каждой роли. Средства тщательного контроля доступа позволяют создать всего одно представление с динамически формируемыми условиями. Последний шаг в решении задачи Ч связывание условий с каждой операцией ЯМД и представлением ЕМР. Это делается следующим образом: tkyte@TKYTE816> begin 2 dhms_rls. add_policy 3 (object_name => 'EMP', 4 policy_name => 'HR_APP_SELECT_POLICY', 5 p o l i c y _ f u n c t i o n => 'HR_PREDICATE_PKG.SELECT_FUNCTION', Тщательный контроль доступа 6 statement_types => ' s e l e c t ' ) ;
7 end;
8/ PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 dhms_rls.add_policy 3 (object_name => 'EMP', 4 policy_name => 'HR_APP_UPDATE_POLICY', 5 policy_function => 'HR_PREDICATE_PKG.UPDATE_FONCTION', 6 statement_types => 'update', 7 update_check => TRUE);
8 end;
9/ PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 dbms_rls.add_policy 3 (object_name => 'EMP', 4 policy_name -> 'HR_APP_INSERT_DELETE_POLICY', 5 policy_function => 'HR_PREDICATE_PKG.INSERT_DELETE_FDNCTION', 6 statement_types => 'insert, delete', 7 update_check => TRUE);
8 end;
9/ PL/SQL procedure successfully completed. Итак, с каждой из операций ЯМД мы связали функцию, возвращающую условие. Когда пользователь обращается с запросом к представлению ЕМР, используется условие, генерируемое функцией HR_PREDICATE_PKG.SELECT_FUNCTION. При изменении данных будет вызвана функция UPDATE_FUNCTION этого пакета и т.д. Теперь протестируем приложение. Мы создадим пакет HR_APP. Этот пакет будет выступать в роли приложения. В нем есть подпрограммы для: Х выборки данных (процедура listEmps);
Х изменения данных (процедура updateSal);
Х удаления данных (процедура deleteAll);
Х вставки новых данных (процедура insertNew). Мы будем регистрироваться от имени различных пользователей, с разными ролями и контролировать работу приложения. В результате будет продемонстрировано, как работают средства тщательного контроля доступа. Вот спецификация приложения: tkyte@TKYTE816> create or replace package hr_app 2 as 3 procedure listEmps;
4 5 procedure updateSal;
Глава 7 procedure deleteAll;
8 9 procedure insertNew(p_deptno in number);
10 end;
11 / Package created. Теперь перейдем к телу пакета. Этот пример несколько надуманный, поскольку процедура, выполняющая UPDATE, пытается изменить все возможные строки, задав всем одно и то же значение. Это сделано для того, чтобы можно было точно увидеть, сколько и какие строки затрагиваются. Другие процедуры, по сути, похожи Ч они сообщают, что сделали и сколько строк обработано: tkyte@TKYTE816> create or replace package body hr_app 2 as 3 4 procedure listEmps 5 as 6 l_cnt number default 0;
7 begin 8 dbms_output.put_line 9 (rpad('ename',10) || rpad('sal', 6 ) || ' ' || 10 rpad('dname',10) || rpad('mgr',5) || ' ' || 11 rpad('dno',3));
12 for x in (select ename, sal, dname, mgr, emp.deptno 13 from emp, dept 14 where emp.deptno = dept.deptno) 15 loop 16 dbms_output.put_line(rpad(nvl(x.ename,'(null)'),10) || 17 to_char(x.sal,'9,999') II ' ' || 18 rpad(x.dname,10) || 19 to_char(x.mgr,'9999') || ' ' || 20 to_char(x.deptno,'99'));
21 l_cnt := l_cnt + 1;
22 end loop;
23 dbms_output.put_line(l_cnt || ' строк(и) выбрано');
24 end;
25 26 27 procedure updateSal 28 is 29 begin 30 update emp set sal = 9999;
31 dbms_output.put_line(sql%rowcount || ' строк(и) изменено');
32 end;
33 34 procedure deleteAll 35 is 36 begin 37 delete from emp where empno <> sys_context('Hr_app_Ctx','EMPNO');
38 dbms_output.put_line(sql%rowcount || ' строк(и) удалено');
Тщательный контроль доступа 39 end;
40 41 procedure insertNew(p_deptno in number) 42 as 43 begin 44 insert into emp (empno, deptno, sal) values (123, p_deptno, 1111);
45 end;
46 47 end hr_app;
48 / Package body created. tkyte@TKYTE816> 2/ Grant succeeded. Вот и все наше "приложение". Процедура listEmps выдает все записи, доступные в представлении EMP. Процедура updateSal изменяет все записи, которые разрешено изменять. Процедура deleteAll удаляет все записи, которые разрешено удалять, за исключением записи текущего пользователя. Процедура insertNew пытается создать новую запись о сотруднике указанного отдела. Это приложение просто проверяет выполнение всех возможных операций ЯМД с представлением ЕМР (как я уже писал, приложение это весьма надуманное). Теперь, регистрируясь от имени различных пользователей, проверим работу приложения. Сначала регистрируемся и просматриваем значения атрибутов в контексте приложения: tkyte@TKYTE816> connect adams/adams adams@TKYTE816> column namespace format a10 adams@TKYTE816> column attribute format a10 adams@TKYTE816> column value format a10 adams@TKYTE816> select * from session_context;
NAMESPACE ATTRIBUTE VALUE HR_APP_CTX ROLENAME HR_APP_CTX USERNAME HR_APP_CTX EMPNO EMP ADAMS 7876 grant execute on hr_app to p u b l i c adams@TKYTE816> set serveroutput on Итак, поскольку мы зарегистрировались как обычный сотрудник, процедура listEmps должна выдать только одну запись Ч для этого сотрудника: adams@TKYTE816> exec tkyte.hr_app.listEmps ename sal dname mgr dno ADAMS 1,100 RESEARCH 7788 20 1 строк(и) выбрано PL/SQL procedure successfully completed. Поскольку мы выступаем в качестве обычного сотрудника, права изменять и удалять записи у нас быть не должно. Проверим:
1464 Глава adams@TKYTE816> exec tkyte.hr_app.updateSal 0 строк(и) изменено PL/SQL procedure successfully completed. adams@TKYTE816> exec tkyte.hr_app.deleteAll 0 строк(и) удалено PL/SQL procedure successfully completed. Наконец, проверим возможность выполнения оператора INSERT. B данном случае сервер выдаст сообщение об ошибке. В нашем примере для операторов UPDATE и DELETE ничего подобного не случилось. Попытки выполнить UPDATE или DELETE завершились успешно, потому что пользователю просто не дали данных, которые можно было бы изменить или удалить. При попытке вставки, однако, строка создается, нарушает правила зашиты, и удаляется. В этом случае сервер выдает сообщение об ошибке: adams@TKYTE816> exec tkyte.hr_app.insertNew(20);
BEGIN tkyte.hr_app.insertNew(20);
END;
* ERROR at line 1: ORA-28115: policy with check option violation ORA-06512: at "TKYTE.HR_APP", line 36 ORA-06512: at line 1 Итак, мы убедились, что пользователь может просматривать только собственную запись. Попытки изменять данные любым способом, удалять и вставлять строки завершаются неудачно. Именно это и предполагалось, и обеспечивается автоматически. Приложение, пакет HR_APP, не содержит кода, обеспечивающего реализацию этих правил. Все они автоматически реализуются сервером, с момента начала и до завершения сеанса, независимо от того, какая среда или инструментальное средство использовано для подключения. Теперь зарегистрируемся в качестве руководителя и посмотрим, что получится. Для начала снова получим значения атрибутов контекста, а затем Ч список сотрудников, записи которых доступны: adams@TKYTE816> @connect jones/jones jones@TKYTE816> set serveroutput on jones@TKYTE816> select * from session_context;
NAMESPACE ATTRIBUTE VALUE HR_APP_CTX ROLENAME HR_APP_CTX USERNAME HR_APP_CTX EMPNO MGR JONES jones@TKYTE816> exec tkyte.hr_app.listEmps ename sal dname mgr dno SMITH 800 RESEARCH 7902 20 JONES 2,975 RESEARCH 7839 20 SCOTT 9,999 RESEARCH 7566 Тщательный контроль доступа ADAMS 1,100 RESEARCH 7788 20 FORD 3,000 RESEARCH 7566 20 5 строк(и) выбрано PL/SQL procedure successfully completed.
Как видите, на этот раз мы получили из представления ЕМР несколько записей. Получены записи для всех сотрудников отдела 20 (пользователь JONES является его руководителем, в соответствии с информацией в представлении ЕМР). Теперь выполним процедуру изменения (updateSal) и проверим, какие изменения сделаны: jones@TKYTE816> exec tkyte.hr_app.updateSal 2 rows updated PL/SQL procedure successfully completed. jones@TKYTE816> exec tkyte.hr_app.listEmps ename sal dname mgr dno SMITH 800 RESEARCH 7902 20 JONES 2,975 RESEARCH 7839 20 SCOTT 9,999 RESEARCH 7566 20 AA S DM 1,100 RESEARCH 7788 20 FR OD 9,999 RESEARCH 7566 20 5 строк(и) выбрано Предполагалось, что изменять можно только записи непосредственных подчиненных. Изменение затронуло только две записи для непосредственных подчиненных пользователя JONES. Теперь попытаемся выполнить удаление и вставку. Поскольку пользователь получил роль MGR, а не HR_REP, мы не сможем удалять записи, а при выполнении оператора INSERT будет получено сообщение об ошибке: jones@TKYTE816> exec tkyte.hr_app.deleteAll 0 строк(и) удалено PL/SQL procedure successfully completed. jones@TKYTE816> exec tkyte.hr_app.insertNew(20) BEGIN tkyte.hr_app.insertNew(20);
END;
* ERROR at line 1: ORA-28115: policy with check option violation ORA-06512: at "TKYTE.HR_APP", line 44 ORA-06512: at line 1 Итак, руководитель может следующее. Х Просматривать не только свои собственные данные. Руководитель получает информацию обо всех своих подчиненных. Х Изменять данные. В частности, можно изменять только записи для непосредственных подчиненных, что и требовалось. Х Как и требовалось, ничего удалять и вставлять руководитель не может. Теперь зарегистрируемся в качестве ответственного за кадры (пользователь с ролью HR_REP) и проверим, что позволяет сделать приложение от имени этой роли. Снова Глава начнем с выдачи информации о контексте приложения и списка доступных для чтения строк. На этот раз выдается вся таблица ЕМР, поскольку пользователь KING имеет доступ к информации по всем трем отделам: jones@TKYTE816> connect king/king king@TKYTE816> select * from session_context;
NAMESPACE ATTRIBUTE VALUE HR_REP KING HR_APP_CTX ROLENAME HR_APP_CTX USERNAME HR_APP_CTX EMPNO king@TKYTE816> exec tkyte.hr_app.listEmps ename sal dname mgr dno CLARK 2,450 ACCOUNTING 7839 10 KING 5,000 ACCOUNTING 10 MILLER 1,300 ACCOUNTING 7782 10 SMITH 800 RESEARCH 7902 20 JONES 2,975 RESEARCH 7839 20 SCOTT 9,999 RESEARCH 7566 20 ADAMS 1,100 RESEARCH 7788 20 FORD 9,999 RESEARCH 7566 20 ALLEN 1,600 SALES 7698 30 HARD 1,250 SALES 7698 30 MARTIN 1,250 SALES 7698 30 BLAKE 2,850 SALES 7839 30 TURNER 1,500 SALES 7698 30 JAMES 950 SALES 7698 30 14 строк(и) выбрано PL/SQL procedure successfully completed. Теперь выполним изменение и посмотрим, какие данные можно изменить. В данном случае изменены все строки: king@TKYTE816> exec tkyte.hr_app.updateSal 14 строк(и) изменено PL/SQL procedure successfully completed. king@TKYTE816> exec tkyte.hr_app.listEmps ename sal dname mgr dno CLARK 9,999 ACCOUNTING 7839 10 KING 9,999 ACCOUNTING 10 MILLER 9,999 ACCOUNTING 7782 10 SMITH 9,999 RESEARCH 7902 20 JONES 9,999 RESEARCH 7839 20 SCOTT 9,999 RESEARCH 7566 20 ADAMS 9,999 RESEARCH 7788 20 FORD 9,999 RESEARCH 7566 20 ALLEN 9,999 SALES 7698 30 WARD 9,999 SALES 7698 30 MARTIN 9,999 SALES 7698 30 BLAKE 9,999 SALES 7839 Тщательный контроль доступа TURNER 9,999 SALES JAMES 9,999 SALES 14 строк(и) выбрано 7698 7698 30 PL/SQL procedure successfully completed. Значение 9,999 в столбце SAL доказывает, что изменены все строки таблицы. Теперь попробуем выполнить удаление. Процедура DeleteAll создавалась так, чтобы она не могла удалять запись текущего зарегистрированного пользователя ни при каких условиях: king@TKYTE816> exec tkyte.hr_app.deleteAll 13 строк(и) удалено PL/SQL procedure successfully completed. Впервые мы смогли удалить записи. Теперь попробуем создать новую запись: king@TKYTE816> exec tkyte.hr_app.insertNew(20) PL/SQL procedure s u c c e s s f u l l y completed. king@TKYTE816> exec t k y t e. h r _ a p p. l i s t E m p s ename sal dname mgr dno KING 9, 9 9 9 ACCOUNTING (null) 1,111 RESEARCH 2 строк(и) выбрано PL/SQL procedure s u c c e s s f u l l y completed. Понятно, что в данном случае это получится, поскольку применяются правила защиты для роли HR_REP. Мы завершили тестирование всех трех ролей. Все требования выполнены, данные защищены, причем защита эта реализована прозрачно для приложений.
Проблемы Как и в случае любого средства, при использовании возможностей тщательного контроля доступа следует учитывать ряд нюансов. В этом разделе мы попытаемся их рассмотреть.
Целостность ссылок При взаимодействии со средствами обеспечения целостности ссылок средства тщательного контроля доступа могут давать неожиданные для разработчиков результаты. Все, мне кажется, зависит от предположений о возможном взаимодействии. Лично я не вполне уверен, что при их взаимодействии должно происходить. Оказывается, средства обеспечения целостности ссылок обходят защиту, устанавливаемую средствами тщательного контроля доступа. С их помощью я могу читать данные таблицы, удалять и изменять их, хотя непосредственно выполнять операторы SELECT, DELETE и INSERT для этой таблицы нельзя. Именно это и предусматривалось разработчиками СУБД и должно быть учтено при проектировании, если предполагается использовать средства тщательного контроля доступа.
Глава Рассмотрим следующие возможности. Х Определение значений данных, которые должны быть недоступны. Это называется тайным каналом (covert channel). Я не могу запросить данные непосредственно. Однако, я могу доказать существование (или отсутствие) определенных значений данных в таблице, используя внешний ключ. Х Удаление данных из таблицы при наличии требования целостности ссылок с конструкцией ON DELETE CASCADE. Х Изменение данных в таблице при наличии требования целостности ссылок с конструкцией ON DELETE SET NULL. Мы рассмотрим все три возможности на несколько надуманном примере двух таблиц Ч P (главная) и С (подчиненная): tkyte@TKYTE816> create table p (x int primary key);
Table created. tkyte@TKYTE816> create table c (x int references p on delete cascade);
Table created.
Тайный канал Тайным каналом в данном случае называется возможность определять наличие или отсутствие значений первичного ключа в таблице P путем вставки строки в таблицу С и анализа результатов. Таким способом можно определить, есть ли в таблице P строка с соответствующим значением первичного ключа. Начнем с функции, которая всегда возвращает условие, являющееся ложным для строки: tkyte@TKYTE816> create or replace function pred_function 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 begin 6 return 'l=0';
7 end;
8/ Function created. И используем эту функцию для ограничения доступа с помощью оператора SELECT к таблице P: tkyte@TKYTE816> begin 2 dbms_rls.add_policy 3 (object_name => ' P ', 4 policy_name => 'P_POLICY', 5 policy_function => 'pred_function', 6 statement_types => ' s e l e c t ' ) ;
7 end;
8/ PL/SQL procedure successfully completed.
Тщательный контроль доступа Теперь мы по-прежнему можем вставлять значения в таблицу P (а также изменять и удалять в ней данные), но не можем ничего выбрать из этой таблицы. Начнем с вставки строки в таблицу P: tkyte@TKYTE816> insert into p values (1);
1 row created. tkyte@TKYTE816> select * from p;
no rows selected Условие не позволяет выбрать эту строку, но можно проверить ее наличие, просто вставив строку в таблицу С: tkyte@TKYTE816> insert into c values (1);
1 row created. tkyte@TKYTE816> insert into c values (2);
insert into c values (2) * ERROR at line 1: ORA-02291: i n t e g r i t y c o n s t r a i n t (TKYTE.SYS_C003873) v i o l a t e d - parent key not found Итак, мы теперь знаем, что значение I содержится в таблице P, а значения 2 в ней нет, потому что в таблицу С удалось вставить строку со значением 1 и не удалось Ч со значением 2. Средства обеспечения целостности ссылок могут читать данные, несмотря на установленные средствами тщательного контроля доступа правила защиты. Это может стать сюрпризом для приложения, например, средства, генерирующего запросы на основе информации из словаря данных. Если запросить данные из таблицы С, все необходимые строки возвращаются. При попытке же соединения таблиц P и С будет получено пустое результирующее множество. Следует также отметить, что подобный тайный канал получения данных из подчиненной таблицы возможен и для главной таблицы. Если бы аналогичное правило защиты было установлено не для таблицы P, а для С и для нее не была бы задана конструкция ON DELETE CASCADE (другими словами, требовалось бы только наличие соответствующего значения первичного ключа), можно было бы определить, какие значения столбца X есть в таблице С, удаляя строки из таблицы Р. Если в подчиненной таблице С есть строки с соответствующим значением, попытка удаления строки из таблицы P приведет к выдаче сообщения об ошибке, а если Ч нет, удаление пройдет успешно, хотя выбирать строки из таблицы С с помощью SELECT и нельзя.
Удаление строк Это можно сделать при наличии конструкции ON DELETE CASCADE в требовании целостности. Если удалить правило для таблицы P и задать эту же функцию в качестве правила защиты для удаления из таблицы С следующим образом: tkyte@TKYTE816> begin 2 dbms_rls.drop_policy 3 ('TKYTE', 'P', 'P_POLICY');
4 end;
1470 Глава 5 / PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 dbms_rls.add_policy 3 (object_name => ' C ', 4 policy_name => 'C_POLICY', 5 policy_function => 'pred_function', 6 statement_types => 'DELETE');
7 end;
8/ PL/SQL procedure successfully completed. то окажется, что нельзя удалить ни одной строки из таблицы С с помощью SQL-оператора: tkyte@TKYTE816> delete from C;
0 rows deleted. Установленное правило защиты не позволяет это сделать. Наличие строки в таблице С (в результате вставки в предыдущем примере) легко проверить: tkyte@TKYTE816> select * from C;
X 1 Простое удаление строки в главной таблице: tkyte@TKYTE816> delete from P;
1 row deleted. снова позволяет обойти правило защиты, задаваемое средствами тщательного контроля доступа Ч соответствующая строка из таблицы С тоже автоматически удаляется: tkyte@TKYTE816> select * from C;
no rows selected Изменение строк Аналогичная ситуация при удалении строк из главной таблицы возникает и при использовании в требовании целостности конструкции ON DELETE SET NULL. Немного изменим пример, чтобы, благодаря требованию целостности ссылок, можно было изменять строки в таблице С, которые нельзя изменять SQL-операторами. Начнем с пересоздания таблицы С, задав для внешнего ключа конструкцию ON DELETE SET NULL. tkyte@TKYTE816> drop table c;
Table dropped. tkyte@TKYTE816> create table c (x int references p on delete set null);
Table created.
Тщательный контроль доступа tkyte@TKYTE816> insert into p values (1);
1 row created. tkyte@TKYTE816> insert into c values (1);
1 row created. Теперь зададим ту же функцию, что и в предыдущих примерах, в качестве правила защиты для изменения данных в таблице С, и установим флагу UPDATE_CHECK значение TRUE. Это не позволит изменять строки: tkyte@TKYTE816> begin 2 dbms_rls.add_policy 3 (object_name -> 'C', 4 policy_name => 'C_POLICY', 5 policy_function => 'pred_function', 6 statement_types => 'UPDATE', 7 update_check => TRUE);
8 end;
9 / PL/SQL procedure successfully completed. tkyte@TKYTE816> update c set x = NULL;
0 rows updated. tkyte@TKYTE816> select * from c;
X 1 Итак, с помощью SQL-операторов строки в таблице С изменять нельзя. Однако удаление строк из таблицы P показывает следующее: tkyte@TKYTE816> delete from p;
1 row deleted. tkyte@TKYTE816> select * from c;
X Итак, обходным путем можно изменить данные в таблице С. Есть и другой способ продемонстрировать это, но данные в таблицах придется восстановить: tkyte@TKYTE816> d e l e t e from c;
1 row d e l e t e d. tkyte@TKYTE816> insert into p values (1);
1 row created. Х tkyte@TKYTE816> insert into c values (1);
1 row created. Теперь перепишем функцию так, чтобы можно было изменять строки в таблице С, задавая им любые значения, кроме Null:
Глава tkyte@TKYTE816> create or replace function pred_function 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 begin 6 return 'x is not null';
7 end;
8/ Function created. tkyte@TKYTE816> update c set x = NULL;
update c set x = NULL * ERROR at line 1: ORA-28115: policy with check option violation Это изменение завершилось неудачно, поскольку условие X IS NOT NULL не будя выполняться после изменения. Если теперь снова удалить строку из таблицы P: tkyte@TKYTE816> delete from p;
1 row deleted. tkyte@TKYTE816> select * from c;
X строка в таблице С получит значение, которое нельзя задать с помощью SQL-оператора непосредственно.
Кэширование курсоров Одна из важных особенностей реализации функции, задающей правила защиты, из первого примера данной главы состоит в том, что в ходе сеанса эта функция возвращает одно и то же условие. Это принципиально важно. Если еще раз рассмотреть алгоритм этой функции: 5 as 6 begin 7 if (user = p_schema) then 8 return '';
9 else 10 return 'owner = USER' 11 end if;
12 end;
оказывается, что она возвращает либо пустое условие, либо условие owner = USER. Ho в течение сеанса она постоянно возвращает одно и то же условие. Невозможно получить условие owner = USER, a затем в том же сеансе получить пустое условие. Чтобы понять, почему это принципиально важно для корректного использования средств тщательного контроля доступа в приложении, надо разобраться, когда условие связывается Тщательный контроль доступа с запросом и как это происходит при использовании различных сред: PL/SQL, Pro*C, OCI, JDBC, ODBC и т.д. Предположим, имеется следующая функция, возвращающая условие: SQL> 2 3 4 5 6 7 8 9 10 11 12 13 create or replace function rls_examp (p_schema in varchar2, p_object in varchar2) return varchar2 as begin if (sys_context('myctx', 'x') is not null) then return 'x > 0';
else return 'l=0';
end if;
end;
/ Function created. Она реализует такой алгоритм: если в контексте установлен атрибут x, возвращается условие x > 0;
если же в контексте атрибут x не установлен, возвращается условие 1=0. Если создать таблицу T, поместить в нее данные и добавить правила и контекст следующим образом: SQL> create table t (x int);
Table created. SQL> insert into t values (1234);
1 row created. SQL> begin 2 dbms_rls.add_policy 3 (object_schema => user, 4 object_name => 'T', 5 policy_name => 'T_POLICY', 6 function_schema => user, 7 policy_function => 'rls_examp', 8 statement_types => 'select');
9 end;
10 / PL/SQL procedure successfully completed. SQL> 2 3 4 5 6 create or replace procedure set_ctx(p_val in varchar2) as begin dbms_session.set_context('myctx', 'x', p_val);
end;
/ Procedure created. SQL> create or replace context myctx using set_ctx;
Context created.
Глава Предполагается, что в случае установки контекста мы должны получить одну строку. Если же контекст не установлен, мы ни одной строки получить не должны. Если проверить это в среде SQL*Plus с помощью простых SQL-операторов, именно так и окажется: SQL> exec set_ctx(null);
PL/SQL procedure successfully completed. SQL> select * from t;
no rows selected SQL> exec set_ctx(1);
PL/SQL procedure successfully completed. SQL> select * from t;
X 1234 Итак, казалось бы, все в порядке. Динамически формируемое условие применяется так, как предполагалось. Фактически же, если использовать язык PL/SQL (или Pro*C, или правильно написанное приложение, использующее интерфейсы OCI/JDBC/ODBC, да и многие другие среды), оказывается, что это не так. Создадим, например, небольшую PL/SQL-процедуру: SQL> create or replace procedure dump_t 2 (some_input in number default NULL) 3 as 4 begin 5 dbms_output.put_line 6 (** '* Результат выполнения оператора SELECT * FROM T');
7 8 for x in (select * from t) loop 9 dbms_output.put_line(x.x);
10 end loop;
11 12 if (some_input is not null) 13 then 14 dbms_output.put_line 15 (** '* Результат выполнения другого оператора SELECT -> * FROM T ' ) ;
16 17 for x in (select * from t) loop 18 dbms_output.put_line(x.x);
19 end loop;
20 end if;
21 end;
22 / Procedure created. Эта процедура выполняет оператор SELECT * FROM T один раз, если входные данные не переданы, и два раза, если переданы какие-либо входные данные. Выполним эту Тщательный контроль доступа процедуру и посмотрим результаты. Выполнение процедуры начнем, установив в контексте значение Null (поэтому будет применяться условие 1Ч0, другими словами, не будет возвращена ни одна строка): SQL> s e t serveroutput on SQL> exec set_ctx(NULL) PL/SQL procedure successfully completed. SQL> exec dump_t *** Результат выполнения оператора SELECT * FROM T PL/SQL procedure successfully completed. Как и ожидалось, данные не получены. Теперь установим значение в контексте так, чтобы возвращалось условие x > 0. Затем вызовем процедуру D U M P _ T так, чтобы она выполняла оба запроса. В версиях Oracle 8.1.5 и 8.1.6 при этом произойдет следующее: SQL> exec set_ctx(l) PL/SQL procedure successfully completed. SQL> exec dump_t(0) *** Результат выполнения оператора SELECT * FROM Т *** Результат выполнения другого оператора SELECT * FROM T 1234 PL/SQL procedure successfully completed. Первый запрос, первоначально выполненный при значении Null в контексте, по-прежнему не возвращает данных. Его курсор был сохранен в кэше и повторно не анализировался. При выполнении процедуры со значением Null атрибута x в контексте, получаем предполагаемые результаты (потому что это было первое выполнение данной процедуры в сеансе). Устанавливаем атрибуту x непустое значение и получаем неоднозначные результаты. Первый оператор SELECT * FROM T в процедуре по-прежнему не возвращает ни одной строки Ч он, видимо, продолжает использовать условие 1=0. Второй запрос (который первый раз мы не выполняли) возвращает, как и предполагалось, правильные результаты. Он использует условие x > 0, как и было задумано. Почему первый оператор SELECT в этой процедуре не использует условие, которое мы предполагали? Это связано с оптимизацией, называемой кэшированием курсора. Язык PL/SQL и многие другие среды выполнения не "закрывают" курсор, когда этого требует разработчик. Представленный пример можно легко воспроизвести в Pro*C, если оставить опции прекомпилятора release_cursor стандартное значение NO. Если тот же код обработать с опцией release_cursor=YES, программа Pro*C будет работать аналогично запросам в среде SQL*Plus. Условие, используемое пакетом DBMS_RLS, связывается с запросом на стадии анализа. Первый запрос SELECT * FROM T анализируется при первом выполнении хранимой процедуры, когда фактически возвращалось условие 1=0. PL/SQL-машина автоматически кэширует проанализированный курсор. При втором выполнении хранимой процедуры PL/SQL-машина повторно использует проанализированный курсор первого запроса SELECT * FROM T. Этот проанализированный курсор Глава включает условие 1=0. Функция, возвращающая условие, на этот раз вообще не вызывалась. Поскольку мы передали процедуре входные данные, PL/SQL-машина выполнила и второй запрос. Для этого запроса, однако, еще нет открытого и проанализированного курсора, так что при выполнении он был проанализирован, с учетом непустого атрибута в контексте. Со вторым запросом SELECT * FROM T было связано условие x>0. Это и вызвало двусмысленность. Поскольку мы не можем управлять кэшированием курсора, возвращения в одном сеансе различных условий функцией, реализующей правила защиты, надо избегать любой ценой. В противном случае возможны плохо воспроизводимые и сложные для отладки ошибки в приложении. Ранее, в примере приложения для работы с информацией о сотрудниках, было продемонстрировано, как реализовать функцию, которая возвращает в ходе сеанса не более одного условия. Это гарантирует следующее. Х Согласованность результатов запросов с точки зрения средств тщательного контроля доступа. Х Неизменность условий защиты по ходу сеанса. Если они изменяются, возможны странные и непредсказуемые результаты. Х Зависимость правил защиты от пользователя, выполняющего операторы, но не от среды, в которой он работает. В версиях Oracle 8.1.7 и выше результат будет следующим: tkyte@dev817> exec dump_t(0) *** Результат выполнения оператора SELECT * FROM T 1234 *** Результат выполнения другого оператора SELECT * FROM T 1234 PL/SQL procedure successfully completed. Во избежание описанных проблем сервер Oracle версий 8.1.7 и выше повторно анализирует запрос при изменении контекста сеанса, если с запросом связаны правила защиты. Подчеркиваю: при изменении контекста сеанса. Если для создания условия не используется контекст сеанса, снова возникает проблема, связанная с кэшированием курсора. Рассмотрим систему, в которой условия хранятся как данные в таблице базы данных. Такая система реализует правила защиты на основе таблицы. Если при этом содержимое таблицы правил изменится, вызывая изменение условия, мы столкнемся версии 8.1.7 с теми же проблемами, что и в 8.1.6, и более ранних версиях. Если изменить предыдущий пример так, чтобы использовалась таблица базы данных: tkyte@TKYTE816> create table policy_rules_table 2 (predicate_piece varchar2(255) 3 );
Table created. tkyte@TKYTE816> insert into policy_rules_table values ( x > 0');
' 1 row created. и изменить функцию, реализующую правила защиты так, чтобы она использовала таблицу:
Тщательный контроль доступа tkyte@TKYTE816> create or replace function rls_examp 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 l_predicate_piecevarchar2(255);
6 begin 7 select predicate_piece into l_predicate_piece 8 from policy_rules_table;
9 10 return l_predicate_piece;
11 end;
12 / Function created. то можно ожидать следующих результатов выполнения процедуры DUMP_T (при изменении условия после выполнения DUMP_T без параметров, но до выполнения ее с параметром): tkyte@DEV817> exec dump_t *** Результат выполнения оператора SELECT * F O T RM 1234 PL/SQL procedure successfully completed. tkyte@DEV817> update policy_rules_table set predicate_piece = '1=0';
1 row updated. tkyte@DEV817> exec dump_t(0) *** Результат выполнения оператора SELECT * FROM T 1234 *** Результат выполнения другого оператора SELECT * FROM T PL/SQL procedure successfully completed. Обратите внимание, что при первом выполнении использовалось условие x>0, и запрос возвращал одну строку из таблицы Т. После выполнения этой процедуры мы изменили условие (это изменение можно было сделать из другого сеанса Ч его, например, мог сделать администратор). При втором выполнении процедуры DUMP_T Ч с параметром, требующим выполнить после первого запроса второй, оказывается, что в первом запросе по-прежнему используется старое условие, x>0, тогда как во втором запросе Ч второе условие, 1=0, только что помещенное в таблицу POLICY_RULES. Необходимо учитывать последствия кэширования курсора, даже в версиях 8.1.7 и выше, если только в правилах защиты наряду с таблицей не используется контекст приложения. Хочу подчеркнуть, что изменять значение SYS_CONTEXT по ходу работы приложения вполне допустимо. Эти изменения будут учтены и использованы при следующем выполнении запроса. Поскольку значения атрибутов контекста передаются как связываемые переменные, они вычисляются на этапе выполнения запроса, а не на этапе анализа, так что константы на этапе анализа не используются. Только текст условия не должен меняться по ходу работы приложения. Вот небольшой пример, демонстрирующий это. Завершим сеанс и снова зарегистрируемся (чтобы очистить кэш курсоров в сеансе), а затем изменим реализацию функции RLS_EXAMP. Затем выполним те же действия, что и раньше:
Глава tkyte@TKYTE816> connect tkyte/tkyte tkyte@TKYTE816> create or replace function rls_examp 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 begin 6 return 'x > sys_context(''myctx'',''x'')';
7 end;
8/ Function created. tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> exec set_ctx(NULL) PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dump_t *** Результат выполнения оператора SELECT * FROM T PL/SQL procedure successfully completed. tkyte@TKYTE816> exec set_ctx(l) PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dump_t(0) *** Результат выполнения оператора SELECT * FROM T 1234 *** Результат выполнения другого оператора SELECT * FROM T 1234 PL/SQL procedure successfully completed. На этот раз оба запроса вернули одинаковые результаты. Это связано с тем, что они используют одинаковую конструкцию WHERE и в условии динамически обращаются к значению атрибута в контексте приложения. Следует упомянуть, что бывают случаи, когда изменение условия по ходу сеанса может оказаться необходимым. Для этого приходится специальным образом программировать приложения, обращающиеся к объектам, правила защиты которых допускают изменение условий по ходу сеанса. Например, в PL/SQL придется использовать в приложении исключительно динамический SQL, чтобы избежать кэширования курсоров. При использовании этого способа поддержки динамических условий следует помнить, что результаты будут зависеть от того, как написано клиентское приложение, так что подобным образом универсальные правила защиты реализовать не удастся. Мы не будем рассматривать этот способ использования средств пакета DBMS_RLS, а сосредоточимся на его исходном назначении Ч защите данных от несанкционированного доступа.
Экспортирование/Импортирование Эту проблему мы уже упоминали. Необходимо быть внимательным при использовании утилиты ЕХР для экспортирования данных и утилиты IMP для их импортирования. Поскольку при этом возникают разные проблемы, они будут рассмотрены по от Тщательный контроль доступа дельности. Чтобы продемонстрировать проблемы, придется несколько расширить предыдущий пример, изменив правила защиты T_POLICY. На этот раз правила будут применяться не только для операторов SELECT, но и для операторов INSERT: tkyte@TKYTE816> begin 2 dbms_rls.drop_policy('TKYTE', 'T', 'T_POLICY');
3 end;
4/ PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 dbms_rls.add_policy 3 (object_name => 'T', 4 policy_name => 'T_POLICY', 5 policy_function => 'rls_examp', 6 statement_types => 'select, insert', 7 update_check => TRUE);
8 end;
9/ PL/SQL procedure successfully completed. После этого мы получим следующий эффект: tkyte@TKYTE816> d e l e t e from t;
1 row d e l e t e d. tkyte@TKYTE816> commit;
Commit complete. tkyte@TKYTE816> exec set_ctx(null);
PL/SQL procedure successfully completed. tkyte@TKYTE816> insert into t values (1);
insert into t values (1) * ERROR at line 1: ORA-28115: policy with check option violation tkyte@TKYTE816> exec set_ctx(0) ;
PL/SQL procedure successfully completed. tkyte@TKYTE816> insert into t values (1);
1 row created. Итак, теперь контекст необходимо устанавливать не только для чтения, но и для вставки данных.
Проблемы экспорта Стандартно утилита ЕХР работает в режиме "обычного" (conventional path) экспорта. Для чтения данных она использует SQL-операторы. Используя утилиту ЕХР для получения данных таблицы T из базы данных, получим следующий результат (учтите, что в таблице T имеется одна строка Ч результат выполнения оператора INSERT):
1480 Глава C:\fgac\exp userid=tkyte/tkyte tables=t Export: Release 8.1.6.0.0 - Production on Mon Apr 16 16:29:25 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 Ч Production Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set About to export specified tables via Conventional Path EXP-00079: Data in table "T" is protected. Conventional path may only be exporting partial table... exporting table T 0 rows exported Export terminated successfully with warnings. Обратите внимание, как утилита ЕХР "великодушно" сообщила, что таблица может быть экспортирована только частично, поскольку используется обычный способ экспортирования. Для решения этой проблемы при экспортировании надо подключаться как пользователь SYS (или от имени любого другого пользователя с ролью SYSDBA). Для пользователя SYS средства тщательного контроля доступа не действуют: C:\fgac\exp userid=sys/manager tables=tkyte.t Export: Release 8.1.6.0.0 - Production on Mon Apr 16 16:35:21 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set About to export specified tables via Conventional Path... Current user changed to TKYTE.. exporting table T 1 rows exported Export terminated successfully without warnings. Можно также использовать процедуру DBMS_RLS.ENABLE_POLICY для временного отключения правил зашиты и повторного их включения после экспортирования. Хотя так делать нежелательно, поскольку на время экспорта таблицы остаются незащищенными. В некоторых версиях Oracle 8.1.5 при непосредственном экспорте по ошибке обходятс средства тщательного контроля доступа. Другими словами, если указать onции direct=true, будут экспортированы все данные. На этот способ полагаться не стоит поскольку в следующих версиях эта ошибка была исправлена. В новых версиях ei получите: About to export specified tables via Direct Path... EXP-00080: Data in table "T" is protected. Using conventional mode. EXP-00079: Data in table "T" is protected. Conventional path may only...
Тщательный контроль доступа Утилита ЕХР будет автоматически экспортировать защищенные таблицы в обычном режиме.
Проблемы импорта Эти проблемы возникают только в том случае, когда установлены правила защиты для операторов вставки и опция UPDATE_CHECK имеет значение True. B этом случае утилита IMP не будет вставлять строки, не удовлетворяющие условию, возвращаемому соответствующей функцией. Именно так и произойдет в рассмотренном ранее примере. Если не установить контекст, ни одна строка вставлена не будет (по умолчанию значение в контексте Ч Null). Поэтому, если попытаться импортировать экспортированные данные: C:\fgac>imp userid=tkyte/tkyte full=y ignore=y Import: Release 8.1.6.0.0 - Production on Mon Apr 16 16:37:33 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 Ч Production With the Partitioning option JServer Release 8.1.6.0.0 Ч Production Export file created by EXPORT:V08.01.06 via conventional path Warning: the objects were exported by SYS, not by you import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set. importing SYS's objects into TKYTE.. importing table "T" IMP-00058: ORACLE error 28115 encountered 0RA-28115: policy with check option violation IMP-00017: following statement failed with ORACLE error 28101: "BEGIN DBMS_RLS.ADD_POLICY('TKYTE', 'T', 'T_POLICY','TKYTE','RLS_EXAMP', 'SE" "LECT,INSERT',TRUE,TRUE);
END;
" IMP-00003: ORACLE error 28101 encountered ORA-28101: policy already exists ORA-06512: at "SYS.DBMS_RLS", line 0 ORA-06512: at line 1 import terminated successfully with warnings. строки вставлены не будут. Проблему можно решить, подключившись от имени пользователя SYS или пользователя с ролью SYSDBA: C:\fgac>imp userid=sys/manager full=y ignore=y import: Release 8.1.6.0.0 - Production on Mon Apr 16 16:40:56 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Глава Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set. importing SYS's objects into SYS. importing TKYTE's objects into TKYTE.. importing table "T" 1 rows imported Можно также с помощью процедуры DBMS_RLS.ENABLE_POLICY временно отключить правила и включить их после импортирования. Как и в случае экспорта, это не желательно, поскольку в процессе импортирования таблица не защищена.
Отладка При написании функций, возвращающих условия, я часто использую пакет средств отладки, debug. Этот пакет, созданный сотрудником корпорации Oracle Кристофером Беком (Christopher Beck), позволяет включить в код операторы отладочной печати. Он позволяет свободно вставлять в код операторы вида: create function foo... as begin debug.f('Bxoдим в процедуру foo');
if (some_condition) then l_predicate := 'x=l';
end if;
debug.f('Возвращаем условие ''%s''', l_predicate);
return l_predicate;
end;
Итак, процедура debug.f работает аналогично С-функции printf и реализована с помощью средств пакета UTL_FILE. Она создает управляемые программистом файлы трассировки на сервере базы данных. Файлы трассировки содержат результаты отладочной печати, благодаря которым можно понять, как выполняется код. Поскольку ядро сервера вызывает код реализующих правила защиты функций в фоновом режиме, отладка их затруднена. Традиционные средства вроде пакета DBMS_OUTPUT и отладчика PL/SQL тут не особенно помогут. При наличии файлов трассировки можно сэкономить очень много времени. Среди сценариев, которые можно загрузить с сайта издательства Wrox, находится и пакет debug c комментариями по его настройке и использованию. Этот пакет особенно полезен при поиске проблем в функциях, реализующих правила защиты, и я настоятельно рекомендую использовать его или другой подобный пакет. При отсутствии подобных средств трассировки практически невозможно разобраться, что работает неправильно.
Ошибки, которые могут произойти По ходу реализации рассмотренного ранее приложения я столкнулся с многочисленными ошибками, и мне пришлось заниматься его отладкой. Поскольку средства тщательного контроля доступа работают на сервере, поиск причин ошибки и отладка при Тщательный контроль доступа ложения очень затруднена. Изучив следующие разделы, вы сможете находить и успешно устранять причины ошибок*.
ORA-28110: пакет или функция <имя функции> методики имеет ошибку Эта ошибка свидетельствует о том, что в пакете или функции, реализующей правила защиты, имеется ошибка, и перекомпиляция ее невозможна. Если выполнить в среде SQL*Plus команду SHOW ERRORS FUNCTION <ИМЯ ФУНКЦИИ> или SHOW ERRORS PACKAGE BODY <ИМЯ ПАКЕТА>, можно получить соответствующие сообщения об ошибках. Эта ошибка может быть связана с тем, что: Х один из объектов, на который ссылается функция, удален или стал недействительным;
Х в компилируемом коде есть синтаксическая ошибка или его по какой-то причине нельзя скомпилировать. Наиболее типичная причина этой ошибки состоит в том, что условие, которое возвращает функция, реализующая правила защиты для таблицы, содержит ошибку. Рассмотрим функцию, использованную в предыдущих примерах: tkyte@TKYTE816> create or replace function rls_examp 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 begin 6 this is an error 7 return 'x > sys_context(''myctx'',''x'')';
8 end;
9/ Warning: Function created with compilation errors.
Предположим, при компиляции мы не обратили внимания, что функция не откомпилирована, как положено. Мы предполагаем, что функция скомпилирована успешно, и что ее можно выполнять. Теперь, попытавшись выполнить запрос к таблице T, мы получим: tkyte@TKYTE816> exec s e t _ c t x ( 0 ) ;
PL/SQL procedure successfully completed. tkyte@TKYTE816> s e l e c t * from t;
select * from t * E R R at l i n e 1: RO ORA-28110: p o l i c y function or package TKYTE.RLS_EXAMP has e r r o r ' Тексты сообщений об ошибках в названиях подразделов здесь представлены так, как их выдает сервер Oracle 8.1.6 при установке русского в качестве языка сообщений. В примерах кода оставлены сообщения об ошибках на английском. Обратите внимание на расхожденияв терминологии: "методика" вместо "правила". - Прим. научн. ред.
Глава Итак, это сообщение свидетельствует о наличии ошибки, а именно, об ошибке в функции TKYTE.RLS_EXAMP (ее не удалось успешно скомпилировать). Для того чтобы находить подобные проблемы прежде, чем они возникнут, пригодится следующий запрос: tkyte@TKYTE816> column pf_owner format a10 tkyte@TKYTE816> column package format a10 tkyte@TKYTE816> column function format a10 tkyte@TKYTE816> select pf_owner, package, function 2 from user_policies a 3 where exists (select null 4 from all_objects 5 where owner = pf_owner 6 andobject_type in ('FUNCTION', 'PACKAGE', 7 'PACKAGE BODY') 8 and status = 'INVALID' 9 and object_name in (a.package, a.function) 10 ) 11 / PF_OWNER PACKAGE FUNCTION TKYTE RLS_EXAMP Этот запрос выдает список всех недопустимых функций, реализующих правила защиты. Пока что он подтверждает то, что мы и так знаем, Ч что функция TKYTE.RLS_EXAMP скомпилирована с ошибками. Решить эту проблему несложно. Выполняем: tkyte@TKYTE816> show errors function rls_examp Errors for FUNCTION RLS_EXAMP: LINE/COL ERROR 6/10 PLS-00103: Encountered the symbol "AN" when expecting one of the following: :=. ( @ % ;
Исправляем ошибку в строке 6 с текстом this is an error, и сообщение ORA-28110 выдаваться не будет.
ORA-28112: сбой при выполнении функции методики Сообщение об ошибке ORA-28112: failed to execute policy function выдается при выполнении оператора SELECT или оператора ЯМД для таблицы, относительно которой заданы правила защиты, если в реализующей эти правила функции (а не в возвращаемом ею условии) произошла ошибка. Это означает, что при выполнении функции возбуждена исключительная ситуация, не обработанная в самой функции и полученная ядром сервера. При возникновении ошибки ORA-28112 в каталоге, заданном параметром инициализации USER_DUMP_DEST, будет генерироваться файл трассировки. В этом файле не будет сообщения об ошибке ORA-28112, но будет фраза Policy function execution error Зададим для функции следующий алгоритм (продолжаем предыдущий пример):
Тщательный контроль доступа tkyte@TKYTE816> create or replace function rls_examp 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 l_uid number;
6 begin 7 select user_id 8 into l_uid 9 from all_users 10 where username - 'ИМЯ_НЕСУЩЕСТВУКЩЕГ0_П0ЛЬPЗОВАТЕЛЯ';
11 12 return 'x > sys_context(''myctx'',''x'')';
13 end;
14 / Function created. Эта функция специально написана так, чтобы возбуждалась и не обрабатывалась исключительная ситуация NO_DATA_FOUND. Любопытно посмотреть, что произойдет, если исключительная ситуация распространится на уровень ядра сервера. Инициируем вызов этой функции: tkyte@TKYTE816> exec set_ctx(0);
PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from t;
select * from t * ERROR at line 1: ORA-28112: failed to execute policy function Это означает, что функция, реализующая правила защиты, существует и синтаксически корректна, но при ее выполнении возбуждается исключительная ситуация. При возникновении этой ошибки создается файл трассировки. Если посмотреть содержимое каталога, задаваемого параметром инициализации USER_DUMP_DEST и найти этот файл трассировки, в самом начале можно обнаружить следующее: *** SESSION ID:(8.405) 2001-04-16 17:03:00.193 *** 2001-04-16 17:03:00.193 Policy function execution error: Logon user : TKYTE Table or View : TKYTE.T Policy name : T_POLICY Policy function: TKYTE.RLS_EXAMP ORA-01403: no data found ORA-06512: at "TKYTE.RLS_EXAMP", line 7. ORA-06512: at line 1 Эта информация позволяет определить, в каком месте функции произошла ошибка. Явно сказано про строку 7, содержащую оператор SELECT... INTO, a также указано, что в этой строке возбуждена исключительная ситуация NO_DATA_FOUND.
Глава ORA-28113: ошибка в предикате методики Сообщение об ошибке ORA-28113: policy predicate has error выдается при выполнении оператора SELECT или оператора ЯМД для таблицы, относительно которой заданы правила защиты, если соответствующая функция возвращает синтаксически или семантически неверное условие. Это условие при добавлении к исходному запросу дает синтаксически неправильный SQL-оператор. При возникновении ошибки ORA-28113 в каталоге, заданном параметром инициализации USER_DUMP_DEST, генерируется файл трассировки. В нем будет сообщение об ошибке ORA-28113 и информация о текущем сеансе и ошибочном условии. Пусть, например, функция реализована так, как показано ниже. Она возвращает условие, сравнивающее значение в столбце X с несуществующим столбцом таблицы: tkyte@TKYTE816> create or replace function rls_examp 2 (p_schema in varchar2, p_object in varchar2) 3 return varchar2 4 as 5 begin 6 return 'x = несуществующий_стол6ец';
7 end;
8/ Function created.
Так что запрос вида:
select * from t будет переписан как: select * from (select * from t where x = несуществующий_стол6ец) Очевидно, поскольку в таблице T такого столбца нет, этот запрос выполнить нельзя. tkyte@TKYTE816> select * from t;
select * from t * ERROR at line 1: ORA-28113: policy predicate has error Функция успешно вернула условие, но при добавлении этого условия к запросу произошла ошибка. В конце текста соответствующего файла трассировки на сервере мы обнаружим: *** SESSION ID:(8.409) 2001-04-16 17:08:10.669 *** 2001-04-16 17:08:10. Error information for ORA-28113: Logon user : TKYTE Table or View : TKYTE.T Policy name : T_POLICY Policy function: TKYTE.RLS_EXAMP RLS predicate :
Тщательный контроль доступа x = несуществукщий_столбец ORA-00904: invalid column name Этой информации достаточно для решения проблемы (изменения условия, которое привело к выдаче сообщения об ошибке), поскольку, помимо ошибочного условия, имеется сообщение об ошибке в этом условии.
ORA-28106: вводимое значение для аргумента #2 неверно Если имя атрибута не является допустимым идентификатором Oracle, сообщение об ошибке можно получить при вызове процедуры DBMS_SESSION.SET_CONTEXT. Атрибуты в контексте приложения должны именоваться с соответствии с соглашениями, принятыми в Oracle (точно так же, как имена столбцов таблиц или переменных PL/SQL). Единственное решение Ч изменить имя атрибута. Нельзя, например, использовать атрибут контекста с именем SELECT, придется переименовать его.
Резюме В этой главе были подробно рассмотрены средства тщательного контроля доступа. Есть много аргументов за использование этих средств и лишь несколько Ч против. В общем-то, найти аргументы против использования этих средств непросто. Мы рассмотрели, как средства тщательного контроля доступа: Х Упрощают разработку приложений. Они отделяют управление доступом от приложения, приближая его к данным. Х Гарантируют постоянную защиту данных. Независимо от того, какое средство использовано для доступа к данным, правила защиты применяются неукоснительно, и обойти их нельзя. Х Позволяют изменять правила защиты без изменения клиентских приложений. Х Упрощают управление объектами базы данных. Их использование позволяет уменьшить количество объектов базы данных, необходимых для поддержки приложения. Эти средства обеспечивают отличную производительность. Производительность фактически зависит от производительности алгоритмов и SQL-операторов, реализующих правила защиты. Если возвращаемое условие не позволяет оптимизатору выработать эффективный план выполнения, это никак не связано со средствами тщательного контроля доступа, а исключительно с настройкой SQL-оператора. Использование контекста приложения позволяет воспользоваться всеми преимуществами разделяемых SQL-операторов и уменьшить количество создаваемых для базы данных объектов. Средства тщательного контроля доступа не снижают производительность в большей степени, чем любой другой способ выполнения тех же проверок. Было также показано, что эти средства могут создавать определенные проблемы при отладке, поскольку тщательный контроль доступа выполняется в фоновом режиме и обычные средства, например, отладчик или пакет DBMS_OUTPUT, не помогают. Пакеты, вроде упомянутого в разделе "Ошибки, которые могут возникнуть" пакета debug, упрощают трассировку и отладку приложений, использующих средства тщательного контроля доступа.
Многоуровневая аутентификация Многоуровневой (n-tier), или промежуточной, аутентификацией называется регистрация программного обеспечения промежуточного уровня от своего имени для выполнения в базе данных каких-либо действий по поручению пользователя. Это позволяет создавать промежуточные приложения, использующие собственную схему аутентификации, например, с помощью сертификатов X509 или другого процесса однократной регистрации и регистрироваться от имени пользователя, не зная его пароля в базе данных. Хотя регистрация выполнена не от имени пользователя, а от имени промежуточного ПО, для базы данных он зарегистрирован. В этой главе мы рассмотрим многоуровневую аутентификацию и использование этой новой возможности Oracle 8i в приложениях. В частности: Х будут представлены возможности и рассмотрено их использование в приложениях;
Х разработана программа на основе средств библиотеки OCI, которая позволит использовать промежуточную аутентификацию для регистрации в базе данных;
Х описан оператор ALTER USER, позволяющий использовать промежуточную аутентификацию в базе данных;
Х рассмотрена возможность отслеживания действий, выполняемых от имени промежуточной учетной записи.
Глава В версии Oracle 8i многоуровневую аутентификацию можно использовать только в программах на основе библиотеки Oracle Call interface (OCI), написанных на языках С или С++. В Oracle 9i многоуровневая аутентификация будет поддерживаться также интерфейсом JDBC, что существенно расширит ее возможности.
Когда использовать многоуровневую аутентификацию?
Во времена централизованных и клиент-серверных систем аутентификация была простой. Клиент (приложение) запрашивал у пользователя реквизиты (имя пользователя и пароль) и передавал их серверу базы данных. Сервер проверял эти реквизиты и, если все было правильно, клиент подключался к серверу:
Pages: | 1 | ... | 16 | 17 | 18 | 19 | 20 | ... | 24 | Книги, научные публикации