行アクセスポリシー

このトピックでは、行アクセスポリシーとは何か、行アクセスポリシーの作成と適用方法、典型的なシナリオでの2つのユースケース、行アクセスポリシーの管理方法、および行アクセスポリシーを使用する際の制限について説明します。

カラムおよび行レベルのセキュリティの概要については、 カラムおよび行レベルのセキュリティを理解する を参照してください。

各 SQL 操作に必要な権限については、 ポリシーの権限を管理する を参照してください。

定義

行レベルのセキュリティを使用すると、テーブルまたはビューに行アクセスポリシーを適用して、クエリ結果で表示される行を決定できます。

行アクセスポリシーのポリシー式には条件や関数を含めることができ、条件が満たされた場合にクエリ実行時にデータを変換します。

行アクセスポリシーは、テーブルの作成時または作成後にテーブルまたはビューに追加できます。

行アクセスポリシーの作成

ポリシーは、カラム名、カラムタイプ、フィルター条件、および関数で構成されます。

構文:

CREATE ROW ACCESS POLICY [ IF NOT EXISTS ] <name> 
AS ( <arg_name> <arg_type> [ , ... ] ) 
RETURNS boolean ->
<expression_on_arg_name>
[ COMMENT = '<string_literal>' ]

パラメータ	必須	説明
name	はい	ポリシーの名前で、データベース内で一意である必要があります。ポリシーは catalog.db.policy の形式でデータベースやカタログをまたいで参照できます。カタログが指定されていない場合、現在のカタログが使用されます。
arg_name	はい	マスクするカラムの名前。
arg_type	はい	マスクするカラムのデータタイプ。
RETURNS	はい	戻り値のデータタイプは BOOLEAN でなければなりません。
expression_on_arg_name	はい	フィルター条件として使用される式で、if()、case when()、ifnull() などの任意の条件関数を使用できます。
COMMENT	いいえ	ポリシーの説明。

例:

例 1: 行アクセスポリシーを作成し、sales_asia が asia 地域のデータのみを、sales_uk が uk 地域のデータのみを、ACCOUNTADMIN がすべてのデータを参照できるようにします。

CREATE ROW ACCESS POLICY region_data AS
(region varchar) RETURNS boolean
->
   CASE WHEN current_role()='sales_asia' and region='asia' THEN true
   WHEN current_role()='sales_uk' and region='uk' THEN true
   WHEN current_role()='ACCOUNTADMIN' THEN true
   ELSE false
   END
 COMMENT "for test";

例 2: サブクエリを行アクセスポリシーにネストし、現在のロールが自分の地域のデータのみを参照できるようにします。

CREATE ROW ACCESS POLICY rap_sales_manager_regions_2 AS
(sales_region varchar) RETURNS boolean
 ->
 CASE WHEN EXISTS (
            select * from map
              where 'role' = current_role()
                and 'sales_region' = region
          ) THEN true 
  ELSE false
  END;

行アクセスポリシーの適用

ポリシーが作成された後、既存のテーブルに適用できます。

構文:

ALTER TABLE <tbl_name> ADD ROW ACCESS POLICY <name> ON (<cond_col1>[, <cond_col2>, ...])

例:

ALTER TABLE `sales_info` ADD ROW ACCESS POLICY region_data ON (region);

テーブルを作成する際に WITH 句を使用して、既存の行アクセスポリシーをテーブルに適用することもできます。

構文:

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...]
[, index_definition1[, index_definition2, ...]])
[ENGINE = [olap|mysql|elasticsearch|hive|iceberg|hudi|jdbc]]
[key_desc]
[COMMENT "table comment"]
[partition_desc]
distribution_desc
WITH ROW ACCESS POLICY <name> ON (<cond_col1 [, <cond_col2> , ...])  
[WITH ROW ACCESS POLICY <name> ON (<cond_col12> [, <cond_col1> , ...]) ...] 
[rollup_index]
[PROPERTIES ("key"="value", ...)]
[BROKER PROPERTIES ("key"="value", ...)]

例:

CREATE TABLE `sales_info` (
    name varchar(50),
    phone string,
    region varchar(50),
    sales INT)
 WITH ROW ACCESS POLICY region_data ON (region);

ユースケース - 地域別にデータをフィルタリング

ユーザーは3つの地域での販売データを持っており、販売スタッフが自分の地域のデータのみを閲覧できるようにしたいと考えています。

