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?



  • Options


    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

  • Options

    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' ?

  • Options



    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!!






  • Options

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

    That way you retain the grants. 

  • Options

    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.

  • Options

    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