这是本节的多页打印视图。点击此处打印.

访问策略

1: 创建列访问策略
2: 创建行访问策略
3: 访问策略和 DML 操作
4: 访问策略和查询优化
5: 管理访问策略

CREATE ACCESS POLICY 可用于在表上创建访问策略，以指定某些用户和角色可以从这些表中查询多少数据。访问策略通常会阻止这些用户查看表中特定列和行的数据。您可以将访问策略应用于表列和行。如果表的行和列上都有访问策略，Vertica 首先筛选行访问策略，然后筛选列访问策略。

可为任何表类型（列式、外部或 Flex 表）创建大部分访问策略。不能在 Flex 表上创建列访问策略。也可以对任何列类型（包括联接）上创建访问策略。

1 - 创建列访问策略

CREATE ACCESS POLICY 可以在各个表列上创建访问策略，每列一个策略。每个列访问策略都允许您为不同的用户和角色指定对该列数据的不同访问级别。列访问表达式还可以指定如何为用户和角色呈现列数据。

以下示例在 client_dimension 表中的 customer_address 列上创建访问策略。此访问策略为具有 administrator 角色的非超级用户提供对该列中所有数据的完全访问权限，但会屏蔽所有其他用户的客户地址数据：

=> CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address
-> CASE
-> WHEN ENABLED_ROLE('administrator') THEN customer_address
-> ELSE '**************'
-> END ENABLE;
CREATE ACCESS POLICY

Vertica 使用此策略来确定它授予用户 MaxineT 和 MikeL 的访问权限，对于这两个用户分别分配了 employee 和 administrator 角色。当这些用户查询 customer_dimension 表时，Vertica 会应用列访问策略表达式，如下所示：

=> \c - MaxineT;
You are now connected as user "MaxineT".
=> SET ROLE employee;
SET
=> SELECT customer_type, customer_name, customer_gender, customer_address, customer_city FROM customer_dimension;
 customer_type |      customer_name      | customer_gender | customer_address |  customer_city
---------------+-------------------------+-----------------+------------------+------------------
 Individual    | Craig S. Robinson       | Male            | **************   | Fayetteville
 Individual    | Mark M. Kramer          | Male            | **************   | Joliet
 Individual    | Barbara S. Farmer       | Female          | **************   | Alexandria
 Individual    | Julie S. McNulty        | Female          | **************   | Grand Prairie
 ...

=> \c - MikeL
You are now connected as user "MikeL".
=> SET ROLE administrator;
SET
=> SELECT customer_type, customer_name, customer_gender, customer_address, customer_city FROM customer_dimension;
 customer_type |      customer_name      | customer_gender | customer_address |  customer_city
---------------+-------------------------+-----------------+------------------+------------------
 Individual    | Craig S. Robinson       | Male            | 138 Alden Ave    | Fayetteville
 Individual    | Mark M. Kramer          | Male            | 311 Green St     | Joliet
 Individual    | Barbara S. Farmer       | Female          | 256 Cherry St    | Alexandria
 Individual    | Julie S. McNulty        | Female          | 459 Essex St     | Grand Prairie
 ...

限制

访问策略具有以下限制：

一列只能有一个访问策略。
不能为除原生数组以外的复杂类型列设置列访问策略。
不能为　Flex 表中的实体化列设置列访问策略。可以为 __raw__ 列设置访问策略，但此举将限制对整个表的访问。
行访问策略对临时表和具有聚合投影的表无效。
访问策略表达式不能包含：
- 子查询
- 聚合函数
- 分析函数
- 用户定义的转换函数 (UDTF)
如果查询优化器无法使用计算值替换仅涉及常量的确定性表达式，则会阻止所有 DML 操作，例如 INSERT。

2 - 创建行访问策略

CREATE ACCESS POLICY 可以为给定的表创建单行访问策略。此策略允许您为不同的用户和角色指定对表行数据的不同级别的访问权限。当用户启动查询时，Vertica 会对所有表行上访问策略的 WHERE 表达式求值。查询仅返回当前用户或角色的表达式求值结果为 true 的行。

