このページは Cloud Translation API によって翻訳されました。

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

このチュートリアルでは、AlloyDB for PostgreSQL でパラメータ化されたセキュアビューを使用して、AlloyDB Studio または psql を使用してパラメータ化されたビューへのユーザーアクセスを制限する方法について説明します。

パラメータ化されたセキュアビューの機能を示す例が含まれています。これらの例はデモを目的としています。

前提条件

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

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

プロジェクトセレクタに移動
Google Cloud プロジェクトに対して課金が有効になっていることを確認します。
AlloyDB for PostgreSQL の作成と接続に必要な Cloud APIs を有効にします。

API を有効にする
1. [プロジェクトを確認] の手順で、[次へ] をクリックして、変更するプロジェクトの名前を確認します。
2. [API を有効にする] の手順で、[有効にする] をクリックして、次の機能を有効にします。
  - AlloyDB API

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

環境を準備する

パラメータ化されたセキュアビューでクエリを実行する準備をするには、まずパラメータ化されたビュー、データベースとデータベースのロール、parameterized_view 拡張機能、アプリケーションスキーマを設定する必要があります。

必要な拡張機能を有効にする

必要な拡張機能ライブラリを読み込む parameterized_views.enabled データベースフラグを有効にします。詳細については、インスタンスのデータベースフラグを構成するをご覧ください。

データベースを設定する

アプリケーションデータとパラメータ化されたビュー用に database というデータベースを作成します。詳細については、データベースを作成するをご覧ください。

データベースロール、拡張機能、アプリケーションスキーマを作成する

AlloyDB Studio または psql を使用して、postgres ユーザーとして、または AlloyDB スーパーユーザーの権限を持つユーザーとしてデータベースに接続します。
```
psql database -U postgres
```
詳細については、AlloyDB でのデータベースユーザー管理についてをご覧ください。
データベースに parameterized_views 拡張機能を作成します。
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
拡張機能が作成されると、parameterized_views という名前のスキーマも作成されます。これにより、API がそのスキーマの名前空間に含まれ、API が既存の API と競合しないようになります。

注: パラメータ化されたビューが作成されるすべてのデータベースに拡張機能を作成する必要があります。
データベースを所有して管理する AlloyDB 管理ロールを作成します。
```
CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
 GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;
```
詳細については、CREATE USER をご覧ください。
パラメータ化されたセキュアビューに対してクエリを実行する新しいデータベースロールを作成します。これは、アプリケーションがデータベースに接続してログインし、公開関数またはオブジェクトへのアクセスを最小限に制限してクエリを実行するために使用する AlloyDB ロールです。
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
詳細については、CREATE USER をご覧ください。
管理者権限を持つユーザーとして接続します。
```
SET role TO admin_user;
```
テーブルを含むスキーマを作成します。
```
CREATE SCHEMA schema;
```

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

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

INSERT INTO schema.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);

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

セキュアなパラメータ化ビューを作成し、ベーステーブルとビューに適切なアクセス権を設定するには、次の操作を行います。

AlloyDB Studio または psql を使用して、admin_user としてデータベースに接続します。
```
psql database -U admin_user
```

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

CREATE VIEW schema.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM schema.checked_items t
WHERE customer_id = $@app_end_userid;

ビューへのアクセス権を付与します。

GRANT SELECT ON schema.secure_checked_items TO psv_user;

ビューにアクセスするには、スキーマへのアクセス権を付与します。
```
GRANT USAGE ON SCHEMA schema TO psv_user;
```
ベーステーブルへの直接アクセスを取り消します。
```
REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;
```

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

パラメータ化されたセキュアビューが指定されたビューへのアクセスを制限していることを確認するには、psv_user として次のコマンドを実行します。これは、アプリケーションがデータベースに接続してログインし、クエリを実行するために使用する AlloyDB データベースロールです。

パラメータ化されたセキュアビューユーザーとして接続します。
```
psql database -U psv_user
```

ベーステーブルにアクセスできないことを確認します。

SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

execute_parameterized_query 関数を使用して、パラメータ化されたセキュアビューにアクセスします。

SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from schema.secure_checked_items',
  param_names => ARRAY ['app_end_userid'],
  param_values => ARRAY ['303']
);

自然言語生成クエリのセキュリティを強化するには、AlloyDB AI の自然言語を使用してパラメータ化されたセキュアビューを統合します。