Amazon Web Services ブログ

Amazon Redshift で列レベルのアクセス制御を行い、よりきめ細かなデータセキュリティを実現する

Amazon Redshift は低コストで迅速な分析情報を提供する、大変人気の高いクラウドデータウェアハウスです。Amazon Redshift にはすぐに利用できるセキュリティとコンプライアンスが付属しており、規制の厳しい業界のお客様でもミッションクリティカルなワークロードを自信を持って実行できます。セキュリティ機能には、データをインプレース形式およびオープン形式で簡単に分析できる機能が組み合わさっているだけでなく、コンピューティングとストレージの回復性や使いやすさも備えているため、数万人ものお客様に Amazon Redshift をご利用いただいています。

個人を特定できる情報 (PII) または機密性の高い個人情報 (SPI) として一般的に分類される機密データを、Amazon Redshift に保存している組織は多くあり、こうしたデータへ組織内の異なる人物からのアクセス制限を行っています。たとえば、人事、財務、販売、データサイエンス、マーケティングのどの部門も、顧客データを表示するために必要なアクセス権をすべて持っている場合もありますが、個人を特定できる情報 (PII) や支払カード業界 (PCI) などの機密データには財務部門しかアクセスできないようになっている必要があります。

以前は、ビューまたは AWS Lake Formation (Amazon Redshift Spectrum にある) を使ってそのようなシナリオを管理していましたが、ビューや Amazon Redshift Spectrum の作成や管理には余分なオーバーヘッドがかかります。ビューベースのアプローチは拡張が難しく、セキュリティ管理が手薄となる可能性があります。Amazon Redshift の列レベルのアクセス制御は、Amazon Redshift のデータを列レベルでアクセス制御する新機能です。列レベルの GRANT ステートメントや REVOKE ステートメントを使用することで、データベースオブジェクトを管理するように、セキュリティとコンプライアンスのニーズを満たすことができます。

この投稿では、テーブル、ビュー、マテリアライズドビューで Amazon Redshift の列レベルのアクセス制御を設定する方法をご紹介します。

ユースケース

こちらに、顧客の人口統計データと口座残高データを格納する 2 つのテーブルがあります。財務部門はすべての顧客データを表示できますが、顧客名、電話番号、国籍などの顧客の人口統計データは PII データと見なされ、アクセスを制限する必要があるため、営業部門は市場セグメントと口座残高データのみを表示および更新できます。このユースケースは、PII データを保護するための列レベルのアクセス制御の良い例です。以下は、2 つのテーブルのシンプルなエンティティ関係図です。

前提条件

このブログにある図を試す前に、次の前提条件をご確認ください。

  1. Amazon Redshift クラスター。
  2. テーブル またはスーパーユーザーを作成するアクセス許可を持つデータベースユーザー。

環境の設定

環境をセットアップしてユースケースを実装するには、次の手順を実行してください。

  1. テーブルまたはスーパーユーザーを作成するアクセス許可を持つユーザーが選択した任意の SQL クライアントを使用して、Amazon Redshift クラスターに接続します。
  2. 次のコードで 2 つのテーブルを作成します。

    CREATE TABLE customer 
(
  customerid       INT8 NOT NULL,
  customername     VARCHAR(25) NOT NULL,
  phone            CHAR(15) NOT NULL,
  nationid        INT4 NOT NULL,
  marketsegment    CHAR(10) NOT NULL,
  accountbalance   NUMERIC(12,2) NOT NULL
);
CREATE TABLE nation 
(
  nationid    INT4 NOT NULL,
  nationname   CHAR(25) NOT NULL
);
    SQL
  3. 次のコードを使用して、サンプルデータをいくつか 2 つのテーブルに入力します。

    INSERT INTO customer VALUES
