Contact us

Madeira Data Solutions

Your Data, Our Solutions

How to Create and Manage Security Roles in Vertica

Written By: Chen Shaulian 15/12/2015

If you have many users in Vertica, managing their permissions can be a hassle.

If you’d like to grant access privileges on some tables to a certain user, you can collect all privileges on several tables and grant the user access to a role (instead of granting access to each table directly).

You can also add other users to the role, and they will get the privileges that have been granted to the role.

By grouping your users to roles, you can reduce the amount of overhead you have when managing user permissions. Instead of granting or altering permissions on a user basis, you can do that on a role basis, which will be affected to all of the users that are included in the role.

One last thing to take into account: Roles just give additional privileges to the user, but don’t cancel other permissions that were granted to the user outside of the role. For instance, a user can have grant permissions on a table that is not in the role, or a revoke from a table that in the role.

And now for the scripts:

  • Create Role

  • Grant SELECT on some tables to the Role

  • Grant permission on ALL tables to a role

  • Add a user to the role

  • Add a default role to a user

  • Remove user from a role

 

 

After you’ve create the role and added users to it, you can check which tables and which users are related to the role:

 

  • Select which tables are in the role

  • Select which Users are in the role

Leave a Reply

Your email address will not be published. Required fields are marked *