How to provide Vertica user with read-only access to certain specified system tables?

Question

We're looking to set up a user in our Vertica database that can see certain system tables, (projections, projection_storage and views), but we don't want this user to be a dbadmin, because we don't want them to have write privileges on these tables. I've tried using GRANT statements to give a regular user access to these tables, but that doesn't seem to work. Each user can only see their own own records in those tables. Is there a way to set up a user as I describe, or do we need to have this user be a dbadmin?

Our use case is that we need a user that can get a list of the schemas that exist in our database and iterate through each schema, gathering information to store in one central location. If our user is granted usage on the individual schemas, then they can get a list of those schemas, but they aren't able to access the necessary records in the projection_storage and views tables.

Thank you!

Answer 1

Granting USAGE on the schema to the user or role is not enough for users to see its projections in projection_storage table. If the user or the role has SELECT access on the table, then projections for those tables can be viewed in projection_storage. I am in Vertica 7.1, and I was able to view projection records by granting SELECT permission just to the role instead of granting to individual user ID.

If the user does not need to access tables but needs to list out tables in the schema for some reporting purpose, one option would be to periodically dump the content of projection_storage to a different table and grant proper privileges on this table to the user.

Answer 2

Just for the sake of maintaince you should create database roles !! and then give acces to those roles to your users . Other-wise the maintainance will be hell to you !!

Answer 3

Normally, I just give a user USAGE on a schema. And then "GRANT SELECT on to ;"

Do they have INSERT permissions on those tables?