アプリケーション データへのアクセスの保護と制御を行う

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

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

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

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

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

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

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

目標

  • 名前付きビュー パラメータを使用して、パラメータ化されたセキュアビューを作成します。
  • アプリケーションがデータベースに接続し、パラメータ化されたセキュアビューにアクセスするために使用するデータベースロールを作成します。
  • 新しいロールにパラメータ化されたセキュアビューに対する権限を付与し、ベーステーブルへのアクセス権を取り消します。
  • 新しいロールを使用して接続し、制限付きテーブルにアクセスできないことを確認します。
  • 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

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

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

環境を準備する

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

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

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

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

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

    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. コンソールで、[Cloud SQL] ページに移動します。 Google Cloud

    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. コンソールで、[Cloud SQL] ページに移動します。 Google Cloud

    Cloud SQL に移動

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

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

  4. Studio にログインし、前のセクションで使用した store_db ユーザー アカウントとして database に接続します。

  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 認証されたデータベース ユーザーまたはアプリケーション ログイン アカウントに psv_user ロールを付与します。

    GRANT 'psv_user' TO 'IAM_USER_EMAIL'@'%';

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

    IAM_USER_EMAIL は、IAM ユーザーのメールアドレスまたはアプリケーション ログイン アカウントに置き換えます。

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

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

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

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

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

    SELECT * FROM store_db.checked_items;

    データベースから ERROR 1142 (42000): SELECT command denied to user 'IAM_USER_EMAIL'@'%' 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. SQL 構文と PSV パラメータを使用した QueryData リクエストを使用して、パラメータ化されたセキュアビューをクエリします。

     curl -X POST \
   "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
   -H "Authorization: Bearer $(gcloud auth print-access-token)" \
   -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"
           }
         }
       },
       "parameterized_secure_view_parameters": {
         "parameters": {
           "key": "app_end_userid"
           "app_end_userid": "303"
         }
       }
     },
     "generation_options": {
       "generate_query_result": true,
       "generate_natural_language_answer": true,
       "generate_explanation": true
     }
   }'

    次の値を置き換えます。

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

クリーンアップ

クラスタの削除

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

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

    Cloud SQL に移動

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

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

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

次のステップ

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

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