授予用户对 Voltage SecureData 集成函数的访问权限

默认情况下，Vertica 用户无权访问 Voltage SecureData 集成函数。用户在没有正确权限的情况下尝试调用集成函数将收到以下错误：

=> SELECT id,
          first_name,
          last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf') AS ssn,
          dob
          FROM customers
          WHERE dob < '1970-1-1'
          ORDER BY id ASC
          LIMIT 10;
ERROR 6482:  Failed to parse Access Policies for table "customers" [Function
public.VoltageSecureProtect(varchar) does not exist, or permission is denied for
public.VoltageSecureProtect(varchar)]

用户必须具有对集成函数的 EXECUTE 访问权限才能使用它们。这些函数是 PUBLIC 架构的一部分。Voltage SecureData 集成库中有以下函数：

以下示例演示了如何向用户 Alice 授予对 VoltageSecureAccess 函数的访问权限，以便能够解密数据。

=> \c vmart dbadmin
You are now connected to database "vmart" as user "dbadmin".
=> GRANT EXECUTE ON FUNCTION public.VoltageSecureProtect(VARCHAR) TO alice;
GRANT PRIVILEGE
=> \c vmart alice
You are now connected to database "vmart" as user "alice".
=> SELECT id, first_name, last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf')
                              AS ssn,
          dob
          FROM customers
          WHERE dob < '1970-1-1'
          ORDER BY id ASC
          LIMIT 10;
  id  | first_name | last_name |     ssn     |    dob
------+------------+-----------+-------------+------------
 5345 | Thane      | Ross      | 559-32-0670 | 1902-03-09
 5348 | Basia      | Lopez     | 011-85-0705 | 1921-08-17
 5349 | Kaseem     | Hendrix   | 672-57-0309 | 1962-08-23
 5350 | Omar       | Lott      | 825-45-0131 | 1930-01-12
 5352 | Illana     | Middleton | 831-47-0929 | 1956-09-07
 5354 | Hanna      | Ware      | 694-97-0394 | 1903-07-16
 5358 | Mallory    | Vaughn    | 870-53-0272 | 1961-03-09
 5363 | Kirk       | Robinson  | 155-08-0085 | 1964-06-28
 5366 | Branden    | Coffey    | 709-38-0423 | 1923-06-11
 5367 | Raven      | Keith     | 250-31-0269 | 1918-07-31
(10 rows)

有关向用户授予对 UDx 的访问权限的详细说明，请参阅 GRANT（用户定义的扩展）。

为 SecureData 用户创建角色

您可以创建可以访问这些函数的角色，而不是向单个用户授予对每个函数的访问权限。然后，您可以向需要访问 SecureData 函数的用户授予对这些角色的访问权限。

考虑创建至少两个角色：一个用于访问 VoltageSecureConfigure 函数，另一个用于访问其他函数。在大多数情况下，并非所有用户都需要访问 VoltageSecureConfigure，尤其是当您选择创建单个全局配置文件时。有关使用 VoltageSecureConfigure 的详细信息，请参阅配置对 SecureData 的访问权限。

下面的例子：

  • 创建两个角色：有权保护和访问函数的 secure_data_users，以及有权访问 SecureDataConfigure 函数的 secure_data_admins。

  • 将 secure_data_user 角色授予用户 Alice

  • 将新角色设置为她的默认角色。

  • 切换到 Alice

  • 调用几个 SecureData 函数。

=> \c vmart dbadmin
You are now connected to database "vmart" as user "dbadmin".
=> CREATE ROLE secure_data_users;
CREATE ROLE
=> GRANT EXECUTE ON FUNCTION public.VoltageSecureAccess(varchar)
   TO secure_data_users;
GRANT PRIVILEGE
=> GRANT EXECUTE ON FUNCTION public.VoltageSecureProtect(varchar)
   TO secure_data_users;
GRANT PRIVILEGE
=> GRANT EXECUTE ON TRANSFORM FUNCTION
         public.VoltageSecureProtectAllKeys(varchar)
   TO secure_data_users;
GRANT PRIVILEGE
=> CREATE ROLE secure_data_admins;
CREATE ROLE
=> GRANT EXECUTE ON TRANSFORM FUNCTION public.VoltageSecureConfigure()
   TO secure_data_admins;
GRANT PRIVILEGE
=> GRANT secure_data_users TO ALICE;
GRANT ROLE
=> ALTER USER alice DEFAULT ROLE secure_data_users;
ALTER USER
=> \c vmart alice
You are now connected to database "vmart" as user "alice".
=> SET ROLE secure_data_users;
SET
=> SELECT VoltageSecureProtect('123-45-6789'
                               USING PARAMETERS format='ssn',
                               config_dfs_path='/voltagesecure/conf');
 VoltageSecureProtect
----------------------
 376-69-6789
(1 row)
=> SELECT VoltageSecureAccess('376-69-6789'
                              USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf');
 VoltageSecureAccess
---------------------
 123-45-6789
(1 row)

=> SELECT VoltageSecureConfigure(USING PARAMETERS config_dfs_path='voltage.conf',
                                 username='alice', identity='alice@example.com',
                                ) OVER ();
ERROR 3457:  Function VoltageSecureConfigure() does not exist, or permission
is denied for VoltageSecureConfigure()
HINT:  No function matches the given name and argument types. You may need to
add explicit type casts

请注意，Alice 虽然无法访问 VoltageSecureConfigure 函数，但可以使用全局配置文件。