admin ユーザーとしてクラスターに接続した後、デフォルトのロール user_admin と db_admin に関連付けられた権限を持っています。データセキュリティの観点から、別のユーザーを作成し、このユーザーに必要な権限のみを割り当てて行アクセスポリシーをテストすることができます。このユースケースでは、次の項目を作成します:

• データベース db_test
• テーブル sales_info
• 異なる地域のデータにアクセスできる2つのロール
• 行アクセスポリシー関連の権限を持つユーザー row_admin とロール row_admin_role
• テーブル用の行アクセスポリシー region_data

1. データベース db_test を作成し、このデータベースに切り替えます。

   CREATE DATABASE db_test;
   USE db_test;

2. ユーザー情報テーブル sales_info を作成し、このテーブルにデータを挿入します。

   CREATE TABLE `sales_info` (
       name varchar(50),
       phone string,
       region varchar(50),
       sales INT);
   
   INSERT INTO `sales_info` VALUES
   ('lily','886410','asia',11),
   ('richard','654321','uk',16),
   ('amber','789165','africa',17);

3. 2つの異なるロールを作成し、テーブルからデータをクエリする権限をロールに付与します。

   CREATE ROLE `sales_asia`,`sales_uk`;
   
   GRANT SELECT ON TABLE `sales_info` TO ROLE `sales_asia`;
   GRANT SELECT ON TABLE `sales_info` TO ROLE `sales_uk`;

4. ユーザー row_admin を作成し、ロール row_admin_role を作成し、このロールに必要な権限を付与し、row_admin にロールを割り当てます。

   CREATE USER `row_admin`;
   CREATE ROLE `row_admin_role`; 
   
   -- データベースで行アクセスポリシーを作成する権限を付与します。
   GRANT CREATE ROW ACCESS POLICY ON DATABASE db_test TO ROLE row_admin_role;
   
   -- データベース内のすべての行アクセスポリシーを適用する権限を付与します。
   GRANT ALTER ON TABLE sales_info TO ROLE row_admin_role;
   GRANT APPLY ON ALL ROW ACCESS POLICIES to ROLE row_admin_role;
   
   -- 前のロールをユーザーに割り当てます。
   GRANT `row_admin_role`,`sales_asia`,`sales_uk` TO USER `row_admin`;
   
   -- ユーザー row_admin に切り替えます。
   EXECUTE AS `row_admin` WITH NO REVERT;
   
   -- マスキングポリシー関連の操作を行うためにロール masking_admin_role をアクティブにします。
   SET ROLE `row_admin_role`;

5. フィルター条件として CASE WHEN を使用する行アクセスポリシーを作成します。このポリシーは、異なるロールが自分の地域のデータのみを閲覧できるようにします。

   CREATE ROW ACCESS POLICY region_data AS
   (region varchar(50)) RETURNS boolean ->
   CASE WHEN current_role() ='sales_asia' and region = 'asia' THEN true
        WHEN current_role() ='sales_uk' and region = 'uk' THEN true
    ELSE false
    END;

6. ポリシーをテーブルに適用します。

   ALTER TABLE `sales_info` ADD ROW ACCESS POLICY region_data ON (region);

7. ロール sales_asia と sales_uk を使用してデータをクエリします。結果は、sales_asia ロールが asia 地域のデータのみを、sales_uk ロールが uk 地域のデータのみを参照できることを示しています。

   SET ROLE `sales_asia`;
   SELECT * FROM `sales_info`;
   +------+--------+--------+-------+
   | name | phone  | region | sales |
   +------+--------+--------+-------+
   | lily | 886410 | asia   |    11 |
   +------+--------+--------+-------+
   
   SET ROLE `sales_uk`;
   SELECT * FROM `sales_info`;
   +---------+--------+--------+-------+
   | name    | phone  | region | sales |
   +---------+--------+--------+-------+
   | richard | 654321 | uk     |    16 |
   +---------+--------+--------+-------+

ユースケース - マッピングテーブルを使用したデータルックアップ

ビジネステーブルに基づいてマッピングテーブルをカスタマイズし、ビジネステーブルから次元をマッピングすることができます。マッピングテーブルは特別な概念ではなく、CelerData の一般的なテーブルです。マッピングテーブルに基づいて行アクセスポリシーを作成し、ビジネステーブルからデータをフィルタリングするためのフィルター条件を指定できます。マッピングテーブルのデータが変更されると、ポリシーは自動的に更新され、ポリシーを変更する必要はありません。

データセキュリティの観点から、別のユーザー mapping_admin を作成し、このユーザーに必要な権限のみを割り当ててマッピングテーブルルックアップをテストすることができます。このユースケースでは、次の項目を作成します:

