※ 本記事は2017年3月23日に公開されたものです。

前回までは仮想プライベートデータベースで行レベルのアクセス制御を実現する方法を説明してきました。
今回は列レベルのアクセス制御を実現する方法を説明します。

列レベルのアクセス制御を実現するには、ファイングレインアクセスコントロール設定時にDBMS_RLS.ADD_POLICYプロシージャでsec_relevant_colsとsec_relevant_cols_optパラメータを設定します。

まずSYSTEMユーザーでデータベースに接続し、列レベルのアクセス制御の確認用にAPP1.TESTTAB表に行を追加します。

alter table app1.testtab add (mynumber varchar2(12));

さて、ここでMYNUMBER列に値を追加したいのですが、どのユーザーであれば値が追加できるでしょうか？ 
正解はSYSユーザーだけです。値を追加するには表に対するUPDATE権限が必要ですが、オブジェクトオーナーであるAPP1ユーザーもUPDATE ANY TABLEシステム権限を持っているSYSTEMユーザーも仮想プライベートデータベースのアクセス制御の設定でデータが参照できなくなっています。そのため、データの更新もできません。唯一、SYSユーザーはUPDATE ANY TABLEシステム権限を持ち、仮想プライベートデータベースのアクセス制御ポリシーをバイパスできるSYSユーザーだけが値を追加できます。
マイナンバーは事務取扱担当者しかアクセスしてはいけないため、今回は事務取扱担当者用のユーザー(NAGISA)を追加し、このユーザーでMYNUMBER列に値を追加することにします。 

create user nagisa identified by Orac1el2c;
grant create session to nagisa;
grant select, update on app1.testtab to nagisa;

ファイングレインアクセスコントロールのポリシーファンクションを変更して、事務取扱事業者のNAGISAユーザーもメンテナンスユーザーのPUKUユーザーと同じ条件で全行のデータを見れるようにします。 

create or replace function app1.vpdfunc
  (v_schema varchar2, v_objname varchar2)
  return varchar2
is begin
  if upper(sys_context('userenv','session_user')) in ('PUKU', 'NAGISA') then
    if sys_context('userenv','ip_address') = '10.185.155.180' and
       sys_context('userenv','client_program_name') like 'sqlplus%' then
      return '1=1';
    else
      return '0=1';
    end if;
  elsif upper(sys_context('userenv','session_user')) = 'APP1USER' then
    if sys_context('userenv','client_program_name') = 'dbsecnavi sample application' then
      return 'name = upper(sys_context(''userenv'',''client_identifier''))';
    else
      return '0=1';
    end if;
  else
    return 'name = upper(sys_context(''userenv'',''session_user''))';
  end if;
end;
/ 

NAGISAユーザーでデータベース接続すると、MYNUMBER列に値を追加できます。

update app1.testtab set mynumber = '123456789012' where name='SATO';
update app1.testtab set mynumber = '234567890123' where name='ITO';
commit;

NAGISAユーザーでデータが追加されていることを確認します。

SQL> select * from app1.testtab;
NAME             MYNUMBER
---------------- ------------
SATO             123456789012
ITO              234567890123

さて、メンテナンスユーザーのPUKUユーザーは事務取扱担当者ではないので、MYNUMBER列にはアクセスできないようにしなくてはなりません。MYNUMBER列へのアクセスは事務取扱担当者のみに制限します。ここではSATO、ITOユーザーも自分のMYNUMBERデータにもアクセスできないような制限をかけます。新しく事務取扱担当者のNAGISAユーザーだけがMYNUMBER列にアクセスできるというポリシーファンクションを作成します。ファンクションを作成するためにSYSTEMユーザーでデータベースに接続し直す必要があります。

create or replace function app1.mynumberfunc
  (v_schema varchar2, v_objname varchar2)
  return varchar2
is begin
  if upper(sys_context('userenv','session_user')) = 'NAGISA' then
    return '1=1';
  else
    return '0=1';
  end if;
end;
/

最後にファイングレインアクセスコントロールのポリシーを作成します。

begin
  dbms_rls.add_policy(
    object_schema => 'app1',
    object_name => 'testtab',
    policy_name => 'sample_colpol',
    function_schema => 'app1',
    policy_function => 'mynumberfunc',
    sec_relevant_cols => 'mynumber',
    sec_relevant_cols_opt => dbms_rls.all_rows);
end;
/
これでメンテナンスユーザーのPUKUユーザーは 全件見えるけれどもMYNUMBER列は見えず、MYNUMBER列が見れるのは事務取扱担当者のNAGISAユーザーだけというアクセス制御が実現できます。

SYSTEMユーザーで接続

SQL> show user
ユーザーは"SYSTEM"です。
SQL> select * from app1.testtab;
レコードが選択されませんでした。

SATOユーザーで接続

SQL> show user
ユーザーは"SATO"です。
SQL> select * from app1.testtab;
NAME             M
---------------- -
SATO

PUKUユーザー(メンテナンスユーザー)で接続

SQL> show user
ユーザーは"PUKU"です。
SQL> select * from app1.testtab;
NAME             M
---------------- -
SATO
ITO

NAGISAユーザー(事務取扱担当者)で接続

SQL> show user
ユーザーは"NAGISA"です。
SQL> select * from app1.testtab;
NAME             MYNUMBER
---------------- ------------
SATO             123456789012
ITO              234567890123

既存のアプリケーションに手を入れずに特定の列を見せないようにする便利な機能ですが、アクセス権限がない場合に戻る値はNULLです。アプリケーション側でNULL値が戻った時の挙動を適切にハンドリングしていないと、エラーになったり、NULLという文字列が表示されてしまったりする可能性もあるのでご注意ください。

「もくじ」にもどる