Google 会使用 AI 技术将内容翻译成您偏好的语言。AI 翻译可能包含错误。

使用参数化安全视图安全地访问和控制应用数据

参数化安全视图可为应用提供数据安全和行级访问权限控制。它们会根据应用用户的身份过滤查询结果。

本教程介绍了如何在 Cloud SQL 中设置参数化安全视图、配置数据库角色和权限以限制对基表的访问，以及验证数据安全性。本文档中提供的示例仅用于演示目的。

什么是参数化安全视图？

作为一般最佳实践，您的应用应使用对数据库具有最低必需访问权限的服务账号运行。例如，如果您的应用不应写入数据库，则应使用具有只读权限的角色。您应在数据库级层配置这些访问权限控制。

如果您的应用需要比标准数据库级访问权限更精细的安全性，您可以使用参数化安全视图来确保用户只能查看其授权的数据。参数化安全视图提供适用于多租户 SaaS 应用的动态数据安全和行级访问权限控制，而无需为每个应用最终用户创建单独的数据库角色。

使用参数化安全视图有三大主要优势：

动态数据安全和行访问权限控制：使用最终用户标识符过滤查询，以便用户只能访问其授权的数据，而不管查询措辞如何。
更轻松的角色管理：为所有应用用户使用单个共享数据库角色，而不是为每个用户管理单独的数据库角色。

目标

使用指定视图参数创建参数化安全视图。
创建供应用用于连接到数据库和访问参数化安全视图的数据库角色。
向新角色授予对参数化安全视图的权限，并撤消对基表的访问权限。
使用新角色进行连接，并验证是否无法访问受限表。
使用 execute_parameterized_query() 函数或 QueryData API 对参数化安全视图运行查询。

费用

在本文档中，您将使用 Google Cloud的以下收费组件：

Cloud SQL for PostgreSQL

您可使用价格计算器根据您的预计使用量来估算费用。

新 Google Cloud 用户可能有资格申请免费试用。

为避免继续计费，请在完成本文档中的任务后删除您创建的资源。如需了解详情，请参阅清理。

准备工作

在创建参数化安全视图之前，请先完成以下前提条件。

启用结算功能及所需 API

在 Google Cloud 控制台中，选择一个项目。

转到“项目选择器”
确保您的 Google Cloud 项目已启用结算功能。
启用创建和连接到 Cloud SQL 所需的 Cloud API。
1. 启用该 API
2. 在确认项目步骤中，点击下一步以确认您要更改的项目的名称。
3. 在启用 API 步骤中，点击启用以启用以下内容：
  - Cloud SQL for PostgreSQL API
  - Knowledge Catalog API
  - Data Analytics API with Gemini (geminidataanalytics.googleapis.com)

创建并连接到数据库

创建实例。
连接到您的实例并创建数据库。
在 Cloud SQL for PostgreSQL 实例上启用 Cloud SQL Data API，以便 QueryData API 可以对其执行查询：
```
gcloud sql instances patch INSTANCE_ID \
    --project=PROJECT_ID \
    --data-api-access=ALLOW_DATA_API
```
替换以下内容：
- INSTANCE_ID：Cloud SQL for PostgreSQL 实例的 ID。
- PROJECT_ID：您的 Google Cloud 项目 ID。

授予所需角色并创建 IAM 数据库用户

如需使用 Identity and Access Management (IAM) 进行身份验证并针对参数化安全视图运行 queryData 请求，请配置 IAM 用户或服务账号：

在您的 Google Cloud 项目中向 IAM_USER_EMAIL 授予以下 IAM 角色：
- roles/geminidataanalytics.queryDataUser
- roles/serviceusage.serviceUsageConsumer
- roles/cloudsql.instanceUser
如需详细了解如何授予 IAM 角色，请参阅为项目添加 IAM 政策绑定。
在 Cloud SQL 实例上启用 IAM 数据库身份验证，并将 IAM_USER_EMAIL 添加为该实例上的 IAM 数据库用户。您必须先创建此 IAM 数据库用户，然后才能向其授予 psv_user 数据库角色。如需详细了解如何设置 IAM 数据库用户，请参阅使用 IAM 数据库身份验证管理用户。

准备环境

为了准备对参数化安全视图运行查询，请设置数据库、数据库角色、parameterized_views 扩展程序以及应用架构。

启用数据库标志

启用 cloudsql.enable_parameterized_views 数据库标志，以加载所需的扩展程序库。如需了解详情，请参阅配置实例的数据库标志。

设置数据库

为应用数据和参数化安全视图创建一个名为 database 的数据库。如需了解详情，请参阅创建数据库。

创建数据库角色、扩展程序和应用架构

在 Google Cloud 控制台中，前往 Cloud SQL 页面。