例如，您可能希望为四个角色指定对表 store.store_store_sales 的不同访问级别：

employee：具有此角色的用户只能访问 employee_key 列中将他们标识为员工的销售记录。以下查询显示有多少销售记录（在 store.store_sales_fact 中）与每个用户（在 public.emp_dimension 中）相关联：

=> SELECT COUNT(sf.employee_key) AS 'Total Sales', sf.employee_key, ed.user_name FROM store.store_sales_fact sf
     JOIN emp_dimension ed ON sf.employee_key=ed.employee_key
     WHERE ed.job_title='Sales Associate' GROUP BY sf.employee_key, ed.user_name ORDER BY sf.employee_key

 Total Sales | employee_key |  user_name
-------------+--------------+-------------
         533 |          111 | LucasLC
         442 |          124 | JohnSN
         487 |          127 | SamNS
         477 |          132 | MeghanMD
         545 |          140 | HaroldON
         ...
         563 |         1991 | MidoriMG
         367 |         1993 | ThomZM
(318 rows)

regional_manager：具有此角色 (public.emp_dimension) 的用户只能访问他们管理的销售区域 (store.store_dimension) 的销售记录：

=> SELECT distinct sd.store_region, ed.user_name, ed.employee_key, ed.job_title FROM store.store_dimension sd
     JOIN emp_dimension ed ON sd.store_region=ed.employee_region WHERE ed.job_title = 'Regional Manager';
 store_region | user_name | employee_key |    job_title
--------------+-----------+--------------+------------------
 West         | JamesGD   |         1070 | Regional Manager
 South        | SharonDM  |         1710 | Regional Manager
 East         | BenOV     |          593 | Regional Manager
 MidWest      | LilyCP    |          611 | Regional Manager
 NorthWest    | CarlaTG   |         1058 | Regional Manager
 SouthWest    | MarcusNK  |          150 | Regional Manager
(6 rows)

dbadmin 和 administrator：具有这些角色的用户可以无限制地访问所有表数据。

给定这些用户以及与他们关联的数据，您可以在 store.store_store_sales 上创建如下所示的行访问策略：

CREATE ACCESS POLICY ON store.store_sales_fact FOR ROWS WHERE
   (ENABLED_ROLE('employee')) AND (store.store_sales_fact.employee_key IN
     (SELECT employee_key FROM public.emp_dimension WHERE user_name=CURRENT_USER()))
   OR
   (ENABLED_ROLE('regional_manager')) AND (store.store_sales_fact.store_key IN
     (SELECT sd.store_key FROM store.store_dimension sd
      JOIN emp_dimension ed ON sd.store_region=ed.employee_region WHERE ed.user_name = CURRENT_USER()))
   OR ENABLED_ROLE('dbadmin')
   OR ENABLED_ROLE ('administrator')
ENABLE;

重要

在此示例中，行策略仅允许策略的 WHERE 表达式中显式包含的一组角色访问表数据。所有其他角色和用户都被隐式拒绝访问表数据。

以下示例指示具有指定角色的用户可以使用的不同访问级别：

dbadmin 可以访问 store.store_sales_fact 中的所有行：

=> \c
You are now connected as user "dbadmin".
=> SELECT count(*) FROM store.store_sales_fact;
  count
---------
 5000000
(1 row)

用户 LilyCP 具有 regional_manager 角色，因此她可以访问她管理的中西部地区的所有销售数据：


=> \c - LilyCP;
You are now connected as user "LilyCP".
=> SET ROLE regional_manager;
SET
=> SELECT count(*) FROM store.store_sales_fact;
 count
--------
 782272
(1 row)

用户 SamRJ 具有 employee 角色，因此他只能访问与其关联的销售数据：


