パラメータ化されたセキュアなビューを使用して、アプリケーション データへのアクセスの保護と制御を行う。

パラメータ化されたセキュアビューは、アプリケーションにデータ セキュリティと行アクセス制御を提供します。アプリケーション ユーザーの ID に基づいてクエリ結果をフィルタします。

このチュートリアルでは、Cloud SQL でパラメータ化されたセキュアビューを設定し、データベースのロールと権限を構成してベーステーブルへのアクセスを制限し、データ セキュリティを検証する方法について説明します。このドキュメントの例はデモ専用です。

パラメータ化されたセキュアビューとは

一般的なベスト プラクティスとして、アプリケーションは、データベースへのアクセスに必要な最小限の権限を持つサービス アカウントを使用して実行する必要があります。たとえば、アプリケーションがデータベースに書き込む必要がない場合は、読み取り専用アクセス権を持つロールを使用する必要があります。これらのアクセス制御は、データベース レベルで構成する必要があります。

アプリケーションで標準のデータベース レベルのアクセス許可よりもきめ細かいセキュリティが必要な場合は、パラメータ化されたセキュアビューを使用して、ユーザーが承認されたデータのみを表示できるようにします。パラメータ化されたセキュアビューは、マルチテナント SaaS アプリケーションに適した動的なデータ セキュリティと行アクセス制御を提供します。アプリケーションのエンドユーザーごとに個別のデータベース ロールは必要ありません。

パラメータ化されたセキュアビューを使用すると、主に次の 3 つのメリットがあります。

  • 動的なデータ セキュリティと行アクセス制御: エンドユーザー識別子を使用してクエリをフィルタリングし、クエリのフレーズに関係なく、ユーザーが承認されたデータにのみアクセスできるようにします。
  • ロール管理の簡素化: 各個人に個別のデータベース ロールを管理するのではなく、すべてのアプリケーション ユーザーに単一の共有データベース ロールを使用します。

目標

  • 名前付きビュー パラメータを使用して、パラメータ化されたセキュアビューを作成します。
  • アプリケーションがデータベースに接続し、パラメータ化されたセキュアビューにアクセスするために使用するデータベースロールを作成します。
  • 新しいロールにパラメータ化されたセキュアビューに対する権限を付与し、ベーステーブルへのアクセス権を取り消します。
  • 新しいロールを使用して接続し、制限付きテーブルにアクセスできないことを確認します。
  • mysql.execute_parameterized_query ストアド プロシージャまたは QueryData API を使用して、パラメータ化されたビューに対してクエリを実行します。

費用

このドキュメントでは、課金対象である次の Google Cloudコンポーネントを使用します。

料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを算出できます。

新規の Google Cloud ユーザーは無料トライアルをご利用いただける場合があります。

このドキュメントのタスクを完了したら、作成したリソースを削除して、それ以上の請求が発生しないようにします。詳細については、クリーンアップをご覧ください。

始める前に

パラメータ化されたセキュアビューを作成する前に、次の前提条件を満たしてください。

課金と必要な API を有効にする

  1. Google Cloud コンソールでプロジェクトを選択します。

    プロジェクト セレクタに移動

  2. Google Cloud プロジェクトに対して課金が有効になっていることを確認します。

  3. Cloud SQL の作成と接続に必要な Cloud APIs を有効にします。

    1. API を有効にする

    2. [プロジェクトを確認] の手順で、[次へ] をクリックして、変更するプロジェクトの名前を確認します。

    3. [API を有効にする] の手順で、[有効にする] をクリックして、次の機能を有効にします。

      • Cloud SQL for MySQL API
      • Knowledge Catalog API
      • Data Analytics API with Gemini（geminidataanalytics.googleapis.com）

データベースを作成して接続する

  1. インスタンスを作成する。
  2. インスタンスに接続してデータベースを作成します。

  3. QueryData API がクエリを実行できるように、Cloud SQL for MySQL インスタンスで Cloud SQL Data API を有効にします。

    gcloud sql instances patch INSTANCE_ID \
    --project=PROJECT_ID \
    --data-api-access=ALLOW_DATA_API

    次のように置き換えます。

    • INSTANCE_ID: Cloud SQL for MySQL インスタンスの ID。
    • PROJECT_ID: 実際の Google Cloud プロジェクト ID。

必要なロールを付与して IAM データベース ユーザーを作成する

