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

vir

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.

Adrian_Oprea_1

 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. 

Sajan_Sangraula

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.

Adrian_Oprea_1

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

vir

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.

Adrian_Oprea_1

 Why not just creating roles for your global vs domain users and grant access to those view to your roles !

vir

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.

Adrian_Oprea_1

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

Ben_Vandiver

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.

arunkumardct

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

darkparticle

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.

databig

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