Include the ALTER and DROP privileges in a GRANT ALL Statement
Jim_Knicely
- Select Field - Administrator
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!
0