(1, 'Customer#000000001', '33-687-542-7601', 3, 'HOUSEHOLD', 2788.52),
(2, 'Customer#000000002', '13-806-545-9701', 1, 'MACHINERY', 591.98),
(3, 'Customer#000000003', '13-312-472-8245', 1, 'HOUSEHOLD', 3332.02),
(4, 'Customer#000000004', '23-127-851-8031', 2, 'MACHINERY', 9255.67),
(5, 'Customer#000000005', '13-137-193-2709', 1, 'BUILDING', 5679.84)
;
INSERT INTO nation VALUES
(1, 'UNITED STATES'),
(2, 'AUSTRALIA'),
(3, 'UNITED KINGDOM')
;
    SQL
  4. 次のコードを使用して、ビューとマテリアライズドビューを作成します。

    CREATE OR REPLACE VIEW customer_vw AS SELECT customername, phone, marketsegment, accountbalance, CASE WHEN accountbalance < 1000 THEN 'low' WHEN accountbalance > 1000 AND accountbalance < 5000 THEN 'middle' ELSE 'high' END AS incomegroup FROM customer;

CREATE MATERIALIZED VIEW customernation_mv AS SELECT customername, phone, nationname, marketsegment, sum(accountbalance) AS accountbalance FROM customer c INNER JOIN nation n ON c.nationid = n.nationid GROUP BY customername, phone, nationname, marketsegment;
    SQL
  5. ビュー customer_vw は、customer データセットに基づく顧客の収入グループを分類するビジネスルールを実装するためのものです。
  6. 分析ダッシュボードは、customer と nation のテーブルを結合し集計することで、このデータセットに頻繁にアクセスします。その結果、マテリアライズドビュー customernation_mv が作成され、このようなクエリのパフォーマンスが大幅に高速化します。
  7. 財務部門のユーザーを表すユーザー finance に、テーブルレベルのアクセス許可を作成して付与します。以下のユーザーは、ここでの説明のためだけに作成しています。Amazon Redshift で手動で企業ユーザーを作成するのではなく、企業ユーザーを持ちこんで AWS IAM フェデレーションを使用することをお勧めします。詳細については、以下をご参照ください https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-authentication-access-control.html#authentication。 
    CREATE USER finance WITH password 'Abcd1234!';
CREATE USER sales WITH password 'Abcd1234!'; 
GRANT SELECT, UPDATE ON customer TO finance;
GRANT SELECT ON customer_vw TO finance;
GRANT SELECT ON customernation_mv TO finance;
    SQL
  8. ユーザー finance には、SELECT および UPDATE への customer テーブルのすべての列に対するアクセス許可があることに注意してください。
  9. ユーザー finance が customer テーブル、customer_vw ビュー、customernation_mv マテリアライズドビューのすべてのデータを表示でき、customer テーブルのデータを更新できることをテストおよび検証する必要があります。
  10. 以下のコードを入力します。 
    SET SESSION AUTHORIZATION 'finance';
SELECT CURRENT_USER;
SELECT * FROM customer;
SELECT * FROM customer_vw;
SELECT * FROM customernation_mv;
UPDATE customer SET accountbalance = 1000 WHERE marketsegment = 'BUILDING';
RESET SESSION AUTHORIZATION;
    SQL

    上記のコードでは、SQL ステートメント SET SESSION AUTHORIZATION 'finance' を使用してユーザー finance を偽装しています。

    各 SELECT ステートメントは 5 行を返し、UPDATE ステートメントは更新した 1 行を返します。次のコードをご参照ください。

    dev=# SET SESSION AUTHORIZATION 'finance';
SET
dev=> SELECT CURRENT_USER;
 current_user 
--------------
 finance
(1 row)
 
dev=> SELECT * FROM customer;
 customerid |    customername    |      phone      | nationid | marketsegment | accountbalance 
------------+--------------------+-----------------+----------+---------------+----------------
          1 | Customer#000000001 | 33-687-542-7601 |        3 | HOUSEHOLD     |        2788.52
          2 | Customer#000000002 | 13-806-545-9701 |        1 | MACHINERY     |         591.98
          3 | Customer#000000003 | 13-312-472-8245 |        1 | HOUSEHOLD     |        3332.02
          4 | Customer#000000004 | 23-127-851-8031 |        2 | MACHINERY     |        9255.67
          5 | Customer#000000005 | 13-137-193-2709 |        1 | BUILDING      |        5679.84
