Granting users access to the Voltage SecureData integration functions

By default, Vertica users do not have access to the Voltage SecureData Integration Functions. Users attempting to call the integration functions without the correct privileges will receive the following error:

=> 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)]

Users must have EXECUTE access to the integration functions in order to use them. These functions are part of the PUBLIC schema. The functions in the Voltage SecureData integration library are:

The following example demonstrates granting the user named Alice access to the VoltageSecureAccess function to be able to decrypt data.

=> \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)

See GRANT (user defined extension) for a detailed explanation of granting access to UDxs to users.

Creating roles for SecureData users

Instead of granting access to each function to individual users, you can create roles that you grant access to the functions. Then you can grant users who need to access the SecureData functions access to these roles.

Consider creating at least two roles: one for access to the VoltageSecureConfigure function and another for access to the other functions. In most cases, not all users need to access VoltageSecureConfigure, especially if you choose to create a single, global configuration file. See Configuring access to SecureData for more information about using VoltageSecureConfigure.

The following example:

• Creates two roles: secure_data_users, who are granted access to the protect and access functions, and secure_data_admins, who are granted access to the SecureDataConfigure function.

• Grants the secure_data_user role to a user named Alice

• Sets the new role as her default role.

• Switches to Alice

• Calls several of the SecureData functions.

=> \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

Note that Alice can use the global configuration file, despite not being able to access the VoltageSecureConfigure function.