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

ScottLScottL Employee, Registered User

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!

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    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

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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!

  • ScottLScottL Employee, Registered User

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

Leave a Comment

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