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

Permissions / Grants Changed for User For New Table

Hi - 



I have a nightly job that drops a table, and reloads it. I am having trouble because I then need to run 'grants' for this table to the appropriate users all over again.


Is there a way in tableau to grant select to a user on all tables in a schema, now and in the future?




    Hi ,


    You should use database roles and associate them with your users.


    Also make sure you enable inheritance on the schemas you work on.(is like db roles/reader/writer in SQL Server).


    -there is a post i wrote about how you can create schemas on the fly with all the underlyng stuff you needs for inheritance and role creation.


    Vertica schema on the fly + inheritance

  • when trying to run:

    alter schema public default include privileges;

    I get the error: ROLLBACK 4953:  The name "public" is a reserved name


    What is the right way to set the inherited privilege at the schema level for 'public' ?

  • Hi 


    What version are you running your cluster in? You can try below command which includes database name with the schema name (database name in my case is test) in order to avoid this reserve word issue


    dbadmin=> alter schema "test.public" default include schema privileges;


    Hope this helps!!






  • You can't just truncate it instead of dropping it?

    That way you retain the grants. 

  • We could, but the way we sync tables between MYSQL and Vertica is at the whole table level, so if schema changes in mysql, its much easier to default rebuild the entire Vertica table than somehow have to propogate an alter table statement.

  • How do you deal with projections? 


    Check out external flex tables. I am assuming you don't have a lot of data. 


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.