Identity and Access Management（IAM）で認証し、パラメータ化されたセキュアビューに対して queryData リクエストを実行するには、IAM ユーザーまたはサービス アカウントを構成します。

  1. Google Cloud プロジェクトの IAM_USER_EMAIL に次の IAM ロールを付与します。

    • roles/geminidataanalytics.queryDataUser
    • roles/serviceusage.serviceUsageConsumer
    • roles/cloudsql.instanceUser

    IAM ロールの付与の詳細については、プロジェクトの IAM ポリシー バインディングを追加するをご覧ください。

  2. Cloud SQL インスタンスで IAM データベース認証を有効にし、インスタンスに IAM データベース ユーザーとして IAM_USER_EMAIL を追加します。この IAM データベース ユーザーに psv_user データベース ロールを付与する前に、この IAM データベース ユーザーを作成する必要があります。IAM データベース ユーザーの設定の詳細については、IAM データベース認証でユーザーを管理するをご覧ください。

環境を準備する

パラメータ化されたセキュアビューでクエリを実行する準備をするには、データベース、データベース ロール、アプリケーション スキーマを設定します。

データベース フラグを有効にする

  • Cloud SQL for MySQL インスタンスで Cloud SQL for MySQL 8.0.43 以降が実行されていることを確認します。Cloud SQL for MySQL 8.0 インスタンスで以前のマイナー バージョンが実行されている場合は、マイナー バージョンのアップグレードを実行できます。
  • インスタンスの cloudsql_parameterized_secure_view データベース フラグを有効にします。このフラグの変更でデータベースを再起動する必要はありません。詳細については、インスタンスのデータベース フラグを構成するをご覧ください。

データベースとロールを設定する

  1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

    Cloud SQL に移動

  2. リストからインスタンスを選択します。

  3. ナビゲーション メニューで [Cloud SQL Studio] をクリックします。

  4. 管理者権限を持つユーザー アカウントを使用して Studio にログインします。

  5. [認証] をクリックします。[エクスプローラ] ペインに、データベースにあるオブジェクトのリストが表示されます。

  6. [新しい SQL エディタタブ] または [新しいタブ] をクリックして、新しいタブを開きます。

  7. アプリケーション データとビュー用に store_db という名前のデータベースを作成します。

    CREATE DATABASE IF NOT EXISTS store_db;
USE store_db;

  8. パラメータ化されたビューに対してクエリを実行する新しいデータベース ロールを作成します。アプリケーションは、このロールを使用してデータベースに接続し、承認済みビューに厳密に制限されたアクセス権でクエリを実行します。

    CREATE ROLE 'psv_user';

テーブルを作成してデータを挿入する

  1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

    [Cloud SQL] に移動

  2. リストから Cloud SQL for MySQL インスタンスを選択します。

  3. ナビゲーション メニューで [Cloud SQL Studio] をクリックします。

  4. データベース認証を使用して Studio にログインします。

  5. [新しいタブ] をクリックして、新しい SQL エディタタブを開きます。

  6. テーブルを作成してデータを挿入します。

    CREATE TABLE store_db.checked_items (
bag_id INT,
timestamp TIMESTAMP,
loc_code CHAR(3),
scan_type CHAR(1),
location TEXT,
customer_id INT
);

INSERT INTO store_db.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

パラメータ化されたセキュアビューを作成し、アクセス権限を設定する

パラメータ化されたセキュアビューを作成する手順は次のとおりです。

  1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

    Cloud SQL に移動

  2. リストからインスタンスを選択します。

  3. ナビゲーション メニューで [Cloud SQL Studio] をクリックします。

  4. Studio にログインし、前のセクションで使用した管理者ユーザー アカウント（root や ADMIN_USER など）として store_db データベースに接続します。

  5. [認証] をクリックします。[エクスプローラ] ペインに、データベースにあるオブジェクトのリストが表示されます。

  6. [新しい SQL エディタタブ] または [新しいタブ] をクリックして、新しいタブを開きます。

  7. ビューへのアクセスを制限するには、パラメータ化されたビューを作成します。

    CREATE VIEW store_db.secure_checked_items AS
