Row based access policy

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'.

Comments

  • sKwasKwa Registered User

    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_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited February 13

    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

  • ersaurabhexersaurabhex Registered User

    Excellent!! Thanks for the nice explanation!!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file