アクセス履歴

このトピックでは、Snowflakeのユーザーアクセス履歴に関する概念を説明します。

このトピックの内容:

概要

Snowflakeのアクセス履歴とは、ユーザークエリがデータを読み取り、 SQL ステートメントが INSERT、 UPDATE、 DELETE などのデータ書き込み操作を、ソースデータオブジェクトからターゲットデータオブジェクトに COPY コマンドのバリエーションとともに実行するときを指します。ユーザーアクセス履歴は、Account Usage ACCESS_HISTORY ビューをクエリすることで見つけられます。このビューにある記録は、法令順守の監査を容易にし、ユーザー(つまり、クエリ演算子)、クエリ、テーブルまたはビュー、列、およびデータの間に直接リンクがあるため、一般的で頻繁にアクセスされるテーブルと列に関する洞察を提供します。

ACCESS_HISTORY ビューの各行には、 SQL ステートメントごとに単一の記録が含まれます。記録には次の種類の情報が含まれます。

  • クエリのデータの基になるテーブルなど、クエリが直接および間接的にアクセスした ソース列

  • SELECT ステートメントで指定された列など、クエリ結果でユーザーに表示される 投影列

  • クエリ結果を決定するために使用されるけれども、投影されない列。例えば、結果をフィルターするための WHERE 句にある列など。

例:

CREATE OR REPLACE VIEW v1 (vc1, vc2) AS
SELECT c1 as vc1,
       c2 as vc2
FROM t
WHERE t.c3 > 0
;
Copy
  • 列C1とC2は、ビューが直接アクセスするソース列で、 ACCESS_HISTORY ビューの base_objects_accessed 列に記録されます。

  • 列C3は、 ACCESS_HISTORY ビューの base_objects_accessed 列に記録されている、ビューに含まれる行のフィルターに使用されます。

  • 列 VC1 と VC2 は、ビューをクエリするとき(SELECT * FROM v1;)にユーザーに表示される投影列で、 ACCESS_HISTORY ビューの direct_objects_accessed 列に記録されます。

同じ動作が WHERE 句のキー列にも当てはまります。例:

CREATE OR REPLACE VIEW join_v (vc1, vc2, c1) AS
  SELECT
      bt.c1 AS vc1,
      bt.c2 AS vc2,
      jt.c1
  FROM bt, jt
  WHERE bt.c3 = jt.c1;
Copy
  • ビューを作成するには、 bt (ベーステーブル)と jt (結合テーブル)の2つの異なるテーブルが必要です。

  • ベーステーブルの列C1、列C2、列C3、結合テーブルの列C1は、 ACCESS_HISTORY ビューの base_objects_accessed 列にすべて記録されます。

  • 列 VC1、 VC2、およびC1は、ビューをクエリするとき(SELECT * FROM join_v;)にユーザーに表示される投影列で、 ACCESS_HISTORY ビューの direct_objects_accessed 列に記録されます。

注釈

Account Usage QUERY_HISTORY ビューの記録が、常に ACCESS_HISTORY ビューに記録されるとは限りません。SQL ステートメントの構造によって、Snowflakeが ACCESS_HISTORY ビューにエントリを記録するかどうかが決まります。

ACCESS_HISTORY ビューでSnowflakeがサポートする読み取りおよび書き込み操作の詳細については、ビュー 使用上の注意 をご参照ください。

読み取りおよび書き込み操作の追跡

ACCESS_HISTORY ビューには、次の列が含まれています。

query_id | query_start_time | user_name | direct_objects_accessed | base_objects_accessed | objects_modified | object_modified_by_ddl | policies_referenced | parent_query_id | root_query_id
Copy

読み取り操作は最初の5列で追跡され、最後の列 objects_modified は、Snowflakeの列、テーブル、およびステージに関連するデータ書き込み情報を指定します。

Snowflakeのクエリとデータベースオブジェクトの作成方法により、Snowflakeが base_objects_accessed 列、 objects_modified 列、 および direct_objects_accessed 列に対して返す情報が決まります。

同様に、クエリが行アクセスポリシーによって保護されたオブジェクトまたはマスキングポリシーによって保護された列を参照する場合、Snowflakeはポリシー情報を policies_referenced 列に記録します。

object_modified_by_ddl 列は、データベース、スキーマ、テーブル、ビュー、および列に対する DDL 操作を記録します。これらの操作には、テーブルやビューに対する行アクセスポリシーを指定するステートメント、列に対するマスキングポリシーを指定するステートメント、オブジェクトや列に対するタグの更新(例: タグの設定、タグ値の変更)も含まれます。

parent_query_id 列と root_query_id 列には、次に対応するクエリ IDs が記録されています。

  • 他のオブジェクトに対して読み取りまたは書き込み操作を行うクエリ。

  • ネストされたストアドプロシージャの呼び出しなど、ストアドプロシージャを呼び出すオブジェクトに対して読み取りまたは書き込み操作を実行するクエリ。詳細については、 祖先クエリ (このトピック内)をご参照くださいい。

列の詳細については、 ACCESS_HISTORY ビューの セクションをご参照ください。

読む

読み取りクエリと、 ACCESS_HISTORY ビューがこの情報を記録する方法を理解するには、次のシナリオを検討してください。

  • 一連のオブジェクト: base_table » view_1 » view_2 » view_3

  • 次のような view_2 の読み取りクエリ:

    select * from view_2;
    
    Copy