=> \c - SamRJ;
You are now connected as user "SamRJ".
=> SET ROLE employee;
SET
=> SELECT count(*) FROM store.store_sales_fact;
 count
-------
   417
(1 row)

限制

以下限制适用于行访问策略：

一个表只能有一个行访问策略。
下表中的行访问策略无效：
- 具有聚合投影的表
- 临时表
- 系统表
- 视图
您不能在具有行访问策略的表上创建定向查询。

3 - 访问策略和 DML 操作

默认情况下，Vertica 遵守以下规则：用户只能编辑他们可以看到的内容。也就是说，您必须能够看到表中所有行和列的原始值（存储在表中）以及最初为它们定义的数据类型，才能执行对表数据进行修改的操作。例如，如果某一列被定义为 VARCHAR(9)，并且该列的访问策略指定同一列为 VARCHAR(10)，则使用该访问策略的用户将无法执行以下操作：

INSERT
UPDATE
DELETE
MERGE
COPY

您可以通过在新或现有访问策略中指定 GRANT TRUSTED 来覆盖此行为。在评估用户能否执行上述操作时，此选项强制访问策略完全遵循显式 GRANT 语句。

您可以使用 ACCESS_POLICY 系统表查看现有访问策略。

行访问权限

在启用行访问策略的表上，只有当行访问策略中的条件求值为 TRUE 时，才能执行 DML 操作。例如：

t1 如下所示：

 A | B
---+---
 1 | 1
 2 | 2
 3 | 3

在 t1 上创建以下行访问策略：

=> CREATE ACCESS POLICY ON t1 for ROWS
WHERE enabled_role('manager')
OR
A<2
ENABLE;

启用此策略后，对于要执行 DML 操作的用户，存在以下行为：

具有管理员角色的用户可以在表中的所有行上执行 DML，因为策略中的 WHERE 子句评估为 TRUE。
具有非管理员角色的用户只能执行 SELECT 以返回列 A 中值小于 2 的数据。如果访问策略必须读取表中的数据以符合某个条件，则不允许执行 DML 操作。

列访问权限

在启用列访问策略的表上，如果可以查看最初为整个列定义的类型，则可以执行 DML 操作。

假设使用以下数据类型和值创建表 t1：

=> CREATE TABLE t1 (A int, B int);
=> INSERT INTO t1 VALUES (1,2);
=> SELECT * FROM t1;
 A | B
---+---
 1 | 2
(1 row)

假设创建了以下访问策略，该策略在执行时将列 A 的数据类型从 INT 强制为 VARCHAR(20)。

=> CREATE ACCESS POLICY on t1 FOR column A A::VARCHAR(20) ENABLE;
Column "A" is of type int but expression in Access Policy is of type varchar(20). It will be coerced at execution time

在这种情况下，u1 可以查看 A 列的全部内容，但由于活动访问策略未指定 A 列的原始数据类型，因此 u1 无法对 A 列执行 DML 操作。

=> \c - u1
You are now connected as user "u1".
=> SELECT A FROM t1;
 A
---
 1
(1 row)


=> INSERT INTO t1 VALUES (3);
ERROR 6538:  Unable to INSERT: "Access denied due to active access policy on table "t1" for column "A""

使用 GRANT TRUSTED 覆盖默认行为

在访问策略中指定 GRANT TRUSTED 会覆盖默认行为（“用户只能编辑他们可以看到的内容”），并指示访问策略在评估用户能否执行 DML 操作时完全遵循显式 GRANT 语句。

重要

GRANT TRUSTED 让用户可以对表进行更改，即使数据被访问策略所覆盖也是如此。在这些情况下，某些受访问策略约束但在表上也有 GRANT 的用户可以对他们无法查看或验证的数据进行更改。

如果数据存储形式与语义上的“真”形式不匹配，GRANT TRUSTED 很有用。

