Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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!


  • Jim_KnicelyJim_Knicely Administrator
    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:

  • Jim_KnicelyJim_Knicely Administrator

    Fyi ... Here is a simple example:

    As the Database admin user in our vsql CLI:

    dbadmin=> create role hr_analyst;
    dbadmin=> create user judy;
    dbadmin=> grant hr_analyst to judy;
    dbadmin=> alter user judy default role hr_analyst;
    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;

    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!

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.