この例では、Snowflakeは次を返します。

  • direct_objects_accessed 列に view_2。クエリで view_2 が指定されているためです。

  • base_objects_accessed 列に base_table。これが view_2 にあるデータの元ソースであるためです。

view_1view_3 は、 direct_objects_accessedbase_objects_accessed の列に含まれません。これらのビューはどちらもクエリに含まれておらず、 view_2 のデータのソースとして機能するベースオブジェクトではないためです。

書き込み

書き込み操作と、 ACCESS_HISTORY ビューがこの情報を記録する方法を理解するには、次のシナリオを検討してください。

  • データソース: base_table

  • データソース(つまり、 CTAS)からテーブルを作成します。

    create table table_1 as select * from base_table;
    
    Copy

この例では、Snowflakeは次を返します。

  • テーブルは直接アクセスされ、データのソースであるため、 base_objects_accessed 列と direct_objects_accessed 列内の base_table

  • テーブルの作成時に書き込まれた列を含む objects_modified 列に table_1

列の系列

列系統(つまり、列のアクセス履歴)は、Account Usage ACCESS_HISTORY ビューを拡張して、書き込み操作でソース列からターゲット列にデータがどのように流れるかを指定します。Snowflakeは、ソース列からのデータを参照する後続のすべてのテーブルオブジェクト(例: INSERT、 MERGE、 CTAS)を介して、ソース列からのデータを追跡します。ただし、系統チェーン内のオブジェクトがドロップされている場合を除きます。Snowflakeは、 ACCESS_HISTORY ビューの objects_modified 列を強化することで、列系統にアクセスできるようにします。

詳細については、次をご参照ください。

マスキングと行アクセスポリシーの参照

POLICY_REFERENCED 列は、テーブルに設定された行アクセスポリシーまたは列に設定されたマスキングポリシーを持つオブジェクトを指定します。これには、行アクセスポリシーまたはマスキングポリシーによって保護されている中間オブジェクトが含まれます。Snowflakeは、テーブルまたは列に適用されるポリシーを記録します。

次のオブジェクトを検討してください。

t1 » v1 » v2

条件:

  • t1 はベーステーブル。

  • v1 はベーステーブルから構築されたビュー。

  • v2v1 から作成されたビュー。

ユーザーが v2 をクエリすると、 policies_referenced 列には、 v2 を保護する行アクセスポリシー、 v2 内の列を保護する各マスキングポリシー、または該当する場合は両方の種類のポリシーが記録されます。さらに、この列には、 t1v1 を保護するマスキングポリシーまたは行アクセスポリシーが記録されます。

これらの記録は、ポリシーで保護されたオブジェクトにアクセスされた方法をデータ管理者が理解するのに役立ちます。

policies_referenced 列は、 ACCESS_HISTORY ビューに追加の利点を提供します。

  • 特定のクエリでユーザーがアクセスする、ポリシーで保護されたオブジェクトを識別します。

  • ポリシー監査プロセスを簡素化します。

    ACCESS_HISTORY ビューをクエリすると、ユーザーがアクセスする保護されたオブジェクトと保護された列に関する情報を取得するために、他のAccount Usageビュー(例: POLICY_REFERENCESQUERY_HISTORY)での複雑な結合が不必要になります。

サポートされている操作

ACCESS_HISTORY ビューがサポートする読み取りおよび書き込み操作の詳細については、 ACCESS_HISTORY ビュー の使用上の注意セクションをご参照ください。

利点

Snowflakeのアクセス履歴には、読み取りおよび書き込み操作に関する次の利点があります。

データ検出:

未使用のデータを検出して、データをアーカイブするか削除するかを決定します。

機密データの移動状況を追跡する:

外部クラウドストレージの場所(例: Amazon S3バケット)からターゲットのSnowflakeテーブルへの、またはその逆のデータ移動を追跡します。

あるSnowflakeテーブルから異なるSnowflakeテーブルへの内部データの移動を追跡します。

機密データの移動をトレースした後、ポリシー(マスキング および 行アクセス)を適用してデータを保護し、 アクセス制御設定 を更新して、ステージへのアクセスをさらに規制します。テーブル、および タグ を設定して、機密データを含むステージ、テーブル、および列のコンプライアンス要件について追跡できるようにします。

データ検証:

レポート、ダッシュボード、およびチャートやグラフなどのデータ可視化製品の正確性と整合性は、データを元のソースまで追跡できるため、検証されます。

データスチュワードは、特定のテーブルまたはビューを削除または変更する前にユーザーに通知することもできます。

コンプライアンス監査:

GDPRCCPA などのコンプライアンス規制を満たすために、テーブルまたはステージで書き込み操作を実行したSnowflakeユーザーを特定し、書き込み操作がいつ発生したかを特定します。

全体的なデータガバナンスを強化する:

ACCESS_HISTORY ビューは、アクセスされたデータ、データアクセスがいつ行われたか、およびアクセスされたデータがデータソースオブジェクトからデータターゲットオブジェクトにどのように移動したかを統一的に示します。

列系統には、次の追加の利点があります。

派生オブジェクトの保護:

データスチュワードは、派生オブジェクト(例: CTAS)を作成した後に追加作業なしで、機密性の高いソース列に簡単に タグ付け できます。その後、データスチュワードは、機密性の高い列を含むテーブルを 行アクセスポリシー で保護するか、機密性の高い列自体を マスキングポリシー または タグベースのマスキングポリシー で保護できます。