(5 rows)
 
dev=> SELECT * FROM customer_vw;
    customername    |      phone      | marketsegment | accountbalance | incomegroup 
--------------------+-----------------+---------------+----------------+-------------
 Customer#000000001 | 33-687-542-7601 | HOUSEHOLD     |        2788.52 | middle
 Customer#000000002 | 13-806-545-9701 | MACHINERY     |         591.98 | low
 Customer#000000003 | 13-312-472-8245 | HOUSEHOLD     |        3332.02 | middle
 Customer#000000004 | 23-127-851-8031 | MACHINERY     |        9255.67 | high
 Customer#000000005 | 13-137-193-2709 | BUILDING      |        5679.84 | high
(5 rows)
 
dev=> SELECT * FROM customernation_mv;
    customername    |      phone      |        nationname         | marketsegment | accountbalance 
--------------------+-----------------+---------------------------+---------------+----------------
 Customer#000000005 | 13-137-193-2709 | UNITED STATES             | BUILDING      |        5679.84
 Customer#000000004 | 23-127-851-8031 | AUSTRALIA                 | MACHINERY     |        9255.67
 Customer#000000003 | 13-312-472-8245 | UNITED STATES             | HOUSEHOLD     |        3332.02
 Customer#000000002 | 13-806-545-9701 | UNITED STATES             | MACHINERY     |         591.98
 Customer#000000001 | 33-687-542-7601 | UNITED KINGDOM            | HOUSEHOLD     |        2788.52
(5 rows)
 
dev=> UPDATE customer SET accountbalance = 1000 WHERE marketsegment = 'BUILDING';
UPDATE 1
dev=>  
dev=> RESET SESSION AUTHORIZATION;
RESET
    SQL

これで、テーブルレベルのアクセス許可が正常に設定され、ユーザー finance がすべての顧客データを表示および更新できるようになりました。

Amazon Redshift の列レベルのアクセス制御のセットアップ

次の構文で GRANT ステートメントおよび REVOKE ステートメントを使用することで、列レベルのアクセス制御を有効または無効にできます。

GRANT { { SELECT | UPDATE } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [,...] ) }
ON { [ TABLE ] table_name [, ...] }
TO { username | GROUP group_name | PUBLIC } [, ...]
 
REVOKE { { SELECT | UPDATE } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [,...] ) }
ON { [ TABLE ] table_name [, ...] }
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
SQL

列レベルの特権をセットアップするには、以下の手順を実行します。

  1. 列レベルのアクセス制御を持っているユーザーを特定するには、 PG_ATTRIBUTE_INFO システムビューのクエリを実行します。以下のコードを入力します。 
    SELECT b.attacl, b.attname, c.relname FROM pg_catalog.pg_attribute_info b JOIN pg_class c ON c.oid=b.attrelid WHERE c.relname in ('customer','customer_vw','customernation_mv') AND b.attacl IS NOT NULL ORDER BY c.relname, b.attname;
    SQL
  2. 列レベルのアクセス制御をまだ実装していない場合、クエリはゼロのレコードを返します。
  3. ユーザー sales SELECT に、列 marketsegment と accountbalance に対するアクセス許可を付与します。この列は、テーブル customer、ビュー customer_vw、マテリアライズドビュー customernation_mv にあります。次のコードを入力することで、UPDATE のアクセス許可も付与します。これは、列 marketsegment と accountbalance (テーブル customer 内) にあります。 
    RESET SESSION AUTHORIZATION;