前往 Cloud SQL
从列表中选择一个实例。
在导航菜单中，点击 Cloud SQL Studio。
使用管理员账号身份验证登录工作室。
点击身份验证。 “探索器”窗格会显示数据库中的对象列表。
点击新的 SQL 编辑器标签页或新标签页以打开新标签页。
如需使用参数化视图，请在数据库中创建 parameterized_views 扩展程序：
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
创建扩展程序时，数据库还会创建一个名为 parameterized_views 的架构，以便将 API 包含在该架构的命名空间中，并确保这些 API 不会与现有 API 发生冲突。

注意：您必须在创建了参数化视图的每个数据库中创建扩展程序。
以拥有超级用户权限的用户（例如内置 postgres 用户）身份登录。
创建新的数据库角色，用于针对参数化安全视图执行查询。这是一个 Cloud SQL 角色，应用使用该角色连接并登录数据库来执行查询，但对公共函数或对象的访问权限仅限于所需的一组最小权限。
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
如需了解详情，请参阅 CREATE USER。
以管理员用户身份连接。
```
SET role TO postgres;
```
创建包含表的架构。
```
CREATE SCHEMA store;
```

创建表并插入数据。

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

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

创建参数化安全视图并设置访问权限

如需创建参数化安全视图并为基表和视图设置适当的访问权限，请按照以下步骤操作：

在 Google Cloud 控制台中，前往 Cloud SQL 页面。

前往 Cloud SQL
从列表中选择一个实例。
在导航菜单中，点击 Cloud SQL Studio。
登录 Studio，并以 postgres 用户身份连接到名为 database 的数据库。
点击身份验证。 “探索器”窗格会显示数据库中的对象列表。
点击新的 SQL 编辑器标签页或新标签页以打开新标签页。

如需提供对视图的有限访问权限，请创建参数化视图：

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

授予对视图的访问权限。

GRANT SELECT ON store.secure_checked_items TO psv_user;

如需访问该视图，请授予对相应架构的访问权限。
```
GRANT USAGE ON SCHEMA store TO psv_user;
```
撤消对基表的直接访问权限。
```
REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;
```
注意：参数化视图的所有者必须对基表拥有 SELECT 权限。此外，参数化视图的用户必须对视图的架构具有 USAGE 权限，并对视图具有 SELECT 权限。例如，postgres 用户拥有参数化视图，因此它对基表具有 SELECT 权限，而 psv_user 必须对 store 架构具有 USAGE 权限，并对视图具有 SELECT 权限。
确保 IAM_USER_EMAIL 已作为 IAM 数据库用户添加到实例中，以 postgres 用户身份登录，并向 IAM 身份验证用户授予 psv_user 角色：
```
GRANT psv_user TO "IAM_USER_EMAIL";
```
将 IAM_USER_EMAIL 替换为您的 IAM 用户邮箱。

验证数据安全

如需验证参数化安全视图是否限制对指定视图的访问权限，请以经过 IAM 身份验证的用户身份登录数据库。在 Cloud SQL 中，IAM 用户会继承分配给他们的数据库角色的权限。

以经过 IAM 身份验证的用户身份登录数据库。

验证是否无法访问基表。

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

使用 execute_parameterized_query 函数访问参数化安全视图：

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

运行 gcloud auth application-default login，以 IAM_USER_EMAIL 身份登录 Google Cloud CLI，以便 gcloud auth application-default print-access-token 为受限 IAM 数据库用户生成令牌，然后使用 queryData 方法和 PSV 参数查询参数化安全视图：

  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": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "instance_id": "INSTANCE_ID",
              "database_id": "DATABASE_ID",
              "table_ids": [
                "store.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 仅针对 store.secure_checked_items 参数化安全视图生成 SQL。如果您省略 table_ids，并且数据库角色对 store.checked_items 基表具有 SELECT 访问权限，则 QueryData 可能会直接查询基表，从而绕过安全视图。请指定架构限定的视图名称，因为 table_ids 中的非限定名称默认为 public 架构。

替换以下内容：

PROJECT_ID：您的 Google Cloud 项目 ID。
REGION：Cloud SQL for PostgreSQL 实例所在的区域。
INSTANCE_ID：Cloud SQL for PostgreSQL 实例的 ID。
DATABASE_ID：Cloud SQL for PostgreSQL 数据库的 ID，在本教程中为 database。

清理

删除实例

删除您在准备工作部分中创建的实例时，您创建的所有对象也会删除。

在 Google Cloud 控制台中，前往 Cloud SQL 页面。

前往 Cloud SQL
从列表中选择一个实例。
点击删除。
输入实例名称，然后点击删除，以确认您要删除该实例。

后续步骤

了解参数化安全视图。

了解如何使用参数化安全视图。