使用行访问策略¶

实施行访问策略¶

以下小节提供有关如何实施行访问策略的示例：

• 使用具有映射表查找的典型行访问策略。

• 将现有的行访问策略子查询替换为可记忆的函数，以提高查询性能。

• 在不同的行访问策略中引用受行访问策略保护的映射表。

示例：映射表查找¶

以下步骤是配置行访问策略权限以及向表和视图添加行访问策略的代表性指南。

这些步骤做出以下假设：

• 管理方法是集中的。

  如果行访问策略用例包括混合或分散管理方法，请参阅 管理行访问策略 以了解角色和权限的典型分布。

• 映射表是必需的，类似于 代表性用例：使用映射表筛选查询结果。

  以下步骤使用 CURRENT_ROLE 上下文函数来确定用户是否在查询结果中看到行，而代表性用例则侧重于用户的名称（即 CURRENT_USER）。

  如果角色激活和角色层次结构很重要，Snowflake 建议策略条件对账户角色使用 IS_ROLE_IN_SESSION 函数，并对数据库角色使用 IS_DATABASE_ROLE_IN_SESSION 函数。有关详细信息，请参阅 活动角色层次结构和映射表。

  即使上下文函数不同，使用映射表实施行访问策略的整个过程也保持不变。

• SECURITYADMIN 系统角色向自定义角色授予权限，以管理和实施行访问策略。

  如果您不想在生产环境中使用权限较高的角色（即 SECURITYADMIN 或 ACCOUNTADMIN），而是使用权限较低的自定义角色（如 database_admin、finance_admin），请验证权限较低的角色是否具有管理和实施行访问策略的必要权限。

  有关更多信息，请参阅 行访问策略权限 和 DDL 命令、操作和权限总结。

• 创建受行访问策略保护的表（步骤 1）和将行访问策略添加到表（步骤 5）有单独的步骤。在创建表时，可以向表添加行访问策略，前提是行访问策略已存在。有关语法的详细信息，请参阅 CREATE TABLE。

例如：

1. 为销售数据创建表：

   CopyExpand

   CREATE TABLE sales (
     customer   varchar,
     product    varchar,
     spend      decimal(20, 2),
     sale_date  date,
     region     varchar
   );
   

   Show lessSee more

   Scroll to top

2. 在 security 架构中，创建一个映射表，如 代表性示例 所示。此表定义了销售经理可以在 sales 表中看到哪些行：

   CopyExpand

   CREATE TABLE security.salesmanagerregions (
     sales_manager varchar,
     region        varchar
   );
   

   Show lessSee more

   Scroll to top

3. 接下来，安全管理员创建 mapping_role 自定义角色并将 SELECT 权限授予该自定义角色。此授权允许具有自定义角色的用户查询映射表：

   CopyExpand

   USE ROLE SECURITYADMIN;
   
   CREATE ROLE mapping_role;
   
   GRANT SELECT ON TABLE security.salesmanagerregions TO ROLE mapping_role;
   

   Show lessSee more

   Scroll to top

4. 使用架构所有者角色，创建具有以下两个条件的行访问策略：

   • 具有 sales_executive_role 自定义角色的用户可以查看所有行。

   • 具有 sales_manager 自定义角色的用户可以根据 salesmanagerregions 映射表查看行。

   请注意，系统会自动向架构所有者角色授予 CREATE ROW ACCESS POLICY 权限。如果其他角色应该能够创建行访问策略，则架构所有者角色可以将 CREATE ROW ACCESS 策略权限授予其他角色。

   CopyExpand

   USE ROLE schema_owner_role;
   
   CREATE OR REPLACE ROW ACCESS POLICY security.sales_policy
   AS (sales_region varchar) RETURNS BOOLEAN ->
     'sales_executive_role' = CURRENT_ROLE()
       OR EXISTS (
         SELECT 1 FROM salesmanagerregions
           WHERE sales_manager = CURRENT_ROLE()
           AND region = sales_region
       )
   ;
   

   Show lessSee more

   Scroll to top

   其中：

   security.sales_policy

   security 架构中行访问策略的名称。

   AS (sales_region varchar)

   行访问策略的签名。

   签名指定映射表属性和数据类型。返回的值确定用户是否有权访问添加行访问策略的表或视图上的给定行。

   'sales_executive_role' = CURRENT_ROLE()

   行访问策略内 body 的开头。

   行访问策略表达式的第一个条件，允许具有 sales_executive_role 自定义角色的用户查看数据。

   OR EXISTS (select 1 from salesmanagerregions WHERE sales_manager = CURRENT_ROLE() AND region = sales_region)

   使用子查询的行访问策略表达式的第二个条件。

   子查询要求 CURRENT_ROLE 为 sales_manager 自定义角色，并对数据执行查询以指定 {salesmanagerregions} 映射表中列出的区域。

   小技巧

   若要提高策略保护表上的查询性能，请用 可记忆的函数 替换 EXISTS 子句中的映射表查找子查询。

   有关详细信息，请参阅 可记忆的函数示例 （本主题内容）。

