はじめに
Oracle Data Redactionの動作確認を下記環境にて行いました。本記事にて実施結果を共有いたします。
SQL> SELECT banner_full FROM v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
事前準備
本記事にて使用するユーザと役割を下記に示します。
| ユーザ名 | 役割 |
|---|---|
| hr | 機密データを保持するテーブルの所有者 |
| sec | リダクションポリシの管理者 |
| ope | 機密データにアクセスできないユーザ |
| admin | 機密データにアクセス可能なデータ管理者 |
secユーザ作成
リダクションポリシの管理者として、secユーザを作成します。リダクションポリシを作成できるように、dbms_redactパッケージに対するEXECUTE権限を付与します。またリダクションポリシの情報を参照できるよう、select_catalog_roleを付与します。
SQL> CREATE USER sec;
ユーザーが作成されました。
SQL> password sec
secに対するパスワードを変更しています。
新規パスワード:
新規パスワードを再入力してください:
パスワードが変更されました。
SQL> GRANT connect, select_catalog_role TO sec;
権限付与が成功しました。
SQL> GRANT execute ON dbms_redact TO sec;
権限付与が成功しました。
opeユーザ作成
opeユーザを作成し、hrスキーマのemployeesテーブルを参照できるように権限を付与します。
SQL> CREATE USER ope;
ユーザーが作成されました。
SQL> PASSWORD ope
opeに対するパスワードを変更しています。
新規パスワード:
新規パスワードを再入力してください:
パスワードが変更されました。
SQL> GRANT connect TO ope;
権限付与が成功しました。
SQL> GRANT SELECT on hr.employees TO ope;
権限付与が成功しました。
adminユーザ作成
adminユーザを作成し、hrスキーマのemployeesテーブルを参照できるように権限を付与します。
SQL> CREATE USER admin;
ユーザーが作成されました。
SQL> PASSWORD admin
opeに対するパスワードを変更しています。
新規パスワード:
新規パスワードを再入力してください:
パスワードが変更されました。
SQL> GRANT connect TO admin;
権限付与が成功しました。
SQL> GRANT SELECT on hr.employees TO admin;
権限付与が成功しました。
完全リダクションポリシの作成
完全リダクションポリシを使用すると、指定したデータ列のすべての内容をリダクションできます。
リダクション時のデフォルト動作はデータ型ごとに下記となります。
- NUMBERデータ型の列はゼロ(0)に置き換えられます。
- 文字データ型の列は空白1つ( )に置き換えられます。
- 日付データ型の列は2001年1月1日に置き換えられます。
- DBMS_REDACT.UPDATE_FULL_REDACTION_VALUESプロシージャを使用することにより、このデフォルトを変更できますが、
データベースの再起動が必要になります。
検証ケース
hr.employees表の下記列に完全リダクションポリシを設定した際の動作を確認します。リダクションポリシを作成する際に、adminユーザはリダクション対象外になるよう設定します。
opeユーザで参照した際は、下記となる想定です。
- NUMBER型のsalary列が、
0にリダクションされる。 - VARCHAR2型のlast_name列が、
空白にリダクションされる。 - DATE型のhire_date列が、
01-01-2001にリダクションされる。
一方、adminユーザで参照した際は、リダクションされず実際の値が参照できる想定です。
最後に、テーブル所有者のhrユーザで参照した際の動作も確認します。
リダクションポリシ作成
dbms_redact.add_policyプロシージャを使用して、salary列に対するポリシを作成します。
- function_typeパラメータにリダクション方法を指定します。完全リダクションの場合は、
DBMS_REDACT.FULLを設定します。 - expressionパラメータにリダクションポリシが適用される条件を指定します。下記のように指定する事で、adminユーザ以外が、リダクションの対象となります。
SEC SQL> BEGIN
dbms_redact.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
column_Name => 'SALARY',
function_type => dbms_redact.full,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ADMIN''');
END;
/
2 3 4 5 6 7 8 9 10
PL/SQLプロシージャが正常に完了しました。
続いてlast_name列とhire_date列も追加します。二つ目以降の列を追加する場合は、dbms_redact.alter_policyプロシージャを使用します。
- actionパラメータに、
DBMS_REDACT.ADD_COLUMNを指定します。
SEC SQL> BEGIN
2 dbms_redact.alter_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 action => DBMS_REDACT.ADD_COLUMN,
7 column_Name => 'LAST_NAME',
8 function_type => dbms_redact.full
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
2 dbms_redact.alter_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 action => DBMS_REDACT.ADD_COLUMN,
7 column_Name => 'HIRE_DATE',
8 function_type => dbms_redact.full
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
redaction_policiesを問い合わせて、リダクションポリシを確認します。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
------------ ----------- ----------- --------------------------------------------------
HR EMPLOYEES EMP_POLICY SYS_CONTEXT('USERENV','SESSION_USER') != 'ADMIN'
また、reduction_columnsを問い合わせて、リダクション対象の列情報を確認します。
下記の通り、三つの列が完全リダクション対象として登録されている事が確認できます。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT
------------ ----------- ----------- --------------------------- ------------------- -------------- --------------------- --------------- ----------------- ----------
HR EMPLOYEES SALARY FULL REDACTION 0 0
HR EMPLOYEES LAST_NAME FULL REDACTION 0 0
HR EMPLOYEES HIRE_DATE FULL REDACTION 0 0
リダクション確認
opeユーザで参照処理を実行します。想定通り、リダクションされている事が確認できます。
OPE SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DAT SALARY
-------------------- ------------------------- -------- ----------
Jennifer 01-01-01 0
また、リダクション対象外のadminユーザで参照処理を実行した場合は、実際のデータが出力されます。
ADMIN SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DAT SALARY
-------------------- ------------------------- -------- ----------
Jennifer Whalen 03-09-17 4400
最後に、テーブル所有者のhrで参照処理を実行します。下記の通り、たとえ所有者であっても、リダクションされる動作となりました。
HR SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DAT SALARY
-------------------- ------------------------- -------- ----------
Jennifer 01-01-01 0
リダクションポリシ削除
リダクションポリシを削除するには、DBMS_REDACT.DROP_POLICYプロシージャを実行します。
先ほど、作成したEMP_POLICYを削除します。
SEC SQL> BEGIN
2 dbms_redact.drop_policy (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
レコードが選択されませんでした。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
レコードが選択されませんでした。
NULLIFYリダクションポリシの作成
NULLIFYリダクションポリシを使用すると、指定したデータ列のすべての内容をNULLにリダクションできます。本機能はOracle Database 12c 12.2以降で使用可能です。
検証ケース
hr.employyes表の下記列にNULLIFYリダクションポリシを設定した際の動作を確認します。
opeユーザで参照した際は、下記となる想定です。
- NUMBER型のsalary列が、
NULLにリダクションされる。 - VARCHAR2型のlast_name列が、
NULLにリダクションされる。 - DATE型のhire_date列が、
NULLにリダクションされる。
一方、adminユーザで参照した際は、リダクションされず実際の値が参照できる想定です。
リダクションポリシ作成
dbms_redact.add_policyプロシージャを使用して、salary列に対するポリシを作成します。
- function_typeパラメータにリダクション方法を指定します。NULLIFYリダクションの場合は、
DBMS_REDACT.NULLIFYを設定します。 - expressionパラメータにリダクションポリシが適用される条件を指定します。下記のように指定する事で、adminユーザ以外が、リダクションの対象となります。
SEC SQL> BEGIN
dbms_redact.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
column_Name => 'SALARY',
function_type => DBMS_REDACT.NULLIFY,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ADMIN''');
END;
/ 2 3 4 5 6 7 8 9 10
PL/SQLプロシージャが正常に完了しました。
salary列がNULLIFYリダクションの対象になりました。続いてlast_name列とhire_date列も追加します。二つ目以降の列を追加する場合は、dbms_redact.alter_policyプロシージャを使用します。
- actionパラメータに、
DBMS_REDACT.ADD_COLUMNを指定します。
SEC SQL> BEGIN
2 dbms_redact.alter_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 action => DBMS_REDACT.ADD_COLUMN,
7 column_Name => 'LAST_NAME',
8 function_type => dbms_redact.nullify
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
2 dbms_redact.alter_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 action => DBMS_REDACT.ADD_COLUMN,
7 column_Name => 'HIRE_DATE',
8 function_type => dbms_redact.nullify
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
redaction_policiesを問い合わせて、リダクションポリシを確認します。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
------------ ----------- ----------- --------------------------------------------------
HR EMPLOYEES EMP_POLICY SYS_CONTEXT('USERENV','SESSION_USER') != 'ADMIN'
また、reduction_columnsを問い合わせて、リダクション対象の列情報を確認します。
下記の通り、FUNCTION_TYPE列値が、NULLIFYリダクションとなっている事が確認できます。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT
------------ ----------- ----------- --------------------------- ------------------- -------------- --------------------- --------------- ----------------- ----------
HR EMPLOYEES HIRE_DATE NULLIFY REDACTION 0 0
HR EMPLOYEES SALARY NULLIFY REDACTION 0 0
HR EMPLOYEES LAST_NAME NULLIFY REDACTION 0 0
リダクション確認
opeユーザで参照処理を実行します。想定通り、リダクションされている事が確認できました。
OPE SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DAT SALARY
-------------------- ------------------------- -------- ----------
Jennifer
また、リダクション対象外のadminユーザで参照処理を実行した場合は、実際のデータが出力されます。
ADMIN SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DAT SALARY
-------------------- ------------------------- -------- ----------
Jennifer Whalen 03-09-17 4400
リダクションポリシ削除
リダクションポリシを削除するには、DBMS_REDACT.DROP_POLICYプロシージャを実行します。
先ほど、作成したEMP_POLICYを削除します。
SEC SQL> BEGIN
2 dbms_redact.drop_policy (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
レコードが選択されませんでした。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
レコードが選択されませんでした。
部分リダクションポリシの作成
部分リダクションポリシを使用すると、指定したデータ列の一部の内容をリダクションできます。
部分リダクションには、固定幅の文字セットを使用する必要があります。可変長の文字セットを使用する場合、後述の正規表現ベースのリダクションを使用する必要があります。
検証ケース
hr.employyes表のhire_date列に部分リダクションポリシを設定し、年は実際のデータを表示し、月を6、日は3でリダクションした際の動作を確認します。
リダクションポリシ作成
dbms_redact.add_policyプロシージャを使用して、部分リダクションポリシを作成します。
- function_typeパラメータにリダクション方法を指定します。部分リダクションの場合は、
DBMS_REDACT.PARTIALを設定します。 - function_parametersパラメータに次に示す順序で設定します。
月をリダクションする場合は、mの後に1から12を追加します。
日をリダクションする場合は、dの後に1から31を追加します。
年をリダクションする場合は、yの後に1から9999を追加します、
時をリダクションする場合は、hの後に0から23を追加します。
分をリダクションする場合は、mの後に0から59を追加します。
秒をリダクションする場合は、sの後ろに0から59を追加します。 - expressionパラメータにリダクションポリシが適用される条件を指定します。下記のように指定する事で、adminユーザ以外が、リダクションの対象となります。
SEC SQL> SEC SQL> BEGIN
dbms_redact.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
column_Name => 'HIRE_DATE',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'm6d3YHMS',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ADMIN''');
END;
/ 2 3 4 5 6 7 8 9 10 11
PL/SQLプロシージャが正常に完了しました。
redaction_policiesを問い合わせて、リダクションポリシを確認します。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
------------ ----------- ----------- --------------------------------------------------
HR EMPLOYEES EMP_POLICY SYS_CONTEXT('USERENV','SESSION_USER') != 'ADMIN'
また、reduction_columnsを問い合わせて、リダクション対象の列情報を確認します。
下記の通り、FUNCTION_TYPE列値が、PARTIAL REDACTIONとなっており、FUNCTION_PARAMETERS列値に日時データのリダクションフォーマットが設定されている事が確認できます。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT
------------ ----------- ----------- --------------------------- ------------------- -------------- --------------------- --------------- ----------------- ----------
HR EMPLOYEES HIRE_DATE PARTIAL REDACTION m6d3YHMS 0 0
リダクション確認
opeユーザで参照処理を実行します。想定通り、リダクションされている事が確認できました。
OPE SQL> SELECT first_name, hire_date FROM hr.employees WHERE department_id = 30;
FIRST_NAME HIRE_DATE
-------------------- ----------
Den 2002-06-03
Alexander 2003-06-03
Shelli 2005-06-03
Sigal 2005-06-03
Guy 2006-06-03
Karen 2007-06-03
6行が選択されました。
また、リダクション対象外のadminユーザで参照処理を実行した場合は、実際のデータが出力されます。
ADMIN SQL> SELECT first_name, hire_date FROM hr.employees WHERE department_id = 30;
FIRST_NAME HIRE_DATE
-------------------- ----------
Den 2002-12-07
Alexander 2003-05-18
Shelli 2005-12-24
Sigal 2005-07-24
Guy 2006-11-15
Karen 2007-08-10
6行が選択されました。
リダクションポリシ削除
リダクションポリシを削除するには、DBMS_REDACT.DROP_POLICYプロシージャを実行します。
先ほど、作成したEMP_POLICYを削除します。
SEC SQL> BEGIN
2 dbms_redact.drop_policy (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
レコードが選択されませんでした。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
レコードが選択されませんでした。
正規表現ベースのリダクションポリシの作成
正規表現ベースのリダクションポリシを使用すると、可変長文字列の一部の内容をリダクションできます。
検証ケース
hr.employyes表のphone_number列にカスタム正規表現を使用したリダクションポリシを設定し、電話番号の最初の二つのブロックを***でリダクションした際の動作を確認します。
リダクションポリシ作成
dbms_redact.add_policyプロシージャを使用して、正規表現リダクションポリシを作成します。
- function_typeパラメータにリダクション方法を指定します。正規表現リダクションの場合は、
DBMS_REDACT.REGEXPまたは、DBMS_REDACT.REGEXP_WIDTHを設定します。 - regexp_patternパラメータに検索パターンを設定します。
- regexp_replace_stringパラメータに、リダクション方法を指定します。
- expressionパラメータにリダクションポリシが適用される条件を指定します。下記のように指定する事で、adminユーザ以外が、リダクションの対象となります。
SEC SQL> BEGIN
2 dbms_redact.add_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 column_Name => 'PHONE_NUMBER',
7 function_type => dbms_redact.regexp,
8 regexp_pattern => '(\d{3})\.(\d{2,3})(\.\d{4}.*)',
9 regexp_replace_string => '***.***\3',
10 expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ADMIN'''
11 );
12 END;
13 /
PL/SQLプロシージャが正常に完了しました。
redaction_policiesを問い合わせて、リダクションポリシを確認します。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
------------ ----------- ----------- --------------------------------------------------
HR EMPLOYEES EMP_POLICY SYS_CONTEXT('USERENV','SESSION_USER') != 'ADMIN'
また、reduction_columnsを問い合わせて、リダクション対象の列情報を確認します。
下記の通り、FUNCTION_TYPE列値が、PARTIAL REDACTIONとなっており、FUNCTION_PARAMETERS列値に日時データのリダクションフォーマットが設定されている事が確認できます。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT
------------ ----------- ------------ --------------------------- ------------------- ----------------------------- --------------------- --------------- ----------------- ----------
HR EMPLOYEES PHONE_NUMBER REGEXP REDACTION (\d{3})\.(\d{2,3})(\.\d{4}.*) ***.***\3 1 0
リダクション確認
opeユーザで参照処理を実行します。想定通り、リダクションされている事が確認できました。
OPE SQL> SELECT first_name, phone_number FROM hr.employees WHERE salary BETWEEN 6500 AND 7000;
FIRST_NAME PHONE_NUMBER
-------------------- --------------------
Susan ***.***.7777
Luis ***.***.4567
Shanta ***.***.4234
Oliver ***.***.1344.486508
Sarath ***.***.1345.529268
David ***.***.1346.529268
Kimberely ***.***.1644.429263
7行が選択されました。
また、リダクション対象外のadminユーザで参照処理を実行した場合は、実際のデータが出力されます。
ADMIN SQL> SELECT first_name, phone_number FROM hr.employees WHERE salary BETWEEN 6500 AND 7000;
FIRST_NAME PHONE_NUMBER
-------------------- --------------------
Susan 515.123.7777
Luis 515.124.4567
Shanta 650.123.4234
Oliver 011.44.1344.486508
Sarath 011.44.1345.529268
David 011.44.1346.529268
Kimberely 011.44.1644.429263
7行が選択されました。
リダクションポリシ削除
リダクションポリシを削除するには、DBMS_REDACT.DROP_POLICYプロシージャを実行します。
先ほど、作成したEMP_POLICYを削除します。
SEC SQL> BEGIN
2 dbms_redact.drop_policy (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
レコードが選択されませんでした。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
レコードが選択されませんでした。
ランダムリダクションポリシの作成
ランダムリダクションポリシを使用すると、指定したデータ列の内容をランダムに生成した値でリダクションできます。
LOB列はサポートされません。
検証ケース
hr.employyes表の下記列にランダムリダクションポリシを設定した際の動作を確認します。
リダクションポリシ作成
dbms_redact.add_policyプロシージャを使用して、salary列に対するポリシを作成します。
- function_typeパラメータにリダクション方法を指定します。完全リダクションの場合は、
DBMS_REDACT.RANDOMを設定します。 - expressionパラメータにリダクションポリシが適用される条件を指定します。下記のように指定する事で、adminユーザ以外が、リダクションの対象となります。
SEC SQL> BEGIN
dbms_redact.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
column_Name => 'SALARY',
function_type => DBMS_REDACT.RANDOM,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ADMIN''');
END;
/
2 3 4 5 6 7 8 9 10
PL/SQLプロシージャが正常に完了しました。
続いてlast_name列とhire_date列も追加します。二つ目以降の列を追加する場合は、dbms_redact.alter_policyプロシージャを使用します。
- actionパラメータに、
DBMS_REDACT.ADD_COLUMNを指定します。
SEC SQL> BEGIN
dbms_redact.alter_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
action => DBMS_REDACT.ADD_COLUMN,
column_Name => 'LAST_NAME',
function_type => dbms_redact.random
);
END;
/
2 3 4 5 6 7 8 9 10 11
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
dbms_redact.alter_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
action => DBMS_REDACT.ADD_COLUMN,
column_Name => 'HIRE_DATE',
function_type => dbms_redact.random
);
END;
/
2 3 4 5 6 7 8 9 10 11
PL/SQLプロシージャが正常に完了しました。
redaction_policiesを問い合わせて、リダクションポリシを確認します。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
------------ ----------- ----------- --------------------------------------------------
HR EMPLOYEES EMP_POLICY SYS_CONTEXT('USERENV','SESSION_USER') != 'ADMIN'
また、reduction_columnsを問い合わせて、リダクション対象の列情報を確認します。
下記の通り、三つの列がランダムリダクション対象として登録されている事が確認できます。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS REGEXP_PATTERN REGEXP_REPLACE_STRING REGEXP_POSITION REGEXP_OCCURRENCE REGEXP_MAT
------------ ----------- ------------ --------------------------- ------------------- ----------------------------- --------------------- --------------- ----------------- ----------
HR EMPLOYEES HIRE_DATE RANDOM REDACTION 0 0
HR EMPLOYEES SALARY RANDOM REDACTION 0 0
HR EMPLOYEES LAST_NAME RANDOM REDACTION 0 0
リダクション確認
opeユーザで参照処理を実行します。毎回ランダムな値にリダクションされている事が確認できます。
OPE SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
-------------------- ------------------------- ---------- ----------
Jennifer Tl<Z<y 0016-05-27 1341
OPE SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
-------------------- ------------------------- ---------- ----------
Jennifer 3+eeqI 0151-03-17 1008
OPE SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
-------------------- ------------------------- ---------- ----------
Jennifer 6:o-88 0076-06-04 3125
OPE SQL>
また、リダクション対象外のadminユーザで参照処理を実行した場合は、実際のデータが出力されます。
ADMIN SQL> SELECT first_name, last_name, hire_date, salary FROM hr.employees WHERE department_id = 10;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
-------------------- ------------------------- ---------- ----------
Jennifer Whalen 2003-09-17 4400
リダクションポリシ削除
リダクションポリシを削除するには、DBMS_REDACT.DROP_POLICYプロシージャを実行します。
先ほど、作成したEMP_POLICYを削除します。
SEC SQL> BEGIN
2 dbms_redact.drop_policy (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
レコードが選択されませんでした。
SEC SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter FROM redaction_columns;
レコードが選択されませんでした。
名前付きデータリダクションポリシ式
名前付きデータリダクションポリシ式を使用するとポリシ式を集中管理する事が可能となります。本機能はOracle Database 12c 12.2以降で使用可能です。
検証ケース
最初に下記テーブルの列に対して、リダクションポリシを作成します。
- hr.employees表のsalary列
- hr.employees表のlast_name列
- hr.jobs表のjob_title列
次にhrユーザをリダクション対象外とする名前付きリダクションポリシ式を作成し、下記テーブルの列に対して、ポリシ式を適用します。
- hr.employees表のsalary列
- hr.jobs表のjob_title列
結果として、hr.employees表のsalary列とhr.jobs表のjob_title列の参照はリダクションされず、hr.employees表のlast_name列の参照はリダクションされる動作を確認します。
リダクションポリシ作成
下記テーブルの列に対して、完全リダクションポリシを作成します。
- hr.employees表のsalary列
- hr.employees表のlast_name列
- hr.jobs表のjob_title列
SEC SQL> BEGIN
2 dbms_redact.add_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 column_Name => 'SALARY',
7 function_type => dbms_redact.full,
8 expression => '1=1'
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
2 dbms_redact.alter_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_POLICY',
6 action => DBMS_REDACT.ADD_COLUMN,
7 column_Name => 'LAST_NAME',
8 function_type => dbms_redact.full
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
2 dbms_redact.add_policy(
3 object_schema => 'HR',
4 object_name => 'JOBS',
5 policy_name => 'JOBS_POLICY',
6 column_Name => 'JOB_TITLE',
7 function_type => dbms_redact.full,
8 expression => '1=1'
9 );
10 END;
11 /
PL/SQLプロシージャが正常に完了しました。
念のため、動作確認しておきます。想定通り、リダクションされました。
HR SQL> SELECT first_name, last_name, salary FROM employees WHERE department_id = 10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer 0
HR SQL> SELECT job_id, job_title FROM jobs WHERE job_id = 'AD_PRES';
JOB_ID JOB_TITLE
---------- -----------------------------------
AD_PRES
名前付きリダクションポリシ式の作成
hrユーザをリダクション対象外とする名前付きリダクションポリシ式を作成します。
SEC SQL> BEGIN
2 DBMS_REDACT.CREATE_POLICY_EXPRESSION(
3 policy_expression_name => 'hr_redact_pol',
4 expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR''');
5 END;
6 /
PL/SQLプロシージャが正常に完了しました。
名前付きリダクションポリシ式は、REDACTION_EXPRESSIONSを問い合わせることで確認可能です。
SEC SQL> SELECT policy_expression_name, expression, object_owner, object_name, column_name FROM redaction_expressions;
POLICY_EXPRESSION_NAME EXPRESSION OBJECT_OWNER OBJECT_NAME COLUMN_NAME
---------------------- -------------------------------------------------- ------------ ----------- -----------
hr_redact_pol SYS_CONTEXT('USERENV','SESSION_USER') != 'HR'
リダクションポリシ式の適用
リダクションポリシ式を下記テーブルの列に対して適用します。
- hr.employees表のsalary列
- hr.jobs表のjob_title列
SEC SQL> BEGIN
2 DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
3 object_schema => 'hr',
4 object_name => 'employees',
5 column_name => 'salary',
6 policy_expression_name => 'hr_redact_pol'
7 );
8 END;
9 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
2 DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
3 object_schema => 'hr',
4 object_name => 'jobs',
5 column_name => 'job_title',
6 policy_expression_name => 'hr_redact_pol'
7 );
8 END;
9 /
PL/SQLプロシージャが正常に完了しました。
redaction_expressionsを確認します。
SEC SQL> SELECT policy_expression_name, expression, object_owner, object_name, column_name FROM redaction_expressions;
POLICY_EXPRESSION_NAME EXPRESSION OBJECT_OWNER OBJECT_NAME COLUMN_NAME
---------------------- -------------------------------------------------- ------------ ----------- -----------
hr_redact_pol SYS_CONTEXT('USERENV','SESSION_USER') != 'HR' HR JOBS JOB_TITLE
hr_redact_pol SYS_CONTEXT('USERENV','SESSION_USER') != 'HR' HR EMPLOYEES SALARY
リダクション確認
hrユーザによる下記参照処理を実行します。
- hr.employees表のsalary列 (リダクションされない事)
- hr.employees表のlast_name列 (リダクションされる事)
- hr.jobs表のjob_title列 (リダクションされない事)
HR SQL> SELECT first_name, last_name, salary FROM employees WHERE department_id = 10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer 4400
HR SQL> SELECT job_id, job_title FROM jobs WHERE job_id = 'AD_PRES';
JOB_ID JOB_TITLE
---------- -----------------------------------
AD_PRES President
想定通り、動作する事が確認できました。
リダクションポリシ式の削除
ポリシ式を使用している列が存在する場合、ポリシ式を削除できないため、まずは列との関連を削除します。
SEC SQL> BEGIN
2 DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
3 object_schema => 'hr',
4 object_name => 'employees',
5 column_name => 'salary',
6 policy_expression_name => null
7 );
8 END;
9 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> BEGIN
2 DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
3 object_schema => 'hr',
4 object_name => 'jobs',
5 column_name => 'job_title',
6 policy_expression_name => null
7 );
8 END;
9 /
PL/SQLプロシージャが正常に完了しました。
リダクションポリシ式を削除します。
SEC SQL> BEGIN
2 DBMS_REDACT.DROP_POLICY_EXPRESSION(
3 policy_expression_name => 'hr_redact_pol'
4 );
5 END;
6 /
PL/SQLプロシージャが正常に完了しました。
SEC SQL> SELECT * FROM redaction_expressions;
レコードが選択されませんでした。
リダクションポリシからのユーザの除外
EXEMPT REDACTION POLICYシステム権限を付与されたユーザは、リダクションの対象外となるため、実際のデータを参照する事が可能です。
注意点としては、EXEMPT REDACTION POLICYシステム権限は、EXP_FULL_DATABASEロールに含まれているため、EXP_FULL_DATABASEロールがリダクションしたいユーザに付与されていると、リダクションできなくなりますのでご注意ください。
使用上の考慮事項
Oracle Data Redactionの一般的な使用上のガイドラインに下記の記載があります。
SQL文のGROUP BY句で使用されるSQL式にリダクションされた列を含めることはできません。Oracleではこのような動作はサポートされないため、「ORA-00979: GROUP BYの式ではありません。」エラーが発生します。これはSELECTリスト内の式がデータ・リダクションにより内部で変更される必要があるために発生しますが、GROUP BY句(その時点ではデータ・リダクションにより更新されていない)を処理するときに見つからなくなっている原因となり、この予期せぬエラー・メッセージが発生します。
上記ガイドラインは、リダクション対象ユーザにのみ、該当する認識でしたが、リダクションポリシのexpressionパラメータを使用して、リダクションを除外しているユーザにおいても、該当するようです。
下記の通り、adminユーザを除外したリダクションポリシがあります。
SEC SQL> SELECT object_owner, object_name, policy_name, expression FROM redaction_policies;
OBJECT_OWNER OBJECT_NAME POLICY_NAME EXPRESSION
------------ ----------- ----------- --------------------------------------------------
HR EMPLOYEES EMP_POLICY SYS_CONTEXT('USERENV','SESSION_USER') != 'ADMIN'
SEC SQL> SELECT object_owner, object_name, column_name FROM redaction_columns;
OBJECT_OWNER OBJECT_NAME COLUMN_NAME
------------ ----------- ------------
HR EMPLOYEES LAST_NAME
HR EMPLOYEES HIRE_DATE
HR EMPLOYEES SALARY
adminユーザが、リダクション列のhire_dateをGROUP BYに指定したSQLを実行すると、ORA-00979: GROUP BYの式ではありません。が発生します。
ADMIN SQL> SELECT TRUNC(hire_date), COUNT(*)
2 FROM hr.employees
3 GROUP BY
4 TRUNC(hire_date);
SELECT TRUNC(hire_date), COUNT(*)
*
行1でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。
もちろん、リダクション対象ユーザopeで実行した場合も同じエラーが発生します。
OPE SQL> SELECT TRUNC(hire_date), COUNT(*)
2 FROM hr.employees
3 GROUP BY
4 TRUNC(hire_date);
SELECT TRUNC(hire_date), COUNT(*)
*
行1でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。
opeにEXEMPT REDACTION POLICYシステム権限を付与した場合は、エラーになりません。
PE SQL> SELECT TRUNC(hire_date), COUNT(*)
2 FROM hr.employees
3 GROUP BY
4 TRUNC(hire_date);
TRUNC(HIRE COUNT(*)
---------- ----------
2003-05-18 1
2007-12-12 1
(省略)
2006-04-24 1
98行が選択されました。
下記のガイドラインもご注意ください。
SQL文のDISTINCT句とORDER BY句の両方で使用されるSQL式にリダクションされた列を含めることはできません。Oracleではこのような動作はサポートされないため、「ORA-01791: SELECT式が無効です。」エラーが発生します。これはSELECTリスト内の式がデータ・リダクションにより内部で変更される必要があるために発生しますが、GROUP BY句を処理するときに見つからなくなっている原因となり、この予期せぬエラー・メッセージが発生します。
SQLの例を挙げます。
ADMIN SQL> SELECT DISTINCT hire_date FROM hr.employees ORDER BY hire_date;
SELECT DISTINCT hire_date FROM hr.employees ORDER BY hire_date
*
行1でエラーが発生しました。:
ORA-01791: SELECT式が無効です。
下記のガイドラインによると、リダクションポリシから除外されないユーザが対象のように読み取れますが、expressionパラメータを使用して除外したユーザも該当するようです。
リダクションされた列のUNIONが問合せに含まれ、UNIONの各ブランチにあるリダクション・ポリシーが同じではない場合は、「ORA-28094: SQL構造はデータ・リダクションでサポートされていません」エラーが発生します。Oracle Databaseではリダクションされた列を含む複雑なSQLはサポートされていませんが、複雑なSQLを使用して問合せを実行するユーザーにEXEMPT REDACTION POLICYシステム権限がある場合、このエラーは発生しません。このエラーは、リダクション・ポリシーから除外されないユーザーに対してのみ発生し、これらのユーザーはリダクションされた列を含む複雑なSQLは実行できませんが、ポリシーから除外されるユーザーには許可されます。
SQLの例を挙げます。last_name列がリダクションされた列となります。
ADMIN SQL> SELECT last_name FROM hr.employees
2 UNION
3 SELECT first_name FROM hr.employees
4 ;
SELECT last_name FROM hr.employees
*
行1でエラーが発生しました。:
ORA-28094: SQL構造はデータ・リダクションでサポートされていません
おわりに
本記事では、Oracle Data Redactionの代表的なリダクション方法について動作確認を行いました。マニュアルに記載されている内容をなぞるだけでも、新たな気付きを得られることがあるため、今後も続けていきたいと思います。