Include the ALTER and DROP Privileges in a GRANT ALL Statement

Posted May 20, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Helpful Tips in blue text with magnifying glass
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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTTable.htm

Have fun!