• データベース db_test
• テーブル revenue とそのマッピングテーブル sales_manager_region
• 異なる地域のデータにアクセスできる2つのロール
• 行アクセスポリシー関連の権限を持つユーザー mapping_admin とロール mapping_admin_role
• テーブルの phone カラム用の行アクセスポリシー phone_mask

1. データベース db_test を作成し、このデータベースに切り替えます。

   CREATE DATABASE db_test;
   USE db_test;

2. ビジネステーブル revenue を作成し、データを挿入します。

   CREATE TABLE `revenue` (
       customer_id varchar(50),
       region varchar(50),
       discount float,
       revenue INT);
   
   INSERT INTO `revenue` VALUES
   ('supermarket1','LA', 0.9,100),
   ('grocery_store3','NYC',0.8,150),
   ('whole_food2','NYC',0.9,120);

3. 各地域の所有者を格納するマッピングテーブル sales_manager_region を作成します。以下の手順では、ユーザー 'Chelsea'@'%' に割り当てられたロールは LA 地域のデータのみを参照できます。

   CREATE TABLE sales_manager_region (
       name varchar(50),
       region varchar(80)
    );
   
   INSERT INTO sales_manager_region VALUES
   ("'Chelsea'@'%'",'LA'),
   ("'Amber'@'%'",'NYC');

4. ロール sales_manager と sales を作成します。テーブル revenue と sales_manager_region に対する SELECT 権限を2つのロールに付与します。

   CREATE ROLE `sales_manager`,`sales`;
   
   GRANT SELECT ON TABLE `revenue`,`sales_manager_region` TO ROLE `sales_manager`;
   GRANT SELECT ON TABLE `revenue`,`sales_manager_region` TO ROLE `sales`;

5. ユーザー mapping_admin を作成し、ロール mapping_admin_role を作成し、このロールに必要な権限を付与し、mapping_admin にロールを割り当てます。

   CREATE USER `mapping_admin`;
   CREATE ROLE `mapping_admin_role`; 
   
   -- データベースで行アクセスポリシーを作成する権限を付与します。
   GRANT CREATE ROW ACCESS POLICY ON DATABASE db_test TO ROLE mapping_admin_role;
   
   -- データベース内のすべての行アクセスポリシーを適用する権限を付与します。
   GRANT ALTER ON TABLE revenue TO ROLE mapping_admin_role;
   GRANT APPLY ON ALL ROW ACCESS POLICIES to ROLE mapping_admin_role;
   
   -- ロールをユーザーに割り当てます。
   GRANT `mapping_admin_role` TO USER `mapping_admin`;

6. sales_manager をユーザー mapping_admin に割り当て、sales をユーザー Chelsea に割り当てます。

   GRANT `sales_manager` TO USER `mapping_admin`;
   
   CREATE USER `Chelsea`;
   GRANT `sales` TO USER `Chelsea`;
   
   -- mapping_admin が Chelsea として操作を行えるようにします。
   GRANT IMPERSONATE ON USER `Chelsea` TO USER `mapping_admin`;
   
   -- ユーザー mapping_admin に切り替えます。
   EXECUTE AS `mapping_admin` WITH NO REVERT;
   
   -- 行アクセスポリシー関連の操作を行うためにロール mapping_admin_role をアクティブにします。
   SET ROLE `mapping_admin_role`;

   必要なフィルタリング効果

   • sales_manager は revenue テーブルのすべてのデータを閲覧できます。
   • 他のロールは自分の地域のデータのみを閲覧できます。
   • 地域の所有者が変更された場合、ポリシーの更新は不要です。

7. マッピングテーブル sales_manager_region に対するサブクエリを含むポリシーを作成します。このポリシーは、sales_manager ロールがすべてのデータを閲覧できるようにし、sales が自分の地域のデータのみを閲覧できるようにし、地域の所有者が変更された場合でも、ポリシーを変更する必要なく権限を更新できます。

   CREATE ROW ACCESS POLICY sales_policy AS (region_data varchar)
   RETURNS boolean ->
        current_role() = 'sales_manager'
        OR current_role() = 'sales' and EXISTS (SELECT 1 FROM sales_manager_region WHERE 
                name = current_user() and region = region_data);

8. ポリシーをテーブル revenue に適用します。

   ALTER TABLE `revenue` ADD ROW ACCESS POLICY sales_policy ON (region);

