How to do granular security ( row & column level) in vertica

How to do granular security ( row & column level) in Vertica like oracle supports VPD policy.

Need to have solution defined in the database instead of doing in the front end. Since I have global support and each region can have different access and if add security table base on user access I see that query is visible with extra filter or join I am doing. Need to apply this security such way that users can't see filter or join I am applying the vertica database.

Comments

  •  I don't think there is such a task can be done in Vertica, since Oracle VPD includes triggering events and triggers are not available in Vertica. 
     Vertica is a columnar database and Oracle VPD enforces row level security.
    What you can do is create multiple projections that will serve for each of your users attending your security/VPD needs.
    See example :
    1-create user 
    2-create specific projection
    3-create view to point to that projection
    4-grant select on that view to the user

    - the create view is needed as no select grant can be done over a projection. 
  • I dont think you can create a view to point to a specific projection, as views only refer to the tables and rely on the database to select the best projection for a given query.
  • Create table :
     
    dbadmin=> create table aaa(id int, name varchar(10));CREATE TABLE
    dbadmin=> insert into aaa values (1,'Adrian');
     OUTPUT
    --------
          1
    (1 row)
    dbadmin=> insert into aaa values (2,'Ben');
     OUTPUT
    --------
          1
    (1 row)
    dbadmin=> insert into aaa values (3,'Ten');
     OUTPUT
    --------
          1
    (1 row)
    Create projection:
    dbadmin=> create projection only_this as select name from aaa limit 1;WARNING 4468:  Projection <public.only_this> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
              The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
    CREATE PROJECTION

    Create view on top of projection:
    dbadmin=> create view view_only_this as select * from only_this;
    CREATE VIEW
    Grant access to the view :

    dbadmin=> grant select on view_only_this to test1; GRANT PRIVILEGE
    dbadmin=> \q


    Login with test1 user and check what was done:
    [dbadmin@bih001 v_test_node0001_catalog]$ vsql -Utest1
    Password:
    test1=> \dv
                    List of views
     Schema |      Name      |  Owner  | Comment
    --------+----------------+---------+---------
     public | view_only_this | dbadmin |
    (1 row)
    test1=> select * from view_only_this;
      name
    --------
     Adrian
    (1 row)
    test1=> select * from GRANTS;
    -[ RECORD 1 ]----------+------------------
    grant_id               | 45035996273970890
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | USAGE
    object_schema          |
    object_name            | general
    object_id              | 45035996273718910
    object_type            | RESOURCEPOOL
    grantee_id             | 45035996273970888
    grantee                | test1
    -[ RECORD 2 ]----------+------------------
    grant_id               | 45035996274062626
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | SELECT
    object_schema          | public
    object_name            | view_only_this
    object_id              | 45035996274062622
    object_type            | VIEW
    grantee_id             | 45035996273970888
    grantee                | test1
       
    explain select * from view_only_this ;
     Access Path:
     +-STORAGE ACCESS for only_this [Cost: 5, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
     |  Projection: public.only_this
     |  Materialize: only_this.name





    I am not a fun of the approach - but it does the trick
  • Thanks, Adrain !

    Now one more question is it possible to have project access set on user and this user can be stored in the DB so that while retrieve data I can decide which projection need to pull dynamically ?

    So base on location ( global or domain ) base access user or group privilege will be set and created different  projects as you mentioned and from my front end it pass group or user it will get only projections he can access.
  •  Why not just creating roles for your global vs domain users and grant access to those view to your roles !
  • Thanks for quick response Adrian!

    Since different region has different countries and need setup each group is assigned to countries level access, so when users sign in from the front end security defined in table ( since lot of users) easier to lookup the table access and find out access level and use particular projection.
  •  Hi Vir, 
      It might work but this will be very hard to implement, even with a (projection > view > access).
      I didn't use any of the stuff i written on a project or any production db so far, but i know it actually work. 
     
      #Granular access to the data is not a think that Vertica was build for, they might improve this in the future for sure but with some cost(performance wise, as new objects will be required).
      
  • Why create an additional projection?  A view on the base table is sufficient to provide the access policy you want.  Adding an additional projection requires you to store an additional 2 copies (ksafety) of the table data.
  • I am part of HR Systems, basically HR data should be more secured and let me know whether Vertica support below mentioned security model. I am bit confused to choose Vertica for my DW project.

     

    • Data encryption
    • Data Masking
    • Row & Column level security model
  • Hi arunkumardct,


    Row & Column level security model can be done using ACCESS_POLICY.

     

    Note the following

    Only DBADMIN users can create access policies.
    To give a specific user or group of users a unique view of a table
    (for example, only let users see rows with their name), you can create an access policy that is based on a role. Then you assign that role to a user.

  • Use something like database firewall for vertica?
    https://www.datasunrise.com/firewall/vertica/

    define the security policies based on specific columns or rows. from outside .
    Define the access based fine granular such as region or ip or application

Leave a Comment

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