Grant access for a frquently dropped and created table

Moderator: NorbertKrupa

Post Reply
rajaskhar
Newbie
Newbie
Posts: 2
Joined: Wed Oct 07, 2015 4:42 pm

Grant access for a frquently dropped and created table

Post by rajaskhar » Wed Oct 07, 2015 4:54 pm

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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Grant access for a frquently dropped and created table

Post by NorbertKrupa » Wed Oct 07, 2015 4:56 pm

Have you tried anything? Like automating it?
Checkout vertica.tips for more Vertica resources.

rajaskhar
Newbie
Newbie
Posts: 2
Joined: Wed Oct 07, 2015 4:42 pm

Re: Grant access for a frquently dropped and created table

Post by rajaskhar » Wed Oct 07, 2015 7:14 pm

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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Grant access for a frquently dropped and created table

Post by JimKnicely » Wed Oct 07, 2015 9:10 pm

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.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Grant access for a frquently dropped and created table

Post by nnani » Mon Dec 28, 2015 5:42 am

Hi,
upgrade to and all your worries will be gone !!! :)

Inherited Privileges
https://my.vertica.com/docs/7.2.x/HTML/ ... n%7C_____3
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “New to Vertica”