Dynamic Row and Column Access Policies

The content of this blog post is based on an article authored by Maurizio Felici.

The Vertica Analytic Database access policies act on columns and rows to provide extra security on data in your tables. You can create flexible access policies that limit which users can access certain data by applying the access policy to a table. To enforce the access policies, Vertica automatically rewrites, or transforms, user queries. See Best Practices for Creating Access Policies on Vertica for information on transforming queries.

To implement access policies, first create a table: CREATE TABLE public.employees ( empid INTEGER NOT NULL, depid INTEGER NOT NULL, salary NUMERIC(7,2) NOT NULL, rate INTEGER ) ; In this table you want to implement the following restrictions:
• Database users with role “manager_top” can access all data
• Database users with role “manager_100” can access only data where depid = 100
• Database users with role “manager_200” can access only data where depid = 200

You can quickly accomplish this in Vertica by creating an access policy, in this case a row access policy: CREATE ACCESS POLICY ON public.employees FOR ROWS WHERE enabled_role('manager_top') OR ( enabled_role('manager_100') and depid = 100 ) OR ( enabled_role('manager_200') and depid = 200 ) ENABLE ; That’s all. From now on users with the “manager_100” or “manager_200” roles can only access their department’s data. Users with “manager_top” role can access everything.

However, this access policy is static: every time you introduce a new “manager_XYZ” role you must update the policy to include that new role. Also, the evaluation of the WHERE clause may slow down query execution if you have numerous OR branches.

To work around these issues you can define a rule to dynamically allow users with the “manager_XYZ” role to access data having depid=XYZ. To do so, use function/subqueries to correlate user/roles to column/row data, for example: CREATE ACCESS POLICY ON public.employees FOR ROWS WHERE enabled_role('manager_top') OR ( 'manager_' || depid = ( SELECT name FROM roles WHERE enabled_role(name)) ) ENABLE ; Note that the above example will not work if you have more than one manager role assigned to you. If you have roles manager_100 and manager_200, this access policy returns an error.

In this case, edit the access policy as follows: CREATE ACCESS POLICY ON public.employees FOR ROWS WHERE enabled_role('manager_top') OR ( 'manager_' || depid = ( SELECT name FROM roles WHERE enabled_role(name) and name like ‘manager_%’ ) ) ENABLE ; You can use row and column access policies on the same table. For example, you want only database users “user_XYZ” (or “manager_top” role) to access the SALARY column for users with empid=XYZ. You can easily add this new security policy as follows: CREATE ACCESS POLICY ON public.employees FOR COLUMN salary CASE WHEN enabled_role('manager_top') OR current_user() = 'user_' || empid THEN salary ELSE null END ENABLE ; For more information on Vertica access policies, see the Vertica documentation.