※ 本記事は、Richard Evansによる”Using Data Redaction in Oracle Database 23ai“を翻訳したものです。
Oracle Database 23.6では、Data Redactionの更新により、機密データを保護し、コンプライアンス要件を満たすための堅牢な新しいオプションが提供されます。次に、新機能の概要と、そのいくつかの詳細を示します:
- 拡張ビューのサポート: ビューのターゲット・リスト内の列をリダクションします(CREATE VIEWとインライン・ビューの両方を含む)。
- 仮想列のベース列のリダクション・ポリシー: 仮想列のベース列をリダクションします。
- SET演算子を使用したインライン・ビューのサポート: データ・リダクション・ポリシーを持つ列には、共通表式(WITH句)、OUTER JOINおよびCOUNTやMAXなどの集計関数を使用します。
- DISTINCT、GROUP BYおよびORDER BYに対する拡張問合せのサポート: リダクションされた列に対する式をGROUP BYとSELECTリストの両方に含める問合せが完全にサポートされるようになりました。
Oracleサンプル・スキーマを使用した例を含む新機能の詳細を次に示します。フォローする場合は、Oracleサンプル・スキーマ、販売履歴(SH)をインストールする必要があります。最新のコピーはここにあります。それ以外の場合は、コマンドおよび出力を読み取って、Oracle Database 23.6のOracle Data Redactionの改善点を理解できます。
読取り専用ユーザーを作成し、SHスキーマ・オブジェクトに対する適切な権限を付与します。
| create user sh_reader identified by Oracle123;
grant create session to sh_reader;
grant read any table on schema sh to sh_reader;
|
まず、列を指定せずにデータ・リダクション・ポリシーを作成します。このポリシーに基づいて、リダクションする列を追加し、特定の列に異なるリダクション式を指定することもできます。このデフォルト・ポリシーは、SHデータベース・ユーザーを除くすべてのユーザーの値を常にリダクションします。
| BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SH'''
);
END;
/
|
拡張ビューのサポート
データベース・ビューは、データ・セキュリティ、データ抽象化、データ集計またはデータ変換の形式として、データ・アクセスを簡略化するために頻繁に使用されます。このリリースでは、CREATE VIEWとインライン・ビューの両方を含む、リダクションされた列の式をビューのターゲット・リストに含めることができるようになりました。たとえば、CUSTOMER_VIEWという顧客データの簡易ビューを作成します。このビューは、姓と名、および資材ステータスを、レポート・ツールで理解できる形式に変換します。
| CREATE OR REPLACE VIEW sh.customer_view AS
SELECT
cust_id,
UPPER(cust_first_name) AS FIRST_NAME,
UPPER(cust_last_name) AS LAST_NAME,
CASE
WHEN cust_marital_status IS NULL OR TRIM(cust_marital_status) = '' THEN 'UNKNOWN'
ELSE UPPER(cust_marital_status)
END AS marital_status,
cust_gender AS GENDER,
cust_email AS EMAIL,
cust_postal_code AS POSTAL_CODE,
cust_credit_limit AS CREDIT_LIMIT
FROM sh.customers;
|
ベース表のCUST_MARITAL_STATUS列にリダクション・ポリシーを適用します:
| BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA',
column_name => 'CUST_MARITAL_STATUS',
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.FULL);
END;
/
|
次に、SH_VIEWERとして接続し、CUSTOMER_VIEWを問い合せます:
| SELECT CUST_ID, FIRST_NAME, LAST_NAME, EMAIL, MARITAL_STATUS
FROM sh.customer_view
WHERE cust_id IN (101, 103, 176, 201);
|
以前のリリースでは、EXEMPT REDACTION POLICY権限が付与されていなければ、次のエラー・メッセージが表示されます:
| ORA-28094: SQL construct not supported by data redaction
|
Oracle Database 23.6では、MARITAL_STATUS列がリダクションされた状態で返されるデータが表示されます。
| SELECT CUST_ID, FIRST_NAME, LAST_NAME, EMAIL, MARITAL_STATUS
FROM sh.customer_view
WHERE cust_id IN (101, 103, 176, 201);
|
| CUST_ID FIRST_NAME LAST_NAME EMAIL MARITAL_STATUS
|
| ---------- --------------- --------------- -------------------------------------- --------------
|
| 101 LOU FITZ lou.fitz@company2.example.com
|
| 103 AILEEN NEWKIRK aileen.newkirk@company2.example.com
|
| 176 CARIN LIGHTFOOT carin.lightfoot@company2.example.com
|
| 201 ALYSSA CLIPP alyssa.clipp@company2.example.com
|
デモンストレーションしたように、このビューは、FIRST_NAME列およびLAST_NAME列に変換を適用し、基礎となるデータに対するリダクション・ポリシーを維持しながら、婚姻区分列形式を変更します。これにより、ユーザーはVIEまたは表と同じ方法でデータを表示できます。
仮想列のベース列のリダクション・ポリシー
ユーザーに個々の列の問合せを依頼するかわりに、複数の列を1つの仮想列に結合できます。たとえば、問合せ結果に顧客の郵便番号を含めなくても、この列は仮想列FULL_ADDRESSに含まれます。以前は、CUST_POSTAL_CODE列にリダクション・ポリシーを適用できませんでした。23.6 では、次の例があります。次の仮想列の作成を実行して、CUST_STREET_ADDRESS、CUST_CITY、CUST_STATE_PROVINCEおよびCUST_POSTAL_CODEをFULL_ADDRESSに結合します。
| ALTER TABLE sh.customers ADD (full_address AS
(cust_street_address || ', ' || cust_city ||
', ' || cust_state_province || ' ' || cust_postal_code));
|
| BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'SH',
object_name => 'CUSTOMERS',
column_name => 'CUST_POSTAL_CODE',
policy_name => 'REDACT_SENSITIVE_DATA',
function_type => DBMS_REDACT.FULL
);
END;
/
|
以前のリリースでは、仮想列の一部である列にリダクション・ポリシーを適用できませんでした。次のエラー・メッセージが表示されます:
| ORA-28073: The column "CUST_POSTAL_CODE" has an unsupported data type or attribute.
|
また、以前のリリースでは、リダクション・ポリシーを設定した後に仮想列を追加しようとすると、次のようなエラーが表示されます:
| ORA-28083: A redacted column was referenced in a virtual column expression.
|
23.6 では、リダクション・ポリシーをCUST_POSTAL_CODE列に適用でき、SH_READERに次のような結果が表示されるようになりました:
| SELECT cust_id, cust_last_name, full_address
FROM sh.customers
WHERE cust_id IN (101, 103, 176, 201);
|
| CUST_ID CUST_LAST_NAME FULL_ADDRESS
---------- -------------- ------------
101 FITZ
103 NEWKIRK
176 LIGHTFOOT
201 CLIPP
|
ノート: このリリースでは、ベース列のみに仮想列自体にリダクション・ポリシーを適用することはできません。
REDACTION_COLUMNSビューを問い合せると、19cの列が2つ、23.6の列が2つ表示されます。
| COLUMN column_name FORMAT a25
COLUMN function_type FORMAT a25
SELECT column_name, function_type
FROM REDACTION_COLUMNS
WHERE OBJECT_OWNER = 'SH'
AND OBJECT_NAME = 'CUSTOMERS';
|
| COLUMN_NAME FUNCTION_TYPE
-------------------- -----------------
CUST_MARTIAL_STATUS FULL REDACTION
CUST_POSTAL_CODE FULL REDACTION
|
SET演算子を使用したインライン・ビューのサポート
ビジネス・アナリストは、貴社の顧客の与信限度額データを保護するよう依頼しました。ビジネス・アナリストは、給与データを公開せずに、平均与信限度額を上回る顧客数と下回る顧客数を識別する必要があります。そのためには、共通表式(WITH句)、OUTER JOINおよびCOUNTやMAXなどの集計関数を含む複雑な問合せをアナリストが記述する必要があります。Oracle Data Redaction 23.6でこれを実行できます!
この演習では、ビジネス・アナリストがCUST_POSTAL_CODE列を参照できる必要があります。データ・リダクション・ポリシーからその列を削除し、CUST_CREDIT_LIMIT列を完全リダクション列として追加します。
| BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA',
column_name => 'CUST_POSTAL_CODE',
action => DBMS_REDACT.DROP_COLUMN);
END;
/
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA',
column_name => 'CUST_CREDIT_LIMIT',
action => DBMS_REDACT.ADD_COLUMN,
function_type => DBMS_REDACT.FULL);
END;
/
|
次の問合せでは、郵便番号別に顧客をグループ化し、与信限度額を上回る顧客数と下回る顧客数を表示する表が作成され、出力に最高、最低または平均与信限度額のデータは提供されません。
| COLUMN cust_postal_code HEADING 'Postal|Code'
COLUMN total_customer_count HEADING 'Total|Customer|Count'
COLUMN num_customers_below_avg HEADING 'Num Customers|Below Avg'
COLUMN num_customers_above_avg HEADING 'Num Customers|Above Avg'
COLUMN highest_credit_limit HEADING 'Highest|Credit Limit'
COLUMN lowest_credit_limit HEADING 'Lowest|Credit Limit'
COLUMN average_credit_limit HEADING 'Average|Credit Limit'
WITH avg_credit_limits AS (
SELECT cust_postal_code, AVG(cust_credit_limit) AS avg_credit_limit
FROM sh.customers
WHERE cust_postal_code IN ('55787', '63736', '38082')
AND cust_credit_limit IS NOT NULL
GROUP BY cust_postal_code
)
SELECT
c.cust_postal_code,
COUNT(*) AS total_customer_count,
COUNT(CASE WHEN c.cust_credit_limit < acl.avg_credit_limit THEN 1 END) AS num_customers_below_avg,
COUNT(CASE WHEN c.cust_credit_limit > acl.avg_credit_limit THEN 1 END) AS num_customers_above_avg,
MAX(c.cust_credit_limit) AS highest_credit_limit,
ROUND(MIN(c.cust_credit_limit), 0) AS lowest_credit_limit,
ROUND(AVG(c.cust_credit_limit), 0) AS average_credit_limit
FROM sh.customers c
JOIN avg_credit_limits acl ON c.cust_postal_code = acl.cust_postal_code
WHERE c.cust_postal_code IN ('55787', '63736', '38082')
GROUP BY c.cust_postal_code
ORDER BY c.cust_postal_code;
|
SHは与信限度額のデータを見ることができます。
| Total
Postal Customer Num Customers Num Customers Highest Lowest Average
Code Count Below Avg Above Avg Credit Limit Credit Limit Credit Limit
---------- ---------- ------------- ------------- ------------ ------------ ------------
38082 695 359 336 15000 1500 5947
55787 612 301 311 15000 1500 6223
63736 637 334 303 15000 1500 5830
|
SH_READERは与信限度額データを表示しません。
| Total
Postal Customer Num Customers Num Customers Highest Lowest Average
Code Count Below Avg Above Avg Credit Limit Credit Limit Credit Limit
---------- ---------- ------------- ------------- ------------ ------------ ------------
38082 695 359 336 0 0 0
55787 612 301 311 0 0 0
63736 637 334 303 0 0 0
|
表示する必要がある列の平均が表示されますが、未リダクションの与信限度額データは表示されません。Data Redactionは非定型SQLのWHERE句に制限を設けないため、問合せ対象の列にデータ・リダクション・ポリシーがあり、リダクションされた値のみが表示されている場合でも、WHERE句を反復的に使用して実際のデータを見分けることができます。ただし、上記のような分析レポートが用意されている場合は、クエリを書き直したり、レポート・ツールを更新することなく、機密データが公開されるリスクを軽減できます。
DISTINCT、GROUP BYおよびORDER BYに対する拡張問合せのサポート
ほとんどの分析問合せまたは拡張問合せは、DISTINCT句、GROUP BY句およびORDER BY句のサポートに依存します。リダクションされた列に対する式をGROUP BYとSELECTリストの両方に含める問合せが完全にサポートされるようになりました。さらに、SELECT DISTINCT句およびORDER BY句を使用する問合せにリダクションされた列を含めることができるため、問合せ機能を損なうことなく機密データを保護できます。
分析タイプの問合せで機密データをリダクションする方法のデモンストレーションを行います。この問合せは、リダクションされた列から個別値を選択し、ソート時にデータのプライバシを維持して順序付けします。
| COLUMN cust_postal_code HEADING 'Postal|Code'
COLUMN total_credit_limit HEADING 'Total|Credit'
SELECT cust_postal_code, SUM(cust_credit_limit) AS TOTAL_CREDIT_LIMIT
FROM sh.customers
GROUP BY cust_postal_code
ORDER BY total_credit_limit DESC
FETCH FIRST 10 ROWS ONLY; ;
|
このリリース前では、次のようなエラーが表示されます:
| ORA-28094: SQL construct not supported by data redaction
|
しかし、23.6では、各部門の給与の合計でソートされたデータを表示できます。
| COLUMN cust_postal_code HEADING 'Postal|Code'
COLUMN total_credit_limit HEADING 'Total|Credit'
SELECT cust_postal_code, SUM(cust_credit_limit) AS total_credit_limit
FROM sh.customers
GROUP BY cust_postal_code
ORDER BY total_credit_limit DESC
FETCH FIRST 10 ROWS ONLY;
|
SHは与信限度額のデータを見ることができます。
| Postal Total
Code Credit
---------- ----------
38082 4133000
78558 3904500
55787 3808500
48346 3741000
63736 3713500
45704 3680500
69776 3647500
67843 3606500
72860 3277500
80841 3262500
10 rows selected.
|
SH_READERは与信限度額データを表示しません。
| Postal Total
Code Credit
---------- ----------
38082 0
78558 0
55787 0
48346 0
63736 0
45704 0
69776 0
67843 0
72860 0
80841 0
10 rows selected.
|
SHスキーマを元の構成にリストアします。
ビュー、データ・リダクション・ポリシーおよび仮想列の削除をクリーンアップするには、次のSQLを実行します:
| DROP VIEW sh.customer_view;
BEGIN
DBMS_REDACT.DROP_POLICY(
object_schema => 'SH',
object_name => 'CUSTOMERS',
policy_name => 'REDACT_SENSITIVE_DATA'
);
END;
/
ALTER TABLE sh.customers DROP COLUMN full_address;
|
Oracle Data Redactionに対するこれらの更新により、データベース管理者および開発者向けの強力な新しいツールが提供されるため、機密データが業界標準に準拠し、安全であることが保証されます。これらの機能をデータ管理戦略に統合することで、Oracleデータベースのセキュリティと機能の両方を強化できます。
Oracle LiveLabsでは、Oracle Autonomous Database 23aiでOracle Data Redactionを無料で試すことができます。
