Changing the Owner of a View: Quick Tip

Jim Knicely authored this tip. We can change the owner of a database view with the ALTER VIEW … OWNER TO command. Example: dbadmin=> CREATE TABLE test (c1 INT); CREATE TABLE dbadmin=> INSERT INTO test SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> CREATE VIEW test_vw AS SELECT c1 FROM test; CREATE VIEW dbadmin=> SELECT table_schema, table_name, owner_name dbadmin-> FROM views dbadmin-> WHERE table_name = ‘test_vw’; table_schema | table_name | owner_name ————–+————+———— public | test_vw | dbadmin (1 row) dbadmin=> GRANT SELECT ON test_vw TO test1; GRANT PRIVILEGE dbadmin=> \c – test1 You are now connected as user “test1”. dbadmin=> select * from test_vw; c1 —- 1 (1 row) dbadmin=> \c – dbadmin You are now connected as user “dbadmin”. dbadmin=> ALTER VIEW test_vw OWNER TO test1; ALTER VIEW dbadmin=> SELECT table_schema, table_name, owner_name dbadmin-> FROM views dbadmin-> WHERE table_name = ‘test_vw’; table_schema | table_name | owner_name ————–+————+———— public | test_vw | test1 (1 row) dbadmin=> \c – test1 You are now connected as user “test1”. dbadmin=> SELECT * FROM test_vw; ERROR 4367: Permission denied for relation test_vw Wait! The TEST1 user now owns the view TEST_VW but can’t read from it?!?! To overcome this issue, I’ll need to grant the SELECT privilege on the underlying table, which is owned by DBADMIN, to the new view owner! dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> GRANT SELECT ON test TO test1; GRANT PRIVILEGE dbadmin=> \c – test1 You are now connected as user “test1”. dbadmin=> SELECT * FROM test_vw; c1 —- 1 (1 row) dbadmin=> SELECT grantor, privileges_description, object_schema, object_name, object_type dbadmin-> FROM grants WHERE grantee = ‘test1’; grantor | privileges_description | object_schema | object_name | object_type ———+————————+—————+————-+————– dbadmin | USAGE | | general | RESOURCEPOOL test1 | SELECT | public | test_vw | VIEW dbadmin | SELECT | public | test | TABLE (3 rows) Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/ALTERVIEW.htm Have fun!