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;

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.