GRANT SELECT (marketsegment, accountbalance) ON customer TO sales WITH GRANT OPTION;
GRANT SELECT (marketsegment, accountbalance),UPDATE (marketsegment, accountbalance) ON customer TO sales;
GRANT SELECT (marketsegment, accountbalance) ON customer_vw TO sales;
GRANT SELECT (marketsegment, accountbalance) ON customernation_mv TO sales;
    SQL
  4. 最初のステートメントで「Grant options are not supported for column privileges」というエラーメッセージが返されます。これは、テーブルの所有者またはスーパーユーザーのみが列レベルの特権を付与でき、シンプルなセキュリティモデルを維持できるためです。
  5. 上記のアクセス許可が、次のコードで付与されているかどうかを検証します。 
    SELECT b.attacl, b.attname, c.relname FROM pg_catalog.pg_attribute_info b  JOIN pg_class c ON c.oid=b.attrelid WHERE c.relname in ('customer','customer_vw','customernation_mv') AND b.attacl IS NOT NULL ORDER BY c.relname, b.attname;
    SQL
  6. クエリは 6 行を返します。次のコードをご参照ください。 
    dev=# SELECT b.attacl, b.attname, c.relname FROM pg_catalog.pg_attribute_info b  JOIN pg_class c ON c.oid=b.attrelid WHERE c.relname in ('customer','customer_vw','customernation_mv') AND b.attacl IS NOT NULL ORDER BY c.relname, b.attname;
      attacl       |    attname     |      relname      
-------------------+----------------+-------------------
 {sales=rw/fqdemo} | accountbalance | customer
 {sales=rw/fqdemo} | marketsegment  | customer
 {sales=r/fqdemo}  | accountbalance | customer_vw
 {sales=r/fqdemo}  | marketsegment  | customer_vw
 {sales=r/fqdemo}  | accountbalance | customernation_mv
 {sales=r/fqdemo}  | marketsegment  | customernation_mv
(6 rows)
    SQL

    上記の出力は、次のとおりです。
    ユーザー: sales (attacl 列)
    アクセス許可: read/write (attacl 列値 “rw”)
    列: accountbalance、marketsegment (attname 列)
    テーブル: customer (relname 列)
    付与者: fqdemo (attacl 列)

    ユーザー: sales (attacl 列)
    アクセス許可: read (attacl 列値 “r”)
    列: accountbalance、marketsegment (attname 列)
    テーブル: customer_vw、customernation_mv (relname 列)
    付与者: fqdemo (attacl 列)

  7. 列レベルのアクセス制御が正しいことを確認したら、ユーザー sales として実行し、次のコードを使用して、テーブル customer、ビュー customer_vw、マテリアライズドビュー customernation_mv のクエリを実行します。 
    SET SESSION AUTHORIZATION 'sales';
SELECT CURRENT_USER;
SELECT * FROM customer;
SELECT * FROM customer_vw;
SELECT * FROM customernation_mv;
    SQL
  8. クエリされているオブジェクトのすべての列に対するアクセス許可をユーザーが持っていない場合、各 SELECT ステートメントはアクセス許可拒否エラーを返します。次のコードをご参照ください。 
    dev=# SET SESSION AUTHORIZATION 'sales';
SET
dev=> SELECT CURRENT_USER;
 current_user 
--------------
 sales
(1 row)
 
dev=> SELECT * FROM customer;
ERROR:  permission denied for relation customer
dev=> SELECT * FROM customer_vw;
ERROR:  permission denied for relation customer_vw
dev=> SELECT * FROM customernation_mv;
ERROR:  permission denied for relation customernation_mv
    SQL
  9. 次のコードを使って、列 marketsegment と accountbalance のみを、テーブル customer、ビュー customer_vw、マテリアライズドビュー customernation_mv からクエリします。 
    SELECT marketsegment, accountbalance FROM customer;
SELECT marketsegment, accountbalance FROM customer_vw;
SELECT marketsegment, accountbalance FROM customernation_mv;
    SQL
  10. ユーザー sales には列 marketsegment と accountbalance のクエリを実行するアクセス許可があるため、各 SELECT ステートメントは 5 行を返します。次のコードをご参照ください。 
    dev=> SELECT marketsegment, accountbalance FROM customer;
 marketsegment | accountbalance 