機密列のコピー頻度:

データプライバシー担当者は、機密データを含む列のオブジェクト数(例: テーブル1つ、ビュー2つ)を迅速に判断できます。機密データを含む列がテーブルオブジェクトに何回出現するかを知ることで、データプライバシー担当者は、規制コンプライアンス基準をどのように満たしているかを証明できます(例: 欧州連合の一般データ保護規則(GDPR)基準に準拠)。

根本原因の分析:

列系統は、データをソースまで追跡するメカニズムを提供します。これは、データ品質の低下に起因する障害点を特定し、トラブルシューティングプロセス中に分析する列の数を減らすのに役立ちます。

ACCESS_HISTORY ビューのクエリ

次のセクションでは、 ACCESS_HISTORY ビューのクエリ例を示します。

一部のサンプルクエリは、クエリのパフォーマンスを向上させるために query_start_time 列でフィルターすることに注意してください。パフォーマンスを向上させるもう1つのオプションは、より狭い時間範囲でクエリを実行することです。

例: クエリの読み取り

以下のサブセクションでは、次のユースケースで読み取り操作のために ACCESS_HISTORY ビューをクエリする方法について詳しく説明します。

  • 特定のユーザーのアクセス履歴を取得します。

  • 次の質問に答えるために、 object_id (例: テーブルID)に基づいて、過去30日間の機密データアクセスのコンプライアンス監査を促進します。

    • 誰がデータにアクセスしましたか?

    • データはいつアクセスされましたか?

    • どの列がアクセスされましたか?

ユーザーのアクセス履歴を返す

最新のアクセスから開始して、ユーザーとクエリの開始時刻の順にユーザーアクセス履歴を返します。

SELECT user_name
       , query_id
       , query_start_time
       , direct_objects_accessed
       , base_objects_accessed
FROM access_history
ORDER BY 1, 3 desc
;
Copy

コンプライアンス監査を促進する

次の例は、コンプライアンス監査を容易にするために役立ちます。

  • object_id 値を追加して、過去30日間に機密テーブルにアクセスしたユーザーを特定します。

    SELECT distinct user_name
    FROM access_history
         , lateral flatten(base_objects_accessed) f1
    WHERE f1.value:"objectId"::int=<fill_in_object_id>
    AND f1.value:"objectDomain"::string='Table'
    AND query_start_time >= dateadd('day', -30, current_timestamp())
    ;
    
    Copy
  • 32998411400350object_id 値を使用して、過去30日間にアクセスがあった時期を特定します。

    SELECT query_id
           , query_start_time
    FROM access_history
         , lateral flatten(base_objects_accessed) f1
    WHERE f1.value:"objectId"::int=32998411400350
    AND f1.value:"objectDomain"::string='Table'
    AND query_start_time >= dateadd('day', -30, current_timestamp())
    ;
    
    Copy
  • 32998411400350object_id 値を使用して、過去30日間にアクセスされた列を特定します。

    SELECT distinct f4.value AS column_name
    FROM access_history
         , lateral flatten(base_objects_accessed) f1
         , lateral flatten(f1.value) f2
         , lateral flatten(f2.value) f3
         , lateral flatten(f3.value) f4
    WHERE f1.value:"objectId"::int=32998411400350
    AND f1.value:"objectDomain"::string='Table'
    AND f4.key='columnName'
    ;
    
    Copy

例: 書き込み操作

以下のサブセクションでは、次のユースケースで書き込み操作のために ACCESS_HISTORY ビューをクエリする方法について詳しく説明します。

  • ステージからテーブルにデータをロードします。

  • テーブルからステージにデータをアンロードします。

  • PUT コマンドを使用して、ローカルファイルをステージにアップロードします。

  • GET コマンドを使用して、ステージからローカルディレクトリにデータファイルを取得します。

  • 機密性の高いステージデータの動きを追跡します。

ステージからテーブルにデータをロードする

外部クラウドストレージのデータファイルからターゲットテーブルの列に値のセットをロードします。

copy into table1(col1, col2)
from (select t.$1, t.$2 from @mystage1/data1.csv.gz);
Copy

direct_objects_accessed 列と base_objects_accessed 列は、外部の名前付きステージにアクセスしたことを指定します。

{
  "objectDomain": STAGE
  "objectName": "mystage1",
  "objectId": 1,
  "stageKind": "External Named"
}
Copy

objects_modified 列は、データがテーブルの2つの列に書き込まれたことを指定します。

{
  "columns": [
     {
       "columnName": "col1",
       "columnId": 1
     },
     {
       "columnName": "col2",
       "columnId": 2
     }
  ],
  "objectId": 1,
  "objectName": "TEST_DB.TEST_SCHEMA.TABLE1",
  "objectDomain": TABLE
}
Copy

テーブルからステージにデータをアンロードする

値のセットをSnowflakeテーブルからクラウドストレージにアンロードします。

copy into @mystage1/data1.csv
from table1;
Copy

direct_objects_accessed 列と base_objects_accessed 列は、アクセスされたテーブル列を指定します。

{
  "objectDomain": TABLE
  "objectName": "TEST_DB.TEST_SCHEMA.TABLE1",
  "objectId": 123,
  "columns": [
     {
       "columnName": "col1",
       "columnId": 1
     },
     {
       "columnName": "col2",
       "columnId": 2
     }
  ]
}
Copy

