Creating Column Access Policies

CREATE ACCESS POLICY can create access policies on individual table columns, one policy per column. Each column access policy lets you specify for different users and roles various levels of access to the data of that column. The column access expression can also specify how to render column data for users and roles.

For example, you can create an access policy on column customer_address in table client_dimension. This access policy gives non-superusers with the administrator role full access to all data in that column, but masks customer address data from all other users:

=> 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 uses this policy to determine the access it gives to users MaxineT and MikeL, who are assigned employee and administrator roles, respectively. When these users query the table customer_dimension, Vertica applies the column access policy expression as follows:

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

Restrictions

The following limitations apply to access policies:

• A column can have only one access policy.
• Row access policies are invalid on the following tables:
  • Temporary tables
  • Tables with aggregate projections
• Do not set column access policies on flex tables. If you do so, Vertica returns a warning that the policy might not be secure.
  
• Access policy expressions cannot contain:
  • Subqueries
  • Aggregate functions
  • Analytic functions
  • User-defined transform functions (UDTF)
• If the Vertica optimizer cannot replace a deterministic expresson that involves only contants with their computed values, it blocks all DML operations such as INSERT.