---------------+----------------
 HOUSEHOLD     |        2788.52
 MACHINERY     |         591.98
 HOUSEHOLD     |        3332.02
 MACHINERY     |        9255.67
 BUILDING      |        1000.00
(5 rows)
 
dev=> SELECT marketsegment, accountbalance FROM customer_vw;
 marketsegment | accountbalance 
---------------+----------------
 HOUSEHOLD     |        2788.52
 MACHINERY     |         591.98
 HOUSEHOLD     |        3332.02
 MACHINERY     |        9255.67
 BUILDING      |        1000.00
(5 rows)
 
dev=> SELECT marketsegment, accountbalance FROM customernation_mv;
 marketsegment | accountbalance 
---------------+----------------
 MACHINERY     |        9255.67
 BUILDING      |        5679.84
 MACHINERY     |         591.98
 HOUSEHOLD     |        3332.02
 HOUSEHOLD     |        2788.52
(5 rows)
    SQL
  11. 次のコードで、accountbalance 列を更新します。 
    UPDATE customer SET accountbalance = 2000 WHERE marketsegment = 'BUILDING';
SELECT accountbalance FROM customer WHERE marketsegment = 'BUILDING';
    SQL
  12. SELECT ステートメントで 2000 の値が含まれている 1 行を返します。次のコードをご参照ください。 
    dev=> UPDATE customer SET accountbalance = 2000 WHERE marketsegment = 'BUILDING';
UPDATE 1
dev=> SELECT accountbalance FROM customer WHERE marketsegment = 'BUILDING';
 accountbalance 
----------------
        2000.00
(1 row)
    SQL
  13. 次のコードを使用して、accountbalance 列を条件 nationid=1 で更新します。 
    UPDATE customer SET accountbalance = 3000 WHERE nationid = 1;
    SQL
  14. ユーザー sales は where 句の列 nationid に対する列レベルの特権を持たないため、UPDATE ステートメントはアクセス許可拒否エラーを返します。
  15. 次のコードを使用して、nationid でレコードグループの数のクエリを実行します。 
    SELECT COUNT(*) FROM customer GROUP BY nationid;
    SQL
  16. ユーザー sales は group by 句の列 nationid に対する列レベルの特権を持たないため、SELECT ステートメントはアクセス許可拒否エラーを返します。
  17. 列レベルの特権は、SELECT リスト内の列だけでなく、where 句、order by 句、group by 句、having 句、列のSELECT/UPDATE 特権を必要とするクエリのその他の句についても確認します。
  18. ユーザー sales の列レベルのアクセス制御から列 marketsegment を削除し (REVOKE コマンドを使用)、何が起こるかを確認します。以下のコードを入力します。 
    RESET SESSION AUTHORIZATION;
REVOKE SELECT (marketsegment) ON customer FROM sales;
SET SESSION AUTHORIZATION 'sales';
SELECT CURRENT_USER;
SELECT marketsegment, accountbalance FROM customer;
SELECT accountbalance FROM customer;
    SQL
  19. ご覧のように、ユーザー sales はテーブル customer から marketsegment を表示できなくなりました。 
    dev=> SELECT marketsegment, accountbalance FROM customer;
ERROR:  permission denied for relation customer
dev=> SELECT accountbalance FROM customer;
 accountbalance 
----------------
        2788.52
         591.98
        3332.02
        9255.67
        2000.00
(5 rows)
    SQL
  20. 次のコードを入力して、列 marketsegment をビュー customer_vw からクエリします。 
    SELECT marketsegment FROM customer_vw;
    SQL
  21. ユーザー sales はビューの列 marketsegment にアクセスできるため (テーブル customer から列レベルの特権が取り消されても)、ステートメントは 5 行を返します。ビューの所有者のアクセス許可でビューを実行するため、ビューが使用するベーステーブルに対する列またはテーブルレベルの特権をビューの所有者が持っている限り、引き続き機能し続けます。機密データへの不正アクセスを防ぐために、ユーザー sales に対する列レベルの特権もビューから取り消す必要があります。
  22. 次のコードを使用して、ユーザー sales のすべてのアクセス許可を取り消します。 
    RESET SESSION AUTHORIZATION;
