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.
How to do granular security ( row & column level) in vertica
+1I 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.
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.
+1I 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.
+1Create table :
Create view on top of projection:
Login with test1 user and check what was done:
I am not a fun of the approach - but it does the trick
dbadmin=> create table aaa(id int, name varchar(10));CREATE TABLECreate projection:
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)
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;Grant access to the view :
CREATE 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.
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.
+1Why 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.
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.
+1Hi 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).
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
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