例如，当与 Voltage SecureData 集成时，一个常见的用例是使用 VoltageSecureProtect 存储加密数据，其中解密留给访问策略中用来调用 VoltageSecureAccess 的 case 表达式。在这种情况下，虽然解密形式被直观地理解为数据的“真”形式，但它仍然以其加密形式存储在表中；可以查看解密数据的用户将看不到存储的数据，因此无法执行 DML 操作。您可以使用 GRANT TRUSTED 来覆盖此行为，并允许用户在拥有授权的情况下执行这些操作。

在以下示例中，customer_info 表包含客户名字和姓氏以及 SSN 的列。SSN 很敏感，应该控制对它的访问，因此在插入表时使用 VoltageSecureProtect 对其进行加密：


=> CREATE TABLE customer_info(first_name VARCHAR, last_name VARCHAR, ssn VARCHAR);
=> INSERT INTO customer_info SELECT 'Alice', 'Smith', VoltageSecureProtect('998-42-4910' USING PARAMETERS format='ssn');
=> INSERT INTO customer_info SELECT 'Robert', 'Eve', VoltageSecureProtect('899-28-1303' USING PARAMETERS format='ssn');
=> SELECT * FROM customer_info;
 first_name | last_name |     ssn
------------+-----------+-------------
 Alice      | Smith     | 967-63-8030
 Robert     | Eve       | 486-41-3371
(2 rows)

在此系统中，角色“trusted_ssn”标识特权用户，Vertica 将为这些特权用户使用 VoltageSecureAccess 对“ssn”列的值进行解密。为了允许这些特权用户执行他们有权执行的 DML 操作，您可以使用以下访问策略：

=> CREATE ACCESS POLICY ON customer_info FOR COLUMN ssn
  CASE WHEN enabled_role('trusted_ssn') THEN VoltageSecureAccess(ssn USING PARAMETERS format='ssn')
  ELSE ssn END
  GRANT TRUSTED
  ENABLE;

同样请注意，GRANT TRUSTED 允许表上所有具有 GRANT 的用户执行指定的操作，包括没有“trusted_ssn”角色的用户。

4 - 访问策略和查询优化

访问策略会影响 Vertica Database Designer 生成的投影设计，以及优化器为查询执行创建的计划。

投影设计

Database Designer 为给定表创建投影时，将考虑适用于当前用户的访问策略。Database Designer 为表生成的投影集已针对该用户的访问权限以及具有类似访问权限的其他用户进行优化。但这些投影对于具有不同访问权限的用户可能不太理想。这些差异可能会对 Vertica 如何高效处理第二组用户的查询有一些影响。当您评估表的投影设计时，请选择一种为所有授权用户优化访问的设计。

查询重写

Vertica 优化器通过在其查询计划中重写用户查询来强制执行访问策略，这会影响查询性能。例如，clients 表具有行和列访问策略，均已启用。当用户查询此表时，查询优化器会生成一个用来重写查询的计划，因此它包含两个策略：

=> SELECT * FROM clients;

查询优化器生成一个查询计划，该计划按如下方式重写查询：

SELECT * FROM (
SELECT custID, password, CASE WHEN enabled_role('manager') THEN SSN ELSE substr(SSN, 8, 4) END AS SSN FROM clients
WHERE enabled_role('broker') AND
  clients.clientID IN (SELECT brokers.clientID FROM brokers WHERE broker_name = CURRENT_USER())
) clients;

5 - 管理访问策略

默认情况下，您只能管理您拥有的表的访问策略。您可以选择使用 AccessPolicyManagementSuperuserOnly 参数（默认值为 false）仅允许超级用户管理访问策略：

=> ALTER DATABASE DEFAULT SET PARAMETER AccessPolicyManagementSuperuserOnly = 1;
ALTER DATABASE

您可以通过多种方式查看和管理表的访问策略：

查看访问策略
修改访问策略的表达式
启用或禁用访问策略
将访问策略复制到另一个表

查看访问策略

您可以通过两种方式查看访问策略：

