Creating row access policies

CREATE ACCESS POLICY can create a single row access policy for a given table.

CREATE ACCESS POLICY can create a single row access policy for a given table. This policy lets you specify for different users and roles various levels of access to table row data. When a user launches a query, Vertica evaluates the access policy's WHERE expression against all table rows. The query returns with only those rows where the expression evaluates to true for the current user or role.

For example, you might want to specify different levels of access to table store.store_store_sales for four roles:

employee: Users with this role should only access sales records that identify them as the employee, in column employee_key. The following query shows how many sales records (in store.store_sales_fact) are associated with each user (in 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: Users with this role (public.emp_dimension) should only access sales records for the sales region that they manage (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 and administrator: Users with these roles have unlimited access to all table data.

Given these users and the data associated with them, you can create a row access policy on store.store_store_sales that looks like this:

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;

Important

In this example, the row policy limits access to a set of roles that are explicitly included in policy's WHERE expression. All other roles and users are implicitly denied access to the table data.

The following examples indicate the different levels of access that are available to users with the specified roles:

dbadmin has access to all rows in store.store_sales_fact:

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

User LilyCP has the role of regional_manager, so she can access all sales data of the Midwest region that she manages:


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

User SamRJ has the role of employee, so he can access only the sales data that he is associated with:


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

Restrictions

The following limitations apply to row access policies:

A table can have only one row access policy.
Row access policies are invalid on the following tables:
- Tables with aggregate projections
- Temporary tables
- System tables
- Views
You cannot create directed queries on a table with a row access policy.