Include the ALTER and DROP privileges in a GRANT ALL Statement

Jim_Knicely

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!