grant access at a schema level

Ram_Somasundara

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;

Navin_C

#Ram,

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.

OR

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.

Ram_Somasundara

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

thanks

Navin_C

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.

@dbadmin

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

Vladimír_Vacula

Hi,
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.
Thanks
Vlada

Prasanta_Pal

This is a known feature request, check-it out in future releases, no ETA now.