grant access at a schema level

Anyone know when or what Vertica release would start supporting various grants at a schema level? Currently, the only way to do this is at an object level, and is very irritating to say the least. Something like this, grant select,insert,update,delete on schema.objectname to username;


  • Options
    Navin_CNavin_C Vertica Customer

    At present the only privileges which can used while assigning schema grants are
    1. CREATE
    2. USAGE

    See if you can use this to categorize among grant select, insert, update, delete.


    As a workaround, you create a bash script for granting privileges to user and roles at once in bulk.
    I can help you with this.

    Hope this helps.
  • Options
    So, is there an upcoming release that would re-mediate this shortcoming. I understand the workaround, but would like a fix to this though.

  • Options
    Navin_CNavin_C Vertica Customer
    Hi Ram,

    An update on the above answer.

    From Version 6.1, we can grant privileges on all objects of a schema using a single SQL Statement for every object type.

    create user user1;  grant all privileges on all tables in schema schema_name to user1;
    grant all privileges on all functions in schema schema_name to user1;
    grant all privileges on all sequences in schema schema_name to user1;
    select * from grants where grantee = 'user1'  
    Hope this helps. NC
  • Options
    is there a way how to NOT repeat the grant every time someone else creates new table ?
    So when user2 creates new table within the same schema, user1 will have access to the new table without running "grant all on all tables in schema" again.
  • Options
    Prasanta_PalPrasanta_Pal - Select Field - Employee
    This is a known feature request, check-it out in future releases, no ETA now.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file