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.
Grant access for a frquently dropped and created table
Moderator: NorbertKrupa
-
- 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
Have you tried anything? Like automating it?
Checkout vertica.tips for more Vertica resources.
Re: Grant access for a frquently dropped and created table
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?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Grant access for a frquently dropped and created table
Maybe grab the privileges from the GRANTS table, generate the DDL necessary to re-grant them and store them in a temp table?
Example:
Then drop the table, re-create it, then execute the DDL grant statements from the temp table?
Something like that might work for you.
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)
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Grant access for a frquently dropped and created table
Hi,
upgrade to and all your worries will be gone !!!
Inherited Privileges
https://my.vertica.com/docs/7.2.x/HTML/ ... n%7C_____3
upgrade to and all your worries will be gone !!!
Inherited Privileges
https://my.vertica.com/docs/7.2.x/HTML/ ... n%7C_____3