Детальный контроль доступа и контексты приложения. Часть 3

Часть 2

• Теперь, когда для приложения созданы таблицы EMP, DEPT и HR_REPS, напишем процедуру, которая позволит установить значения контекста приложения. Контекст приложения будет состоять из двух элементов данных - EMPNO пользователя, присоединенного в данный момент, и его роль (одна из EMP, MGR или HR_REP). Процедура формирования динамического предиката будет использовать роль, хранящуюся в контексте приложения для того, чтобы определить, как должно выглядеть условие where для конкретного пользователя.

1. Данные таблицы служащих используются для реализации политики безопасности.
2. Эта таблица считывается при создании контекста приложения.

SQL> -- это процедура, "несущая ответственность" за 
SQL> -- создание контекста приложения. Она реализует
SQL> -- политику путем установки привилегий,
SQL> -- выданных пользователю в контексте приложения.

SQL> create or replace
  2  procedure set_role( p_roleName in varchar2 default null )
  3  as
  4      l_empno       number;
  5      l_cnt      number;
  6      l_roleName varchar2(40) default upper(p_roleName);
  7  begin
  8      if ( sys_context( 'Hr_App_Ctx', 'RoleName' ) is NOT NULL )
  9      then
10    -- прерывание запроса. Изменение роли требует изменения
11       -- предиката, связанного с запросом. Из-за кеширования курсора
12          -- в клиентском приложении нельзя гарантировать, что
13          -- не существует других уже разобранных запросов с
14          -- предикатами из текущей роли.
15          -- Если, например, роль уже установлена в значение MGR и 
16          -- разобрано несколько запросов, а теперь попытаться 
17          -- изменить роль на EMP, то разобранные запросы все еще 
18          -- будут использовать предикаты MGR, а не EMP.
19          raise_application_error( -20001, 'Рольужеустановлена' );
20      end if;
21
22    -- Так как используется пользователь СЕССИИ, а не ТЕКУЩИЙ 
25    -- пользователь и, кроме того, empno текущего пользователя 
23    -- используется достаточно часто, то он будет храниться в этом 
24    --Контексте. Пользователь СЕССИИ - это имя присоединенного в
26    -- данный момент пользователя. ТЕКУЩИЙ пользователь - это имя
27    -- пользователя, который обладает привилегиями на выполнение 
28    -- запроса, им может быть владелец процедуры, 
29    -- т.е. не присоединенный пользователь!!!
30      select empno into l_empno
31        from emp_base_table
32       where ename = sys_context( 'userenv', 'session_user');
33
34      dbms_session.set_context( 'Hr_App_Ctx', 'Empno', l_empno );
35
36      if ( l_roleName = 'EMP' )
37      then
38          -- любой может воспользоваться ролью EMP
39          dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'EMP' );
40      elsif ( l_roleName = 'MGR' )
41      then
42     -- проверим, является ли пользователь MGR (менеджером), 
43    -- если нет, то выдадим сообщение об ошибке и прервем выполнение. 
44    -- Пользователь может попытаться снова.
45          select count(*) into l_cnt
46            from dual
47           where exists
48             ( select NULL
49             from emp_base_table
50                where mgr = to_number(sys_context('Hr_App_Ctx','Empno'))
51             );
52          if ( l_cnt = 0 )
53          then
54          raise_application_error( -20002, 'Вынеменеджер' );
55          end if;
56          dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'MGR' );
57      elsif ( l_roleName = 'HR_REP' )
58      then59          -- проверим, является ли пользователь HR_REP, если нет, 
60    -- выдадим сообщение об ошибке и прервем выполнение.
61    -- Пользователь может попытаться снова.
62          select count(*) into l_cnt
63            from dual
64           where exists
65             ( select NULL
66             from hr_reps
67                where username = sys_context( 'userenv', 'session_user' )
68             );
69
70          if ( l_cnt = 0 )
71          then
72          raise_application_error( -20002, 'Вынеконтролер' );
73          end if;
74          dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'HR_REP' );
75      else
76          raise_application_error( -20003, 'Роль ' // l_roleName //
77                           ' невозможно распознать' );
78      end if;
79  end;
80  /
Procedure created.

SQL> grant execute on set_role to public
  2  /
Grant succeeded.

