Comments

• Jim_Knicely - Select Field - Administrator

  December 2017 edited December 2017

  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

  0
• Jim_Knicely - Select Field - Administrator

  December 2017

  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!

  1
• ScottL Employee

  December 2017

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

  0