Exanple:
dbadmin=> SELECT user;
current_user
--------------
dbadmin
(1 row)
dbadmin=> CREATE SCHEMA test;
CREATE SCHEMA
dbadmin=> CREATE TABLE test.tab1 (c INT);
CREATE TABLE
dbadmin=> INSERT INTO test.tab1 SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> COMMIT;
COMMIT
dbadmin=> CREATE VIEW test.tab1_vw AS SELECT * FROM test.tab1;
CREATE VIEW
dbadmin=> CREATE USER user1;
CREATE USER
dbadmin=> GRANT USAGE ON SCHEMA test TO user1;
GRANT PRIVILEGE
dbadmin=> GRANT SELECT ON test.tab1_vw TO user1;
GRANT PRIVILEGE
dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> SELECT * FROM test.tab1_vw;
c
---
1
(1 row)
Great! The user, USER1, can read from the view as expected. But what happens when I change the ownership of the view from DBADMIN to USER1?
dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".
dbadmin=> ALTER VIEW test.tab1_vw OWNER TO user1;
ALTER VIEW
dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> SELECT * FROM test.tab1_vw;
ERROR 4367: Permission denied for relation tab1_vw
Yikes! USER1 can no longer read from the view even though it now owns it! Problem is, the user needs direct access to the underlying objects if the user doesn’t also own them.
dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".
dbadmin=> GRANT SELECT ON test.tab1 TO user1;
GRANT PRIVILEGE
dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".
dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> SELECT * FROM test.tab1_vw;
c
---
1
(1 row)
That’s better!Helpful Link:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERVIEW.htm
Have fun!
About the Author
James Knicely
Vertica Field Chief Technologist
I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!