1. Проверяет, является ли пользователь служащим - при получении ошибки "NO DATA FOUND", становится известным, что он не служащий. Поэтому значения его контекста никогда не будут установлены, а сам пользователь не увидит никаких данных.
2. Помещает часто используемое значение в контекст приложения. Теперь через EMPNO текущего пользователя можно быстро получить доступ к таблице EMP - это будет реализовано в предикатной функции, описанной ниже.

SQL> -- Создание контекста приложения. Имя контекста -
SQL> -- HR_APP_CTX.  Процедура, с которой он связан в данном случае - 
SQL> -- это SET_ROLE

SQL> create or replace context Hr_App_Ctx using SET_ROLE
  2  /
Context created.

SQL> REM Выполнение следующих далее операций будет ПРЕРВАНО. 
SQL> REM Это показывает, что процедура dbms_session.set_context 
SQL> REM может установить контекст Hr_App_Ctx только через процедуру
SQL> REM SET_ROLE 

SQL> exec dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'MGR' );
BEGIN dbms_session.set_context( 'Hr_App_Ctx', 'RoleName', 'MGR' ); END

*
ERROR at line 1:
ORA-01031: привилегийнедостаточно
ORA-06512: at "SYS.DBMS_SESSION", line 55
ORA-06512: at line 1

SQL> grant select on sys.v_$context to rls_smith;
Grant succeeded.

SQL> connect rls_smith/rls_smith
Connected.

SQL> set serveroutput on
SQL> show user
USER is "RLS_SMITH"

SQL> exec rls.set_role( 'Mgr' )
BEGIN rls.set_role( 'Mgr' ); END;

*
ERROR at line 1:
ORA-20002: Вы не менеджер
ORA-06512: at "RLS.SET_ROLE", line 53
ORA-06512: at line 1

SQL> select * from v$context;

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- -----
HR_APP_CTX EMPNO      7369

SQL> exec rls.set_role( 'emp' )
PL/SQL procedure successfully completed.

SQL> select * from v$context;

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- -----
HR_APP_CTX ROLENAME   EMP
HR_APP_CTX EMPNO      7369

SQL> exec rls.set_role( 'emp' )
BEGIN rls.set_role( 'emp' ); END;

*
ERROR at line 1:
ORA-20001: Роль уже установлена
ORA-06512: at "RLS.SET_ROLE", line 18
ORA-06512: at line 1

SQL> select * from v$context;

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- -----
HR_APP_CTX ROLENAME   EMP
HR_APP_CTX EMPNO      7369

SQL> connect rls_blake/rls_blake
Connected.

SQL> exec rls.set_role( 'mgr' )
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> declare
  2      l_AppCtx     dbms_session.AppCtxTabTyp;
  3      l_size        number;
  4  begin
  5      dbms_session.list_context( l_AppCtx, l_size );
  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;
/

HR_APP_CTX.ROLENAME = MGR
HR_APP_CTX.EMPNO = 7698

PL/SQL procedure successfully completed.

SQL> -- создадим предикатный пакет (пакет для генерации условия where)
SQL> -- приложения HR. Для создания уникального условия where каждой 
SQL> -- операции SELECT/INSERT/UPDATE/DELETE должна соответствовать
SQL> -- отдельная функция.

SQL> create or replace package hr_predicate_pkg
  2  as
  3     function select_function( p_schema in varchar2,
  4                               p_object in varchar2 ) return varchar2;
  5
  6     function update_function( p_schema in varchar2,
  7                               p_object in varchar2 ) return varchar2;
  8
  9     function insert_delete_function( p_schema in varchar2,
10   p_object in varchar2 ) return varchar2;
11  end;
12  /

SQL> create or replace package body hr_predicate_pkg
  2  as
  3
  4  g_app_ctx constant varchar2(30) default 'Hr_App_Ctx';
  5
  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
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 := 'empno = sys_context(''Hr_App_Ctx'',''EmpNo'')';
21
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(''Hr_App_Ctx'',''EmpNo'')
29                 connect by prior empno = mgr)';
30
31          elsif ( 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(''userenv'',''session_user'') )';
38
39          else
40          raise_application_error( -20005, 'Рольнеустановлена' );
41          end if;
42      end if;
43
44      return g_sel_pred;
45  end;
46
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 := '1=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(''Hr_App_Ctx'',''EmpNo'') )';
64
65          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'HR_REP' )
66          then
67          g_upd_pred := 'deptno in
68                   ( select deptno
69                       from hr_reps
70                      where username =
71              sys_context(''userenv'',''session_user'') )';
72
73          else
74          raise_application_error( -20005, 'Рольнеустановлена' );
75          end if;
76      end if;
77
78      return g_upd_pred;
79  end;
80
81  function insert_delete_function( p_schema in varchar2,
82                       p_object in varchar2 ) return varchar2
83  is
84  begin
85      if ( g_ins_del_pred is NULL )
86      then
87          if ( sys_context( g_app_ctx, 'RoleName' ) in ( 'EMP', 'MGR' ) )
88          then
89          g_ins_del_pred := '1=0';
90          elsif ( sys_context( g_app_ctx, 'RoleName' ) = 'HR_REP' )
91          then
92          g_upd_pred := 'deptno in
93                   ( select deptno
94                       from hr_reps
95                      where username =
96                  sys_context(''userenv'',''session_user'') )';
97          else
98          raise_application_error( -20005, 'Рольнеустановлена' );
99          end if;
100      end if;
101      return g_ins_del_pred;
102  end;
103
104  end;
/
Package body created.

 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  
