Granting Users Select Permission in Vertica

At work we use Vertica for one of our databases. As part of the data team, it is our job to help monitor and maintain the data in the database, as well as the health of the database as well. One of the things I have been doing is to create database users for the different services that are using the database.

This helps us when we have long running queries, high load on the database, issues with the number of connections, and it would also allow us to add constraints around all of those in the future on a per service basis based off of the different user accounts, not to mention restrict the permissions to tables that are needed and allow some of the experimental tables to exist without causing confusion by having people see those tables before they have been stabilized.

First we create a user for each service with a username and a password; the quotes around the username signify to Vertica that the username is case sensitive.

CREATE USER "UserName"  IDENTIFIED BY 'password';

We then grant SELECT only permissions to the tables that are specific to that user. We do SELECT only permissions as this is a data warehouse style database, and only a few select services have permissions to update the database, while everyone else should be free to read from the tables they need, or are stable.

grant select on <tablename> to "UserName";

We also have some helper functions that we give all of the users, and as I still usually can’t remember the functions and their argument types, I have to do a lookup of the different functions each time, because when granting permissions on Vertica functions, you need to specify the function name along with the arguments.

This query finds me all of the user defined functions in the database.

select * from user_functions;

Once I have the functions and arguments, I can then grant EXECUTE permissions on the functions, by providing the function name and the argument types.

grant execute on function <function name with argument types> to "UserName";

So to grant execute permissions to the function foo that has a number of overloads, it might look like.

grant execute on function foo(Float, Float) to "UserName";
grant execute on function foo(Float, Integer) to "UserName";
grant execute on function foo(Float, Numeric) to "UserName";
grant execute on function foo(Integer, Integer) to "UserName";
grant execute on function foo(Integer, Numeric) to "UserName";
grant execute on function foo(Numeric, Integer) to "UserName";
grant execute on function foo(Numeric, Numeric) to "UserName";

I hope this helps someone the next time they find themselves having to help play db admin/owner on a Vertica database.


Leave a Reply

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