SELECT CURRENT_USER;
REVOKE SELECT ON customernation_mv FROM sales;
REVOKE SELECT ON customer_vw FROM sales;
REVOKE SELECT ON customer FROM sales;
REVOKE UPDATE ON customer FROM sales;
    SQL
  23. 次のコードを使用して、ユーザー sales でテーブル、ビュー、マテリアライズドビューを再度クエリします。 
    SET SESSION AUTHORIZATION 'sales';
SELECT CURRENT_USER;
SELECT marketsegment, accountbalance FROM customer;
SELECT marketsegment, accountbalance FROM customer_vw;
SELECT marketsegment, accountbalance FROM customernation_mv;
    SQL
  24. アクセス許可拒否エラーが返されます。これは、REVOKE がすべてのアクセス許可を削除できることを示しています。

つまり、シンプルな GRANT ステートメントで、Amazon Redshift のテーブル、ビュー、マテリアライズドビューで列レベルのアクセス制御を有効にできるということです。REVOKE ステートメントは、アクセス許可を削除するために必要です。これで、レガシービューベースのアクセス制御の複雑さが解消されるため、読み込みと書き込みのきめ細かなアクセス制御が実現します。

クリーンアップ

上記のテストが完了したら、次のコードでオブジェクトとユーザーを削除します。

RESET SESSION AUTHORIZATION;
REVOKE SELECT ON customernation_mv FROM finance;
REVOKE SELECT ON customer_vw FROM finance;
REVOKE SELECT ON customer FROM finance;
REVOKE UPDATE ON customer FROM finance;
DROP VIEW customer_vw;
DROP MATERIALIZED VIEW customernation_mv;
DROP TABLE nation;
DROP TABLE customer;
DROP USER IF EXISTS sales;
DROP USER IF EXISTS finance;
SQL

まとめ

Amazon Redshift はデフォルトでもセキュアなため、セキュリティに追加費用がかかりません。認証 (Active Directory、Okta、Ping Federate、Azure AD) とフェデレーションを提供しており、また、AWS IAM および KMS と統合済みです。さらに、2019 年 9 月以降、Amazon Redshift のデータに対するテーブルベースでのアクセス制御と、Amazon Redshift Spectrum を介した Amazon S3 のデータに対する列レベルのアクセス制御もサポートされるようになりました。Amazon Redshift ではローカルテーブルの列レベルのアクセス制御をサポートするようになったため、ビューベースのアクセス制御を実装したり、別のシステムを使用したりする必要がなくなりました。

この投稿では、Amazon Redshift の列レベルのアクセス制御を簡単に設定する方法をご紹介しました。この投稿のユースケースでは、テーブル、ビュー、マテリアライズドビューに対するきめ細かなアクセスを確認する方法を解説しました。こうした機能を活用することで、ビジネスニーズをサポートできます。

ご不明な点がございましたら、下記へコメントをお寄せください。

 

著者について

BP Yau は AWS のデータウェアハウススペシャリストソリューションアーキテクトです。 彼の役割は、お客様がビッグデータソリューションを構築して大規模にデータを処理するのをサポートすることです。AWS に加わる前は、Amazon.com の Supply Chain Optimization Technologies が Oracle Data Warehouse を Amazon Redshift に移行するのを支援し、AWS テクノロジーを使用して次世代のビッグデータ分析プラットフォームを構築しました。

 

 

 

Srikanth Sopirala はシニアスペシャリストソリューションアーキテクトで、AWS のアナリティクスに力を入れています。 彼は、お客様がクラウドでスケーラブルなデータと分析ソリューションを構築するのをサポートすることに情熱を傾けています。