-- логика для присвоения значения g_sel_pred, 
-- глобальной переменной, объявленной в теле пакета
 42      end if;
 43
 44      return g_sel_pred;
 45  end;

• Это быстрее, чем вычисление и создание предиката в процедуре для каждого разбираемого предложения. Размер исполняемого кода уменьшается.
• Предотвращается возможность возврата различных значений предиката в пределах одной сессии. Как отмечено в первом рассмотренном выше примере, результаты возврата различных предикатов в пределах одной сессии могут быть противоречивыми, а при использовании этой технологии предотвращается возникновение такой ситуации.

SQL> -- Добавим политику к представлению EMP. Каждая функция пакета
SQL> -- HR_PREDICATE_PKG связывается с таблицей для выполнения операций 
SQL> -- SELECT/INSERTUPDATE/DELETE. При INSERT и UPDATE установим флаг
SQL> -- 'update_check' взначение TRUE. Это очень похоже на создание
SQL> -- представления с 'CHECK OPTION'
SQL> -- В этом случае обеспечивается, что данные, создаваемые в базе
SQL> -- данных, - это те данные, которые пользователь может
SQL> -- увидеть.

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'EMP',
  5       policy_name     => 'HR_APP_SELECT_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'HR_PREDICATE_PKG.SELECT_FUNCTION',
  8       statement_types => 'select' );
  9  end;
10  /

PL/SQL procedure successfully completed.

 SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'EMP',
  5       policy_name     => 'HR_APP_UPDATE_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'HR_PREDICATE_PKG.UPDATE_FUNCTION',
  8       statement_types => 'update' ,
  9       update_check    => TRUE );
 10  end;
 11  /

 PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => 'RLS',
  4       object_name     => 'EMP',
  5       policy_name     => 'HR_APP_INSERT_DELETE_POLICY',
  6       function_schema => 'RLS',
  7       policy_function => 'HR_PREDICATE_PKG.INSERT_DELETE_FUNCTION',
  8       statement_types => 'insert, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

 PL/SQL procedure successfully completed.

• Запроса данных
• Обновления данных
• Удаления данных
• Вставки новых данных

SQL> create or replace package hr_app
  2  as
  3      procedure listEmps;
  4
  5      procedure updateSal;
  6
  7      procedure deleteAll;
  8
  9      procedure insertNew( p_deptno in number );
 10  end;
 11  /

Package created.

Телопакета:

SQL> create or replace package body hr_app
  2  as
  3
  4  procedure listEmps
  5  as
  6      l_AppCtx     dbms_session.AppCtxTabTyp;
  7      l_size       number;
  8  begin
  9      dbms_output.put_line( '------ КонтекстСессии ----------' );
 10      dbms_session.list_context( l_AppCtx, l_size );
 11      for i in 1 .. l_size loop
 12          dbms_output.put( l_AppCtx(i).namespace // '.' );
 13          dbms_output.put( l_AppCtx(i).attribute // ' = ' );
 14          dbms_output.put_line( l_AppCtx(i).value );
 15      end loop;
 16
 17      dbms_output.put_line( '------ Данныетаблицы Emp, которыеможноувидеть -----' );
 18      for x in ( select ename, sal, dname
 19               from emp, dept
 20              where emp.deptno = dept.deptno )
 21      loop
 22          dbms_output.put_line( x.ename // ',' // x.sal // ',' // x.dname );
 23      end loop;
 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.

 SQL> grant execute on hr_app to public
 2  /
Grant succeeded.

Часть 4