SELECT bag_id, timestamp, location
FROM store_db.checked_items t
WHERE customer_id = @app_end_userid;

  8. パラメータ化されたビューに対する SELECT アクセス権を psv_user ロールに付与します。

    GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

  9. パラメータ化されたクエリを安全に実行するために使用される組み込みシステム ストアド プロシージャに対する実行権限を付与します。

    GRANT EXECUTE ON PROCEDURE mysql.execute_parameterized_query TO 'psv_user';

  10. 省略可。ユーザーがセキュアビューをバイパスできないように、psv_user からベーステーブルに対する直接権限を明示的に取り消します。

    REVOKE ALL PRIVILEGES ON store_db.checked_items FROM 'psv_user';

  11. IAM_USER_EMAIL が IAM データベース ユーザーとしてインスタンスに追加されていることを確認し、IAM 認証されたデータベース ユーザーまたはアプリケーション ログイン アカウントに psv_user ロールを付与します。

    GRANT 'psv_user' TO 'IAM_USERNAME'@'%';

SET DEFAULT ROLE 'psv_user' TO 'IAM_USERNAME'@'%';

    IAM_USERNAME は、IAM データベースのユーザー名またはアプリケーションのログイン アカウントに置き換えます。IAM ユーザー アカウントの場合、Cloud SQL for MySQL はメールアドレスから @ とドメイン名を削除するため、@ の前にメールの接頭辞（user@example.com の場合は user など）を指定します。

データ セキュリティを確認する

パラメータ化されたセキュアビューをクエリできることを確認する手順は次のとおりです。

  1. 制限付きの IAM メールアドレスまたはアプリケーション ログインを使用して Cloud SQL Studio にログインします。

  2. [新しいタブ] をクリックして、新しい SQL エディタタブを開きます。

  3. ベーステーブルに対してクエリを実行し、アクセスが拒否されることを確認します。

    SELECT * FROM store_db.checked_items;

    データベースが ERROR 1142 (42000): SELECT command denied to user 'IAM_USERNAME'@'%' for table 'checked_items' エラーを返します。

  4. 組み込みの mysql.execute_parameterized_query ストアド プロシージャを使用して、パラメータ化されたセキュアビューをクエリします。

    CALL mysql.execute_parameterized_query(
  'SELECT * FROM store_db.secure_checked_items',
  'app_end_userid=303'
);

  5. gcloud auth application-default login を実行して IAM_USER_EMAIL として Google Cloud CLI にログインし、gcloud auth application-default print-access-token が制限付き IAM データベース ユーザーのトークンを生成するようにします。次に、PSV パラメータを指定して queryData メソッドを使用し、パラメータ化されたセキュアビューをクエリします。

     curl -X POST \
   "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
   -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
   -H "x-goog-user-project: PROJECT_ID" \
   -H "Content-Type: application/json; charset=utf-8" \
   -d '{
     "prompt": "Show me the checked items.",
     "context": {
       "datasource_references": {
         "cloud_sql_reference": {
           "database_reference": {
             "engine": "MYSQL",
             "project_id": "PROJECT_ID",
             "region": "REGION",
             "instance_id": "INSTANCE_ID",
             "database_id": "DATABASE_ID",
             "table_ids": [
               "secure_checked_items"
             ]
           }
         }
       },
       "parameterized_secure_view_parameters": {
         "parameters": [
           {
             "key": "app_end_userid",
             "value": "303"
           }
         ]
       }
     },
     "generation_options": {
       "generate_query_result": true,
       "generate_natural_language_answer": true,
       "generate_explanation": true
     }
   }'

    database_reference の table_ids フィールドを使用して、QueryData が secure_checked_items パラメータ化されたセキュアビューに対してのみ SQL を生成するようにします。table_ids を省略し、データベース ロールに checked_items ベーステーブルに対する SELECT アクセス権がある場合、QueryData はセキュアビューをバイパスしてベーステーブルに直接クエリを実行する可能性があります。

    次のように置き換えます。

    • PROJECT_ID: 実際の Google Cloud プロジェクト ID。
    • REGION: Cloud SQL for MySQL インスタンスが配置されているリージョン。
    • INSTANCE_ID: Cloud SQL for MySQL インスタンスの ID。
    • DATABASE_ID: Cloud SQL for MySQL データベースの ID。このチュートリアルでは store_db です。

クリーンアップ

インスタンスの削除

始める前にセクションで作成したインスタンスを削除すると、作成したすべてのオブジェクトも削除されます。

  1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

    Cloud SQL に移動

  2. リストからインスタンスを選択します。

  3. [削除] をクリックします。

  4. インスタンス名を入力し [削除] をクリックして、インスタンスの削除を確定します。

次のステップ

  • パラメータ化されたセキュアビューについて学習する。

  • パラメータ化されたセキュアビューを使用する方法を学習する。

    •