9. ロール sales_manager に切り替えて revenue からデータをクエリします。sales_manager はテーブル内のすべてのデータを閲覧できます。

   SET ROLE sales_manager;
   SELECT * FROM `revenue`;
   +----------------+--------+----------+---------+
   | customer       | region | discount | revenue |
   +----------------+--------+----------+---------+
   | supermarket1   | LA     |      0.9 |     100 |
   | grocery_store3 | NYC    |      0.8 |     150 |
   | whole_food2    | NYC    |      0.9 |     120 |
   +----------------+--------+----------+---------+

10. ユーザー Chelsea として操作を行い、sales ロールに切り替えます。このロールは region が LA のデータ行のみアクセスできます。

    EXECUTE AS `Chelsea` WITH NO REVERT;
    SET ROLE sales;
    
    SELECT * FROM `revenue`;
    +----------------+--------+----------+---------+
    | customer       | region | discount | revenue |
    +----------------+--------+----------+---------+
    | supermarket1   | LA     |      0.9 |     100 |
    +----------------+--------+----------+---------+

行アクセスポリシーの管理

行アクセスポリシーの解除

テーブルから1つまたはすべての行アクセスポリシーを解除します。

構文:

ALTER TABLE <tbl_name> DROP ROW ACCESS POLICY <name>
ALTER TABLE <tbl_name> DROP ALL ROW ACCESS POLICIES

例:

ALTER TABLE sales_info DROP ROW ACCESS POLICY region_data;
ALTER TABLE sales_info DROP ALL ROW ACCESS POLICIES;

行アクセスポリシーの変更

ポリシー本体の変更、ポリシーの名前変更、またはポリシーのコメントの更新のみが可能です。新しいポリシーは作成後すぐに有効になり、各テーブルに再適用する必要はありません。

構文:

ALTER ROW ACCESS POLICY [ IF EXISTS ] <name> SET BODY -> <expression_on_arg_name>
ALTER ROW ACCESS POLICY [ IF EXISTS ] <name> RENAME TO <new_name>
ALTER ROW ACCESS POLICY [ IF EXISTS ] <name> SET COMMENT = '<string_literal>'

例:

ALTER ROW ACCESS POLICY region_data RENAME TO data_region;

ALTER ROW ACCESS POLICY region_data SET COMMENT = 'test';

すべての行アクセスポリシーのクエリ

データベース内のすべての行アクセスポリシーをクエリします。

SHOW ROW ACCESS POLICIES;
+-----------------------------+------------+-----------------+----------+
| Name                        | Type       | Catalog         | Database |
+-----------------------------+------------+-----------------+----------+
| region_data                 | ROW ACCESS | default_catalog | zj_test  |
| rap_sales_manager_regions_2 | ROW ACCESS | default_catalog | zj_test  |

行アクセスポリシーの CREATE ステートメントのクエリ

構文:

SHOW CREATE ROW ACCESS POLICY <name>

例:

+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Policy      | Create Policy                                                                                                                                                                                                                                                                                                 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| region_data | CREATE ROW ACCESS POLICY region_data AS (region varchar) RETURNS boolean -> CASE WHEN (((CURRENT_ROLE()) = 'ROLE1') AND (`region` = 'uk')) THEN TRUE WHEN (((CURRENT_ROLE()) = 'ROLE2') AND (`region` = 'us')) THEN TRUE WHEN ((CURRENT_ROLE()) = 'ACCOUNTADMIN') THEN TRUE ELSE FALSE END COMMENT "for test" |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

行アクセスポリシーの削除

テーブルに適用されているポリシーを削除することはできません。そのようなポリシーを削除するには、このポリシーが適用されているすべてのテーブルからポリシーを取り消してから削除してください。

DROP ROW ACCESS POLICY <name>

制限

• 行アクセスポリシーを作成する際、ポリシーの戻り値の型は BOOLEAN でなければなりません。
• 行アクセスポリシーがベーステーブルに適用されている場合、そのテーブルに基づいてマテリアライズドビューを作成することはできません。
• 同様に、テーブルがマテリアライズドビューのベーステーブルとして使用されている場合、そのテーブルに行アクセスポリシーを適用することはできません。
• 行アクセスポリシーが適用されたカラムは、他のマスキングポリシーの条件カラムとして使用したり、他のポリシーのサブクエリで参照したりすることができます。

参照

ポリシーの作成と適用は、CREATE、APPLY、ALTER、DROP などの権限によって制御されます。これらの権限の付与方法、各コマンドに必要な権限、および権限管理モードの詳細については、 ポリシーの権限を管理する を参照してください。