objects_modified 列は、アクセスされたデータが書き込まれたステージを指定します。

{
  "objectId": 1,
  "objectName": "mystage1",
  "objectDomain": STAGE,
  "stageKind": "External Named"
}
Copy

PUT コマンドを使用して、ローカルファイルをステージにアップロードする

データファイルを内部(つまり、Snowflake)ステージにコピーします。

put file:///tmp/data/mydata.csv @my_int_stage;
Copy

direct_objects_accessed 列と base_objects_accessed 列は、アクセスされたファイルへのローカルパスを指定します。

{
  "location": "file:///tmp/data/mydata.csv"
}
Copy

objects_modified 列は、アクセスされたデータが書き込まれたステージを指定します。

{
  "objectId": 1,
  "objectName": "my_int_stage",
  "objectDomain": STAGE,
  "stageKind": "Internal Named"
}
Copy

GET コマンドを使用して、ステージからローカルディレクトリにデータファイルを取得する

内部ステージからローカルマシン上のディレクトリにデータファイルを取得します。

get @%mytable file:///tmp/data/;
Copy

direct_objects_accessed 列と base_objects_accessed 列は、アクセスされたステージとローカルディレクトリを指定します。

{
  "objectDomain": Stage
  "objectName": "mytable",
  "objectId": 1,
  "stageKind": "Table"
}
Copy

objects_modified 列は、アクセスされたデータが書き込まれたディレクトリを指定します。

{
  "location": "file:///tmp/data/"
}
Copy

機密性の高いステージデータの動きの追跡

時系列で実行される一連のクエリを移動するときに、機密性の高いステージデータを追跡します。

次のコマンドを実行します。5つのステートメントがステージデータにアクセスすることに注意してください。したがって、ステージアクセスの ACCESS_HISTORY ビューをクエリする場合、結果セットには5行が含まれている必要があります。

use test_db.test_schema;
create or replace table T1(content variant);
insert into T1(content) select parse_json('{"name": "A", "id":1}');

-- T1 -> T6
insert into T6 select * from T1;

-- S1 -> T1
copy into T1 from @S1;

-- T1 -> T2
create table T2 as select content:"name" as name, content:"id" as id from T1;

-- T1 -> S2
copy into @S2 from T1;

-- S1 -> T3
create or replace table T3(customer_info variant);
copy into T3 from @S1;

-- T1 -> T4
create or replace table T4(name string, id string, address string);
insert into T4(name, id) select content:"name", content:"id" from T1;

-- T6 -> T7
create table T7 as select * from T6;
Copy

条件:

  • T1T2 ... T7 は、テーブルの名前を指定します。

  • S1 および S2 は、ステージの名前を指定します。

アクセス履歴をクエリし、ステージ S1 へのアクセスを決定します。

次のテーブルに、 direct_objects_accessedbase_objects_accessed、および objects_modified 列のデータを示します。

direct_objects_accessed

base_objects_accessed

objects_modified

[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]
Copy
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
Copy
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68613,
        "columnName": "ID"
      },
      {
        "columnId": 68612,
        "columnName": "NAME"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66568,
    "objectName": "TEST_DB.TEST_SCHEMA.T2"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "objectDomain": "Stage",
    "objectId": 118,
    "objectName": "TEST_DB.TEST_SCHEMA.S2",
    "stageKind": "External Named"
  }
]
Copy
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
Copy
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68614,
        "columnName": "CUSTOMER_INFO"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66570,
    "objectName": "TEST_DB.TEST_SCHEMA.T3"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68615,
        "columnName": "NAME"
      },
      {
        "columnId": 68616,
        "columnName": "ID"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66572,
    "objectName": "TEST_DB.TEST_SCHEMA.T4"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]