5. 使用 SECURITYADMIN 系统角色，执行以下两个语句：

   CopyExpand

   GRANT OWNERSHIP ON ROW ACCESS POLICY security.sales_policy TO mapping_role;
   
   GRANT APPLY ON ROW ACCESS POLICY security.sales_policy TO ROLE sales_analyst_role;
   

   Show lessSee more

   Scroll to top

   这两个 GRANT <privileges> ... TO ROLE 语句具有以下效果：

   • 策略的所有权不属于 SECURITYADMIN 系统角色。在查询运行时，Snowflake 使用授予自定义角色的权限，因为策略是以 所有者的权限 执行的，而不是具有更高权限的 SECURITYADMIN 系统角色。此方法支持最低权限原则。

   • sales_analyst_role 自定义角色可以根据需要在表中添加或删除行访问策略。

6. 将行访问策略添加（绑定）到 sales 数据表中的区域列：

   CopyExpand

   USE ROLE SECURITYADMIN;
   
   ALTER TABLE sales ADD ROW ACCESS POLICY security.sales_policy ON (region);
   

   Show lessSee more

   Scroll to top

7. 将受保护 sales 数据的 SELECT 权限授予 sales_manager_role 自定义角色：

   CopyExpand

   GRANT SELECT ON TABLE sales TO ROLE sales_manager_role;
   

   Show lessSee more

   Scroll to top

8. 销售数据填充 sales 数据后，测试行访问策略：

   CopyExpand

   USE ROLE sales_manager_role;
   SELECT product, SUM(spend)
   FROM sales
   WHERE YEAR(sale_date) = 2020
   GROUP BY product;
   

   Show lessSee more

   Scroll to top

示例：将策略子查询替换为可记忆的函数¶

本示例中的步骤为行访问策略条件中的每个映射表查找创建一个 可记忆的函数。每个 EXISTS 子句中的子查询指定映射表查找，其中表分别命名为 regions、customers 和 products：

CopyExpand

CREATE OR REPLACE ROW ACCESS POLICY rap_NO_memoizable_function
  AS (region_id number, customer_id number, product_id number)
  RETURNS BOOLEAN ->
    EXISTS(SELECT 1 FROM regions WHERE id = region_id) OR
    EXISTS(SELECT 1 FROM customers WHERE id = customer_id) OR
    EXISTS(SELECT 1 FROM products WHERE id = product_id)
  ;

Show lessSee more

Scroll to top

对于以下步骤，假设 rap_admin 自定义角色可以创建行访问策略（即具有对 SCHEMA CREATE ROW ACCESS POLICY 的权限）。

完成以下步骤，将每个行访问策略映射表查找替换为可记忆的函数：

1. 创建一个名为 functions_admin 的自定义角色来管理可记忆的函数：

   CopyExpand

   USE ROLE USERADMIN;
   
   CREATE ROLE functions_admin;
   

   Show lessSee more

   Scroll to top

2. 向 functions_admin 角色授予以下权限，以允许在名为 governance.functions 的现有架构中创建可记忆的函数：

   CopyExpand

   USE ROLE SECURITYADMIN;
   
   GRANT USAGE ON DATABASE governance TO ROLE functions_admin;
   
   GRANT USAGE ON SCHEMA governance.functions TO ROLE functions_admin;
   
   GRANT CREATE FUNCTION ON SCHEMA governance.functions TO ROLE functions_admin;
   

   Show lessSee more

   Scroll to top

