Page 1 of 1

Grant access for a frquently dropped and created table

Posted: Wed Oct 07, 2015 4:54 pm
by rajaskhar
Hi,

I am very new to Vertica. As per requirement i have to grant access to a set of users for a table that is frequently dropped and created again with same name. It's hectic to grant users permission all the time. So i wish to know if there is any way to Grant access to users when the table is created. It would be very helpful if somebody can prvide some syntax or example. Thanks.

Re: Grant access for a frquently dropped and created table

Posted: Wed Oct 07, 2015 4:56 pm
by NorbertKrupa
Have you tried anything? Like automating it?

Re: Grant access for a frquently dropped and created table

Posted: Wed Oct 07, 2015 7:14 pm
by rajaskhar
I am able to automate till giving grant rights to users. So what i am trying to find out is the view on the table given to specific end-users is also revoked when the table is dropped. Is there a way to retain view for those users?

Re: Grant access for a frquently dropped and created table

Posted: Wed Oct 07, 2015 9:10 pm
by JimKnicely
Maybe grab the privileges from the GRANTS table, generate the DDL necessary to re-grant them and store them in a temp table?

Example:

Code: Select all

dbadmin=> \d bob_bill;
                                 List of Fields by Tables
 Schema |  Table   | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------+--------+------+------+---------+----------+-------------+-------------
 public | bob_bill | a      | int  |    8 |         | f        | f           |
(1 row)

dbadmin=> create table grant_temp as select 'grant ' || privileges_description || ' on ' || object_schema || '.' || object_name || ' to ' || grantee || ';' from grants join users on user_name = grantee where object_name = 'bob_bill' and not is_super_user;
CREATE TABLE

dbadmin=> select * from grant_temp;
                             ?column?
------------------------------------------------------------------
 grant INSERT, SELECT, UPDATE, DELETE on public.bob_bill to bob;
 grant INSERT, SELECT, UPDATE, DELETE on public.bob_bill to bill;
(2 rows)
Then drop the table, re-create it, then execute the DDL grant statements from the temp table?

Code: Select all

dbadmin=> drop table bob_bill;
DROP TABLE

dbadmin=> create table bob_bill (a varchar(100));
CREATE TABLE

dbadmin=> \d bob_bill;
                                     List of Fields by Tables
 Schema |  Table   | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------+--------+--------------+------+---------+----------+-------------+-------------
 public | bob_bill | a      | varchar(100) |  100 |         | f        | f           |
(1 row)

dbadmin=> \! /opt/vertica/bin/vsql -Atc "select * from grant_temp;" | /opt/vertica/bin/vsql
GRANT PRIVILEGE
GRANT PRIVILEGE

dbadmin=> drop table grant_temp;
DROP TABLE
Something like that might work for you.

Re: Grant access for a frquently dropped and created table

Posted: Mon Dec 28, 2015 5:42 am
by nnani
Hi,
upgrade to and all your worries will be gone !!! :)

Inherited Privileges
https://my.vertica.com/docs/7.2.x/HTML/ ... n%7C_____3