Copy
[
  {
    "columns": [
      {
        "columnId": 68618,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66574,
    "objectName": "TEST_DB.TEST_SCHEMA.T7"
  }
]
Copy

クエリの例については、次の点に注意してください。

  • 再帰共通テーブル式 を使用します。

  • USING 句 ではなく JOIN 構造を使用します。

    with access_history_flatten as (
        select
            r.value:"objectId" as source_id,
            r.value:"objectName" as source_name,
            r.value:"objectDomain" as source_domain,
            w.value:"objectId" as target_id,
            w.value:"objectName" as target_name,
            w.value:"objectDomain" as target_domain,
            c.value:"columnName" as target_column,
            t.query_start_time as query_start_time
        from
            (select * from TEST_DB.ACCOUNT_USAGE.ACCESS_HISTORY) t,
            lateral flatten(input => t.BASE_OBJECTS_ACCESSED) r,
            lateral flatten(input => t.OBJECTS_MODIFIED) w,
            lateral flatten(input => w.value:"columns", outer => true) c
            ),
        sensitive_data_movements(path, target_id, target_name, target_domain, target_column, query_start_time)
        as
          -- Common Table Expression
          (
            -- Anchor Clause: Get the objects that access S1 directly
            select
                f.source_name || '-->' || f.target_name as path,
                f.target_id,
                f.target_name,
                f.target_domain,
                f.target_column,
                f.query_start_time
            from
                access_history_flatten f
            where
            f.source_domain = 'Stage'
            and f.source_name = 'TEST_DB.TEST_SCHEMA.S1'
            and f.query_start_time >= dateadd(day, -30, date_trunc(day, current_date))
            union all
            -- Recursive Clause: Recursively get all the objects that access S1 indirectly
            select sensitive_data_movements.path || '-->' || f.target_name as path, f.target_id, f.target_name, f.target_domain, f.target_column, f.query_start_time
              from
                 access_history_flatten f
                join sensitive_data_movements
                on f.source_id = sensitive_data_movements.target_id
                    and f.source_domain = sensitive_data_movements.target_domain
                    and f.query_start_time >= sensitive_data_movements.query_start_time
          )
    select path, target_name, target_id, target_domain, array_agg(distinct target_column) as target_columns
    from sensitive_data_movements
    group by path, target_id, target_name, target_domain;
    
    Copy

クエリは、ステージ S1 のデータ移動に関連する次の結果セットを生成します。

PATH

TARGET_NAME

TARGET_ID

TARGET_DOMAIN

TARGET_COLUMNS

TEST_DB.TEST_SCHEMA.S1-->TEST_DB.TEST_SCHEMA.T1

TEST_DB.TEST_SCHEMA.T1

66564

テーブル

["CONTENT"]

TEST_DB.TEST_SCHEMA.S1-->TEST_DB.TEST_SCHEMA.T1-->TEST_DB.TEST_SCHEMA.S2

TEST_DB.TEST_SCHEMA.S2

118

ステージ

[]

TEST_DB.TEST_SCHEMA.S1-->TEST_DB.TEST_SCHEMA.T1-->TEST_DB.TEST_SCHEMA.T2

TEST_DB.TEST_SCHEMA.T2

66568

テーブル

["NAME","ID"]

TEST_DB.TEST_SCHEMA.S1-->TEST_DB.TEST_SCHEMA.T1-->TEST_DB.TEST_SCHEMA.T4

TEST_DB.TEST_SCHEMA.T4

66572

テーブル

["ID","NAME"]

TEST_DB.TEST_SCHEMA.S1-->TEST_DB.TEST_SCHEMA.T3

TEST_DB.TEST_SCHEMA.T3

66570

テーブル

["CUSTOMER_INFO"]

例: 列の系列

次の例では、 ACCESS_HISTORY ビューをクエリし、 FLATTEN 関数を使用して objects_modified 列をフラット化します。

代表的な例として、Snowflakeアカウントで次の SQL クエリを実行して、以下のテーブルを生成します。ここで、番号付きのコメントは次を示しています。

  • // 1: directSources フィールドとターゲット列の間のマッピングを取得します。

  • // 2: baseSources フィールドとターゲット列の間のマッピングを取得します。

// 1

select
  directSources.value: "objectId" as source_object_id,
  directSources.value: "objectName" as source_object_name,
  directSources.value: "columnName" as source_column_name,
  'DIRECT' as source_column_type,
  om.value: "objectName" as target_object_name,
  columns_modified.value: "columnName" as target_column_name
from
  (
    select
      *
    from
      snowflake.account_usage.access_history
  ) t,
  lateral flatten(input => t.OBJECTS_MODIFIED) om,
  lateral flatten(input => om.value: "columns", outer => true) columns_modified,
  lateral flatten(
    input => columns_modified.value: "directSources",
    outer => true
  ) directSources

union

// 2

select
  baseSources.value: "objectId" as source_object_id,
  baseSources.value: "objectName" as source_object_name,
  baseSources.value: "columnName" as source_column_name,
  'BASE' as source_column_type,
  om.value: "objectName" as target_object_name,
  columns_modified.value: "columnName" as target_column_name
from
  (
    select
      *
    from
      snowflake.account_usage.access_history
  ) t,
  lateral flatten(input => t.OBJECTS_MODIFIED) om,
  lateral flatten(input => om.value: "columns", outer => true) columns_modified,
  lateral flatten(
    input => columns_modified.value: "baseSources",
    outer => true
  ) baseSources
;
Copy

戻り値:

SOURCE_OBJECT_ID

SOURCE_OBJECT_NAME

SOURCE_COLUMN_NAME

SOURCE_COLUMN_TYPE

TARGET_OBJECT_NAME

TARGET_COLUMN_NAME

1

D.S.T0

NAME

BASE

D.S.T1

NAME

2

D.S.V1

NAME

DIRECT

D.S.T1

NAME

例: 行アクセスポリシーの参照を追跡する

テーブル、ビュー、またはマテリアライズドビューに行アクセスポリシーが重複することなく設定されている場合は、各インスタンスに対して行を返します。

use role accountadmin;
select distinct
    obj_policy.value:"policyName"::VARCHAR as policy_name
from snowflake.account_usage.access_history as ah
    , lateral flatten(ah.policies_referenced) as obj
    , lateral flatten(obj.value:"policies") as obj_policy
;
Copy

例: マスキングポリシーの参照を追跡する

マスキングポリシーによって列が重複することなく保護されている場合は、各インスタンスに対して行を返します。 policies_referenced 列は、テーブルの行アクセスポリシーよりも1レベル深い列のマスキングポリシーを指定するため、追加のフラット化が必要であることに注意してください。

use role accountadmin;
select distinct
    policies.value:"policyName"::VARCHAR as policy_name
from snowflake.account_usage.access_history as ah
    , lateral flatten(ah.policies_referenced) as obj
    , lateral flatten(obj.value:"columns") as columns
    , lateral flatten(columns.value:"policies") as policies
;
Copy

例: クエリで強制されたポリシーを追跡する

指定された時間枠内の指定されたクエリの、ポリシーが更新された時間(POLICY_CHANGED_TIME)とポリシー条件(POLICY_BODY)を返します。

このクエリを使用する前に、 WHERE 句の入力値を更新します。

where query_start_time > '2023-07-07' and
   query_start_time < '2023-07-08' and
   query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777')
Copy

条件:

query_start_time > '2023-07-07'

開始タイムスタンプを指定します。

query_start_time < '2023-07-08'

終了タイムスタンプを指定します。

query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777'

Account Usage ACCESS_HISTORY ビューにあるクエリ識別子を指定します。

クエリを実行します。

SELECT *
from(
  select j1.*,j2.QUERY_START_TIME as POLICY_CHANGED_TIME, POLICY_BODY
from
(
  select distinct t1.*,
      t4.value:"policyId"::number as PID
  from (select *
      from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
      where query_start_time > '2023-07-07' and
         query_start_time < '2023-07-08' and
         query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777') as t1, //
  lateral flatten (input => t1.POLICIES_REFERENCED,OUTER => TRUE) t2,
  lateral flatten (input => t2.value:"columns", OUTER => TRUE) t3,
  lateral flatten (input => t3.value:"policies",OUTER => TRUE) t4
) as j1
left join
(
  select OBJECT_MODIFIED_BY_DDL:"objectId"::number as PID,
      QUERY_START_TIME,
      OBJECT_MODIFIED_BY_DDL:"properties"."policyBody"."value" as POLICY_BODY
      from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
      where OBJECT_MODIFIED_BY_DDL is not null and
      (OBJECT_MODIFIED_BY_DDL:"objectDomain" ilike '%masking%' or OBJECT_MODIFIED_BY_DDL:"objectDomain" ilike '%row%')
) as j2
On j1.POLICIES_REFERENCED is not null and j1.pid = j2.pid and j1.QUERY_START_TIME>j2.QUERY_START_TIME) as j3
QUALIFY ROW_NUMBER() OVER (PARTITION BY query_id,pid ORDER BY policy_changed_time DESC) = 1;
Copy

例: UDFs

これらの UDF の例は、Account Usage ACCESS_HISTORY ビューが記録する方法を示しています。

  • get_product という名前の UDF を 呼び出し ています。

  • get_product 関数を呼び出した積を mydb.tables.t1 という名前のテーブルに 挿入 します。

  • 共有 UDFs。

UDF を呼び出す

2つの数値の積を計算する次の SQL UDF を検討し、それが mydb.udfs という名前のスキーマに格納されていると仮定します。

CREATE FUNCTION MYDB.UDFS.GET_PRODUCT(num1 number, num2 number)
RETURNS number
AS
$$
    NUM1 * NUM2
$$
;
Copy

get_product を直接 呼び出す と、 UDF の詳細が direct_objects_accessed 列に記録されます。

[
  {
    "objectDomain": "FUNCTION",
    "objectName": "MYDB.UDFS.GET_PRODUCT",
    "objectId": "2",
    "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
    "dataType": "NUMBER(38,0)"
  }
]
Copy

この例は、 ストアドプロシージャの呼び出し (このトピック内)に類似しています。

INSERT DML を使用した UDF

mydb.tables.t1 という名前のテーブルの1および2という名前の列を更新するために、次の INSERT ステートメントを検討してください。

insert into t1(product)
select get_product(c1, c2) from mydb.tables.t1;
Copy

ACCESS_HISTORY ビューは、 get_product 関数を次の場所に記録します。

  • direct_objects_accessed 列。これは、 SQL ステートメントで明示的に関数の名前が付けられているため。また、

  • directSources 配列にある objects_modified 列。これは、関数が列に挿入される値のソースであるため。

同様に、テーブル t1 は次の同じ列に記録されます。

direct_objects_accessed

objects_modified

[
  {
    "objectDomain": "FUNCTION",
    "objectName": "MYDB.UDFS.GET_PRODUCT",
    "objectId": "2",
    "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
    "dataType": "NUMBER(38,0)"
  },
  {
    "objectDomain": "TABLE",
    "objectName": "MYDB.TABLES.T1",
    "objectId": 1,
    "columns":
    [
      {
        "columnName": "c1",
        "columnId": 1
      },
      {
        "columnName": "c2",
        "columnId": 2
      }
    ]
  }
]
Copy
 [
   {
     "objectDomain": "TABLE",
     "objectName": "MYDB.TABLES.T1",
     "objectId": 2,
     "columns":
     [
       {
         "columnId": "product",
         "columnName": "201",
         "directSourceColumns":
         [
           {
             "objectDomain": "Table",
             "objectName": "MYDB.TABLES.T1",
             "objectId": "1",
             "columnName": "c1"
           },
           {
             "objectDomain": "Table",
             "objectName": "MYDB.TABLES.T1",
             "objectId": "1",
             "columnName": "c2"
           },
           {
             "objectDomain": "FUNCTION",
             "objectName": "MYDB.UDFS.GET_PRODUCT",
             "objectId": "2",
             "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
             "dataType": "NUMBER(38,0)"
           }
         ],
         "baseSourceColumns":[]
       }
     ]
   }
]
Copy

共有 UDFs

共有 UDFs は、直接または間接的に参照できます。

  • 直接参照は、 UDF を明示的に呼び出す (このトピック内)ことと同じですが、 UDF が base_objects_accessed 列と direct_objects_accessed 列の両方に記録されます。

  • 間接参照の例は、 UDF を呼び出してビューを作成することです。

    create view v as
    select get_product(c1, c2) as vc from t;
    
    Copy

    base_objects_accessed 列には、 UDF とテーブルが記録されます。

    direct_objects_accessed 列はビューを記録します。

例: DDL 操作によって変更されたオブジェクトの追跡

ALLOWED_VALUES でタグを作成する

タグを作成します。

create tag governance.tags.pii allowed_values 'sensitive','public';
Copy

列の値:

{
  "objectDomain": "TAG",
  "objectName": "governance.tags.pii",
  "objectId": "1",
  "operationType": "CREATE",
  "properties": {
    "allowedValues": {
      "sensitive": {
        "subOperationType": "ADD"
      },
      "public": {
        "subOperationType": "ADD"
      }
    }
  }
}
Copy

注釈

タグの作成時に許容値を指定しなかった場合、 properties フィールドは空の配列になります(つまり、 {})。

タグおよびマスキングポリシーでテーブルを作成する

列にマスキングポリシー、列にタグ、テーブルにタグを付けてテーブルを作成します。

create or replace table hr.data.user_info(
  email string
    with masking policy governance.policies.email_mask
    with tag (governance.tags.pii = 'sensitive')
  )
with tag (governance.tags.pii = 'sensitive');
Copy

列の値:

{
  "objectDomain": "TABLE",
  "objectName": "hr.data.user_info",
  "objectId": "1",
  "operationType": "CREATE",
  "properties": {
    "tags": {
      "governance.tags.pii": {
        "subOperationType": "ADD",
        "objectId": {
          "value": "1"
        },
        "tagValue": {
          "value": "sensitive"
        }
      }
    },
    "columns": {
      "email": {
        objectId: {
          "value": 1
        },
        "subOperationType": "ADD",
        "tags": {
          "governance.tags.pii": {
            "subOperationType": "ADD",
            "objectId": {
              "value": "1"
            },
            "tagValue": {
              "value": "sensitive"
            }
          }
        },
        "maskingPolicies": {
          "governance.policies.email_mask": {
            "subOperationType": "ADD",
            "objectId": {
              "value": 2
            }
          }
        }
      }
    }
  }
}
Copy

タグにマスキングポリシーを設定する

タグにマスキングポリシーを設定します(例: タグベースのマスキング)。

alter tag governance.tags.pii set masking policy governance.policies.email_mask;
Copy

列の値:

{
  "objectDomain": "TAG",
  "objectName": "governance.tags.pii",
  "objectId": "1",
  "operationType": "ALTER",
  "properties": {
    "maskingPolicies": {
      "governance.policies.email_mask": {
        "subOperationType": "ADD",
        "objectId": {
          "value": 2
        }
      }
    }
  }
}
Copy

テーブルを入れ替える

t2 という名前のテーブルを t3 という名前のテーブルと入れ替えます。

alter table governance.tables.t2 swap with governance.tables.t3;
Copy

ビューの2つの異なる記録に注目してください。

記録1:

{
  "objectDomain": "Table",
  "objectId": 0,
  "objectName": "GOVERNANCE.TABLES.T2",
  "operationType": "ALTER",
  "properties": {
    "swapTargetDomain": {
      "value": "Table"
    },
    "swapTargetId": {
      "value": 0
    },
    "swapTargetName": {
      "value": "GOVERNANCE.TABLES.T3"
    }
  }
}
Copy

記録2:

{
  "objectDomain": "Table",
  "objectId": 0,
  "objectName": "GOVERNANCE.TABLES.T3",
  "operationType": "ALTER",
  "properties": {
    "swapTargetDomain": {
      "value": "Table"
    },
    "swapTargetId": {
      "value": 0
    },
    "swapTargetName": {
      "value": "GOVERNANCE.TABLES.T2"
    }
  }
}
Copy

マスキングポリシーをドロップする

マスキングポリシーをドロップします。

drop masking policy governance.policies.email_mask;
Copy

列の値:

{
  "objectDomain" : "MASKING_POLICY",
  "objectName": "governance.policies.email_mask",
  "objectId" : "1",
  "operationType": "DROP",
  "properties" : {}
}
Copy

注釈

列の値は代表的なもので、タグと行のアクセスポリシーに対する DROP 操作に適用されます。

properties フィールドは空の配列で、 DROP 操作前のポリシーに関する情報は提供されません。

列のタグ参照を追跡する

タグがどのように列に設定されているかをモニターするために、 object_modified_by_ddl 列をクエリします。

テーブル管理者として、列にタグを設定し、タグの設定を解除し、異なる文字列値でタグを更新します。

alter table hr.tables.empl_info
  alter column email set tag governance.tags.test_tag = 'test';

alter table hr.tables.empl_info
  alter column email unset tag governance.tags.test_tag;

alter table hr.tables.empl_info
  alter column email set tag governance.tags.data_category = 'sensitive';
Copy

データエンジニアとして、タグの値を変更します。

alter table hr.tables.empl_info
  alter column email set tag governance.tags.data_category = 'public';
Copy

ACCESS_HISTORY ビューをクエリして変更をモニターします。

select
  query_start_time,
  user_name,
  object_modified_by_ddl:"objectName"::string as table_name,
  'EMAIL' as column_name,
  tag_history.value:"subOperationType"::string as operation,
  tag_history.key as tag_name,
  nvl((tag_history.value:"tagValue"."value")::string, '') as value
from
  TEST_DB.ACCOUNT_USAGE.access_history ah,
  lateral flatten(input => ah.OBJECT_MODIFIED_BY_DDL:"properties"."columns"."EMAIL"."tags") tag_history
where true
  and object_modified_by_ddl:"objectDomain" = 'Table'
  and object_modified_by_ddl:"objectName" = 'TEST_DB.TEST_SH.T'
order by query_start_time asc;
Copy

戻り値:

+-----------------------------------+---------------+---------------------+-------------+-----------+-------------------------------+-----------+
| QUERY_START_TIME                  | USER_NAME     | TABLE_NAME          | COLUMN_NAME | OPERATION | TAG_NAME                      | VALUE     |
+-----------------------------------+---------------+---------------------+-------------+-----------+-------------------------------+-----------+
| Mon, Feb. 14, 2023 12:01:01 -0600 | TABLE_ADMIN   | HR.TABLES.EMPL_INFO | EMAIL       | ADD       | GOVERNANCE.TAGS.TEST_TAG      | test      |
| Mon, Feb. 14, 2023 12:02:01 -0600 | TABLE_ADMIN   | HR.TABLES.EMPL_INFO | EMAIL       | DROP      | GOVERNANCE.TAGS.TEST_TAG      |           |
| Mon, Feb. 14, 2023 12:03:01 -0600 | TABLE_ADMIN   | HR.TABLES.EMPL_INFO | EMAIL       | ADD       | GOVERNANCE.TAGS.DATA_CATEGORY | sensitive |
| Mon, Feb. 14, 2023 12:04:01 -0600 | DATA_ENGINEER | HR.TABLES.EMPL_INFO | EMAIL       | ADD       | GOVERNANCE.TAGS.DATA_CATEGORY | public    |
+-----------------------------------+---------------+---------------------+-------------+-----------+-------------------------------+-----------+

例: ストアドプロシージャを呼び出す

次のストアドプロシージャを検討し、 mydb.procedures という名前のスキーマに格納されていると仮定します。

create or replace procedure get_id_value(name string)
returns string not null
language javascript
as
$$
  var my_sql_command = "select id from A where name = '" + NAME + "'";
  var statement = snowflake.createStatement( {sqlText: my_sql_command} );
  var result = statement.execute();
  result.next();
  return result.getColumnValue(1);
$$
;
Copy

my_procedure を直接 呼び出す と、次のように direct_objects_accessed 列と base_objects_accessed 列の両方にプロシージャの詳細が記録されます。

[
  {
    "objectDomain": "PROCEDURE",
    "objectName": "MYDB.PROCEDURES.GET_ID_VALUE",
    "argumentSignature": "(NAME STRING)",
    "dataType": "STRING"
  }
]
Copy

この例は、 UDF の呼び出し (このトピック内)に類似しています。

例: ストアドプロシージャによる祖先クエリ

parent_query_id 列と root_query_id 列を使用して、ストアドプロシージャの呼び出しが互いにどのように関連しているかを理解することができます。

3つの異なるストアドプロシージャステートメントがあり、それらを次の順序で実行したとします。

CREATE OR REPLACE PROCEDURE myproc_child()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  BEGIN
  SELECT * FROM mydb.mysch.mytable;
  RETURN 1;
  END
$$;

CREATE OR REPLACE PROCEDURE myproc_parent()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  BEGIN
  CALL myproc_child();
  RETURN 1;
  END
$$;

CALL myproc_parent();
Copy

ACCESS_HISTORY ビューのクエリは、以下のように情報を記録します。

SELECT
  query_id,
  parent_query_id,
  root_query_id,
  direct_objects_accessed
FROM
  SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;
Copy
+----------+-----------------+---------------+-----------------------------------+
| QUERY_ID | PARENT_QUERY_ID | ROOT_QUERY_ID | DIRECT_OBJECTS_ACCESSED           |
+----------+-----------------+---------------+-----------------------------------+
|  1       | NULL            | NULL          | [{"objectName": "myproc_parent"}] |
|  2       | 1               | 1             | [{"objectName": "myproc_child"}]  |
|  3       | 2               | 1             | [{"objectName": "mytable"}]       |
+----------+-----------------+---------------+-----------------------------------+
  • 最初の行は、 direct_objects_accessed 列に示されているように、 myproc_parent という2番目のプロシージャを呼び出すことに対応しています。

    このストアドプロシージャを直接呼び出したため、 parent_query_idroot_query_id 列は NULL を返します。

  • 2行目は、 direct_objects_accessed column に示されているように、 myproc_child という最初のプロシージャを呼び出すクエリに対応しています。

    parent_query_id 列と root_query_id 列が同じクエリ ID を返すのは、 myproc_child を呼び出すクエリが、直接呼びだした myproc_parent を呼び出すクエリによって開始されたためです。

  • 3行目は、 direct_objects_accessed 列に示されているように、 myproc_child プロシージャで mytable という名前のテーブルにアクセスしたクエリに対応しています。

    parent_query_id 列は、 mytable にアクセスしたクエリのクエリ ID を返し、これは myproc_child の呼び出しに対応します。このストアドプロシージャは、 myproc_parent を呼び出すクエリによって開始されました。 root_query_id 列に示されています。