Dynamic Row and Column Access Policies

This blog post was authored by Serge Bonte. Vertica’s row and column access policies can be used to provide extra security on data in your tables. These policies are well covered in Best Practices for Creating Access Policies in Vertica and Dynamic Row and Column Access Policies. In this blog, we will explore how dynamic row and column access policies can be used to solve a multi-tenancy and privacy requirement one of our customers had. Let’s consider the following table: => CREATE TABLE invoices ( vendor_id INTEGER NOT NULL, customer_name VARCHAR(30), amount MONEY(5,2) ) ; The access policies requirements are that each vendor can only see its own invoices and that only managers can see customer names.

Dynamic Row Level Access Policy for multi-tenancy

One solution to meet these requirements is to create a policy rule for each vendor. But, it would require changing the policy with each vendor change; also a large number of vendors would drag down performance. If the vendor_id is part of a Vendor’s role name (e.g. vendor_1012), a simpler dynamic policy would be written as simply as: CREATE ACCESS POLICY ON public.invoices FOR ROWS WHERE enabled_role('dbadmin') OR ( 'vendor_' || vendor_id = ( SELECT name FROM roles WHERE enabled_role(name) and name like 'vendor_%' ) ) ENABLE; Let’s then define a role for each vendor – e.g. vendor_1012 and vendor_1013- and grant them to users for each vendor –e.g. user_1012 and user_1013. Each vendor user sees only the rows for its vendor id: user_1012=> select * from invoices; vendor_id | customer_name | amount -----------+---------------+-------- 1012 | DAN JONES | 602.78 1012 | JOHN DOE | 134.56 1012 | JANE DOE | 567.33 1012 | JOHN DOE | 127.22 (4 rows);

Column Level Access Policy for privacy

Column level access can easily be implemented by defining a cross-vendor managerial role –e.g. is_manager – and granting it all vendor managers. A vendor manager would be assigned two roles is_manager and vendor_. CREATE ACCESS POLICY ON public.invoices FOR COLUMN customer_name CASE WHEN enabled_role('dbadmin') OR ENABLED_role('is_manager') THEN customer_name ELSE 'XXXXXXX' END ENABLE; Once the column level access policy is enabled, a vendor user who has only the vendor_ role, would see only invoices for that vendor_id and masked values for the customer_name column: user_1012=> select * from invoices; vendor_id | customer_name | amount -----------+---------------+-------- 1012 | XXXXXXX | 602.78 1012 | XXXXXXX | 134.56 1012 | XXXXXXX | 567.33 1012 | XXXXXXX | 127.22 (4 rows) Likewise, a vendor manager who has both the vendor_ and is_manager role, would see only invoices for that vendor_id with unmasked values for the customer_name column: manager_1012=> select * from invoices; vendor_id | customer_name | amount -----------+---------------+-------- 1012 | DAN JONES | 602.78 1012 | JOHN DOE | 134.56 1012 | JANE DOE | 567.33 1012 | JOHN DOE | 127.22 (4 rows)

Grouping with column level access policy

Vendor Managers (with the is_manager role) can perform grouping on the customer_name column. For instance, to find the total invoiced amount per customer: manager_1012=> select sum(amount) total_amount, customer_name from invoices group by customer_name order by total_amount desc; total_amount | customer_name --------------+--------------- 602.78 | DAN JONES 567.33 | JANE DOE 261.78 | JOHN DOE (3 rows) Because the customer_name column values are masked (XXXXX), vendor users (without the is_manager role) would be grouping on one value (XXXX): user_1012=> select sum(amount) total_amount, customer_name from invoices group by customer_name order by total_amount desc; total_amount | customer_name --------------+--------------- 1431.89 | XXXXXXX (1 row) One solution is to change the column level access policy to assign a unique hash for each customer instead of masking it (XXXX) CREATE ACCESS POLICY ON public.invoices FOR COLUMN customer_name CASE WHEN enabled_role('dbadmin') OR ENABLED_role('is_manager') THEN customer_name ELSE SHA512(customer_name) END ENABLE; The values for the customer_name column are still obfuscated: user_1012=> select * from invoices; vendor_id | customer_name | amount -----------+--------------------------------+-------- 1012 | d49ac472a4a042c5e136edd97ba75d | 602.78 1012 | 3d0e47400578c79eca255b890c1a48 | 134.56 1012 | 94614719f176cd8e732c1ee5747c23 | 567.33 1012 | 3d0e47400578c79eca255b890c1a48 | 127.22 (4 rows) However, the obfuscated values are unique for each customer name, allowing some grouping on the customer_name column: user_1012=> select sum(amount) total_amount, customer_name from invoices group by customer_name order by total_amount desc; total_amount | customer_name --------------+-------------------------------- 602.78 | d49ac472a4a042c5e136edd97ba75d 567.33 | 94614719f176cd8e732c1ee5747c23 261.78 | 3d0e47400578c79eca255b890c1a48 (3 rows)