Oracle Data Redactionの動作確認と考慮事項

# はじめに
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;

権限付与が成功しました。
```

# 完全リダクションポリシの作成
完全リダクションポリシを使用すると、指定したデータ列のすべての内容をリダクションできます。

:::note info
リダクション時のデフォルト動作はデータ型ごとに下記となります。
* 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;

レコードが選択されませんでした。
```

# 部分リダクションポリシの作成
部分リダクションポリシを使用すると、指定したデータ列の一部の内容をリダクションできます。

:::note info
部分リダクションには、固定幅の文字セットを使用する必要があります。可変長の文字セットを使用する場合、後述の正規表現ベースのリダクションを使用する必要があります。
:::

### 検証ケース
hr.employyes表のhire_date列に部分リダクションポリシを設定し、年は実際のデータを表示し、月を6、日は3でリダクションした際の動作を確認します。

### リダクションポリシ作成
dbms_redact.add_policyプロシージャを使用して、部分リダクションポリシを作成します。
* function_typeパラメータにリダクション方法を指定します。部分リダクションの場合は、`DBMS_REDACT.PARTIAL`を設定します。
* function_parametersパラメータに次に示す順序で設定します。<BR>月をリダクションする場合は、mの後に1から12を追加します。<BR>日をリダクションする場合は、dの後に1から31を追加します。<BR>年をリダクションする場合は、yの後に1から9999を追加します、<BR>時をリダクションする場合は、hの後に0から23を追加します。<BR>分をリダクションする場合は、mの後に0から59を追加します。<BR>秒をリダクションする場合は、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;

レコードが選択されませんでした。
```

# ランダムリダクションポリシの作成
ランダムリダクションポリシを使用すると、指定したデータ列の内容をランダムに生成した値でリダクションできます。

:::note info
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の一般的な使用上のガイドライン](https://docs.oracle.com/cd/F19136_01/asoag/oracle-data-redaction-use-with-oracle-database-features.html#GUID-FFCACD39-626A-4851-B4B2-D38225C91707)に下記の記載があります。

:::note info
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行が選択されました。
```

下記のガイドラインもご注意ください。

:::note info
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パラメータを使用して除外したユーザも該当するようです。

:::note info
リダクションされた列の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の代表的なリダクション方法について動作確認を行いました。マニュアルに記載されている内容をなぞるだけでも、新たな気付きを得られることがあるため、今後も続けていきたいと思います。