Row based access policy

ersaurabhex

Overview
1. We have a BI tool, with many named users.
2. Users log in using their email id and password and view different reports/dashboards.
3. Reports/dashboards fetch data from Vertica database using generic user 'dw_readonly' and password (In a prebuilt connection of BI Tool).
4. I would like to implement a row level security so that user can access only specific client's data which is maintained in another table within two columns -> business_user_id integer, client_id integer.
5. Business users and clients have many to many relationship.

Problem
Dynamic implementation of row level security, without creating as many roles or users because BI Tool is using the prebuilt connection with user 'dw_readonly'.

sKwa

Hi!

Can BI Tool do a complex logic while connecting to Vertica?

For example:

• If user = 'John Dow' then set connection label = 'foo'
• connect to Vertica with connection label = 'foo'

In any case, somehow Vertica should determine users to apply policy and it should be reflected in connection params. If each connection uses in same params so Im afraid it's not possible.

Jim_Knicely

Per @sKwa's suggestion, below is a quick example:

Here is the table I want to limit row access:

dbadmin=> SELECT * FROM test;
 c1 |          c2
----+-----------------------
  1 | Jim can see this!
  2 | Jim can see this!
  3 | Jim can NOT see this!
  4 | Jim can NOT see this!
  5 | Jim can NOT see this!
(5 rows)

Here is a lookup table that lists the users and the rows they can see:

dbadmin=> SELECT * FROM test_policy;
 client_label | test_c1
--------------+---------
 jim          |       1
 jim          |       2
(2 rows)

Here is an access policy:

dbadmin=> CREATE ACCESS POLICY on test for rows
dbadmin-> WHERE
dbadmin->   c1 IN (SELECT tp.test_c1
dbadmin(>            FROM test_policy tp
dbadmin(>            JOIN sessions s on s.session_id = current_session()
dbadmin(>           WHERE tp.client_label = s.client_label)
dbadmin-> ENABLE;
CREATE ACCESS POLICY

Now I cannot read data from the test table:

dbadmin=> SELECT * FROM test;
 c1 | c2
----+----
(0 rows)

Until my BI tool (or vsql in this example) sets the session label:

dbadmin=> SELECT set_client_label('jim');
    set_client_label
-------------------------
 client_label set to jim
(1 row)

dbadmin=> SELECT * FROM test;
 c1 |        c2
----+-------------------
  1 | Jim can see this!
  2 | Jim can see this!
(2 rows)

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/ConnectionManagement/SET_CLIENT_LABEL.htm

Note, Tableau can run the Vertica SET_CLIENT_LABEL function via the " Run Initial SQL" option:

See:
http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.html#connect_basic_initialsql.html

ersaurabhex

Excellent!! Thanks for the nice explanation!!