Vertica 9 and Security/User Management - show partial data to users?

ScottL

On Youtube there is a Micro Focus Learning Channel with several videos including one for a Vertica Essentials 9 x Class. At 2:25 in the video it is implied that there is functionality to show all, some or no data columns depending on security/user management functions. I tried to find this in the 9.0 documentation and had no luck. Is anyone aware of this functionality and how it is implemented? thank you!

https://youtu.be/m3EtRBu7M1U?t=149

Jim_Knicely

I believe that you are inquiring about "Column/Row Access Policies".

Here is a link to the section in the online doc:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/UsersPrivileges/USINGACCESSPOLICY.htm

Jim_Knicely

Fyi ... Here is a simple example:

As the Database admin user in our vsql CLI:

dbadmin=> create role hr_analyst;
CREATE ROLE

dbadmin=> create user judy;
CREATE USER

dbadmin=> grant hr_analyst to judy;
GRANT ROLE

dbadmin=> alter user judy default role hr_analyst;
ALTER USER

dbadmin=> select * from ssn_test;
 name |    ssn
------+-----------
 jane | 444556666
 jim  | 111223333
(2 rows)

dbadmin=> create access policy on ssn_test for column ssn
dbadmin-> case
dbadmin-> when enabled_role('hr_analyst') then right(ssn, 4)
dbadmin-> else ssn
dbadmin-> end
dbadmin-> enable;
CREATE ACCESS POLICY

Now connect as the user Judy:

dbadmin=> \c sfdc judy
You are now connected to database "sfdc" as user "judy".

sfdc=> select * from ssn_test;
 name | ssn
------+------
 jane | 6666
 jim  | 3333
(2 rows)

The user Judy can only see the last 4 digits if he SSN!

ScottL

Thank you, Jim! I appreciate the quick and detailed feedback!