The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Include the ALTER and DROP privileges in a GRANT ALL Statement

Jim_KnicelyJim_Knicely Administrator
edited May 20 in Tips from the Team

When you issue a GRANT ALL statement on a database object such as a table, the ALTER and DROP privileges are excluded. Staring in Vertica 9.2.1 you can include the ALTER and DROP privileges as part of a GRANT ALL statement by specifying the EXTEND clause.

Example:

dbadmin=> SELECT user;
 current_user
--------------
 dbadmin
(1 row)

dbadmin=> CREATE USER lucy;
CREATE USER

dbadmin=> CREATE TABLE test_table (c1 INT);
CREATE TABLE

dbadmin=> INSERT INTO test_table SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> GRANT ALL ON TABLE test_table TO lucy;
GRANT PRIVILEGE

dbadmin=> \c - lucy
You are now connected as user "lucy".

dbadmin=> SELECT * FROM test_table;
 c1
----
  1
(1 row)

dbadmin=> ALTER TABLE test_table ADD COLUMN c2 INT;
ROLLBACK 8958:  Insufficient privileges on test_table, alter privileges needed

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> GRANT ALL EXTEND ON TABLE test_table TO lucy;
GRANT PRIVILEGE

dbadmin=> \c - lucy
You are now connected as user "lucy".

dbadmin=> ALTER TABLE test_table ADD COLUMN c2 INT;
ALTER TABLE

dbadmin=> \d test_table;
                                  List of Fields by Tables
 Schema |   Table    | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------+--------+------+------+---------+----------+-------------+-------------
 public | test_table | c1     | int  |    8 |         | f        | f           |
 public | test_table | c2     | int  |    8 |         | f        | f           |
(2 rows)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTTable.htm

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.