3. 为行访问策略中的每个 EXISTS 子查询子句创建一个可记忆的函数。每个可记忆的函数定义都采用相同的形式。为简洁起见，仅显示一个函数示例：

   CopyExpand

   USE ROLE functions_admin;
   
   USE SCHEMA governance.functions;
   
   CREATE OR REPLACE function allowed_regions()
     RETURNS array
     memoizable
     AS 'SELECT ARRAY_AGG(id) FROM regions';
   

   Show lessSee more

   Scroll to top

4. 使用 CREATE ROW ACCESS POLICY 语句定义新的行访问策略，该策略将子查询替换为可记忆的函数：

   新的行访问策略允许在策略使用或不使用可记忆函数时测试对受保护表的查询，以量化在策略条件下使用可记忆函数的性能影响：

   CopyExpand

   USE ROLE rap_admin;
   
   CREATE OR REPLACE ROW ACCESS POLICY rap_with_memoizable_function
     AS (region_id number, customer_id number, product_id number)
     RETURNS BOOLEAN ->
       ARRAY_CONTAINS(region_id, allowed_regions()) OR
       ARRAY_CONTAINS(customer_id, allowed_customers()) OR
       ARRAY_CONTAINS(product_id, allowed_products())
     ;
   

   Show lessSee more

   Scroll to top

示例：使用行访问策略保护映射表¶

此示例说明如何在不同的行访问策略中引用受行访问策略保护的映射表。保护映射表的行访问策略调用 IS_ROLE_IN_SESSION 上下文函数来说明角色层次结构。不同的行访问策略可保护用户查询的表。此行访问策略使用子查询来执行映射表查找。例如：

1. 创建一个映射表，以根据地理销售区域定义允许的角色，并将数据插入到表中：

   CopyExpand

   CREATE OR REPLACE TABLE sales.tables.regional_managers (
     allowed_regions varchar
     allowed_roles varchar
   );
   

   Show lessSee more

   Scroll to top
   CopyExpand

   INSERT INTO sales.tables.regional_managers
   (allowed_regions, allowed_roles)
   VALUES
   ('na', 'NA_MANAGER'),
   ('eu', 'EU_MANAGER'),
   ('apac', 'APAC_MANAGER');
   

   Show lessSee more

   Scroll to top

2. 创建行访问策略以指定映射表中的 ALLOWED_ROLES 列：

   CopyExpand

   CREATE OR REPLACE ROW ACCESS POLICY governance.policies.rap_map_exempt
   AS (allowed_roles varchar) RETURNS BOOLEAN ->
   IS_ROLE_IN_SESSION(allowed_roles);
   

   Show lessSee more

   Scroll to top

3. 使用 ALTER TABLE 语句在映射表中添加行访问策略：

   CopyExpand

   ALTER TABLE sales.tables.regional_managers
     ADD ROW ACCESS POLICY governance.policies.rap_map_exempt
     ON (allowed_roles);
   

   Show lessSee more

   Scroll to top

4. 创建一个新的行访问策略，用于指定对受保护的映射表进行映射表查找：

   CopyExpand

    CREATE OR REPLACE ROW ACCESS POLICY governance.policies.rap_map_lookup
    AS (allowed_regions varchar) RETURNS BOOLEAN ->
    EXISTS (
      SELECT * FROM sales.tables.regional_managers
      WHERE
        REGION = allowed_regions
   );
   

   Show lessSee more

   Scroll to top

5. 使用 ALTER TABLE 语句在名为 sales.tables.data 的表中添加名为 governance.policies.rap_map_lookup 的行访问策略：

   CopyExpand

   ALTER TABLE sales.tables.data
     ADD ROW ACCESS POLICY governance.policies.rap_map_lookup
     ON (allowed_regions);
   

   Show lessSee more

   Scroll to top

6. 向映射表中的角色授予权限，以允许具有这些角色的用户查询受保护的数据。例如，这些授权适用于 na_manager 自定义角色：

   CopyExpand

   USE ROLE SECURITYADMIN;
   GRANT USAGE ON DATABASE sales TO ROLE na_manager;
   GRANT USAGE ON SCHEMA sales.tables TO ROLE na_manager;
   GRANT SELECT ON TABLE sales.tables.regional_managers TO ROLE na_manager;
   GRANT SELECT ON TABLE sales.tables.data TO ROLE na_manager;
   

   Show lessSee more

   Scroll to top

   如有必要，请对映射表中的每个角色重复此步骤中的命令。