查询系统表 ACCESS_POLICY。例如，以下查询返回表 public.customer_dimension 上的所有访问策略：

=> \x
=> SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy WHERE table_name = 'public.customer_dimension';
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------
policy_type       | Column Policy
is_policy_enabled | Enabled
table_name        | public.customer_dimension
column_name       | customer_address
expression        | CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END

使用 EXPORT_TABLES、EXPORT_OBJECTS 或 EXPORT_CATALOG 从数据库编录导出表 DDL。例如：

=> SELECT export_tables('','customer_dimension');
                                export_tables
-----------------------------------------------------------------------------
CREATE TABLE public.customer_dimension
(
    customer_key int NOT NULL,
    customer_type varchar(16),
    customer_name varchar(256),
    customer_gender varchar(8),
    ...
    CONSTRAINT C_PRIMARY PRIMARY KEY (customer_key) DISABLED
);

CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END ENABLE;

修改访问策略表达式

ALTER ACCESS POLICY 可以修改现有访问策略的表达式。例如，您可以通过扩展对 dbadmin 角色的访问权限来修改前面示例中的访问策略：

=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address
    CASE WHEN enabled_role('dbadmin') THEN customer_address
         WHEN enabled_role('administrator') THEN customer_address
         ELSE '**************' END ENABLE;
ALTER ACCESS POLICY

查询系统表 ACCESS_POLICY 会确认此更改：

=> SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy
  WHERE table_name = 'public.customer_dimension' AND column_name='customer_address';
-[ RECORD 1 ]-----+-------------------------------------------------------------------------------------------------------------------------------------------
policy_type       | Column Policy
is_policy_enabled | Enabled
table_name        | public.customer_dimension
column_name       | customer_address
expression        | CASE WHEN enabled_role('dbadmin') THEN customer_address WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END

启用和禁用访问策略

表的所有者可以启用和禁用表中的行和列访问策略。

行访问策略

启用和禁用表中的访问策略。

ALTER ACCESS POLICY ON [schema.]table FOR ROWS { ENABLE | DISABLE }

以下示例先禁用后重新启用表 customer_dimension 上的行访问策略：

=> ALTER ACCESS POLICY ON customer_dimension FOR ROWS DISABLE;
ALTER ACCESS POLICY
=> ALTER ACCESS POLICY ON customer_dimension FOR ROWS ENABLE;
ALTER ACCESS POLICY

列访问策略

按如下方式启用和禁用表列的访问策略：

ALTER ACCESS POLICY ON [schema.]table FOR COLUMN column { ENABLE | DISABLE }

以下示例先禁用后重新启用表 customer_dimension.customer_address 上的同一个列访问策略：

=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address DISABLE;
ALTER ACCESS POLICY
=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address ENABLE;
ALTER ACCESS POLICY

复制访问策略

按如下方式将访问策略从一个表复制到另一个表。非超级用户必须同时拥有源表和目标表的所有权：

ALTER ACCESS POLICY ON [schema.]table { FOR COLUMN column | FOR ROWS } COPY TO TABLE table

当您使用以下函数（而非 CREATE TABLE AS SELECT 或 CREATE TABLE LIKE）创建表的副本或移动其内容时，会将原始表的访问策略复制到新/目标表：

若要将访问策略复制到另一个表，请使用 ALTER ACCESS POLICY。

注意

如果您使用 ALTER TABLE...RENAME TO 重命名表，则以旧名称存储的访问策略将存储在表的新名称下。

例如，您可以按如下方式复制行访问策略：

=> ALTER ACCESS POLICY ON public.emp_dimension FOR ROWS COPY TO TABLE public.regional_managers_dimension;

以下语句将列 employee_key 上的访问策略从表 public.emp_dimension 复制到 store.store_sales_fact：

=> ALTER ACCESS POLICY ON public.emp_dimension FOR COLUMN employee_key COPY TO TABLE store.store_sales_fact;

注意

复制的策略保留源策略的已启用/已禁用设置。