Allowing Users to Query a View Owned by Another User: Quick Tip

As we learned in yesterday’s Vertica Quick Tip A View Owner Needs Access to the Underlying Objects, the owner of a View must have direct access to the underlying objects referenced by the view!

But what if another user wants to read a view owned by another user? In this case, not only must the View owner have read access to the View’s underlying objects, but the owner must have been granted the read privilege using the WITH GRANT OPTION.

Example: dbadmin=> SELECT user; current_user -------------- dbadmin (1 row) dbadmin=> CREATE USER user1; CREATE USER dbadmin=> CREATE USER user3; CREATE USER dbadmin=> CREATE SCHEMA test; CREATE SCHEMA dbadmin=> GRANT USAGE ON SCHEMA test TO user1, user3; GRANT PRIVILEGE 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=> ALTER VIEW test.tab1_vw OWNER TO user1; ALTER VIEW dbadmin=> GRANT SELECT ON test.tab1 TO user1; GRANT PRIVILEGE dbadmin=> \c - user1 You are now connected as user "user1". dbadmin=> SELECT * FROM test.tab1_vw; c --- 1 (1 row) This works as expected per yesterday’s Vertica Quick Tip! But what happens if another user (i.e,. USER3) also wants to read from the view? dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> GRANT SELECT ON test.tab1_vw TO user3; GRANT PRIVILEGE dbadmin=> \c - user3 You are now connected as user "user3". dbadmin=> SELECT * FROM test.tab1_vw; ERROR 4367: Permission denied for relation tab1_vw That didn’t work! Why? The owner of the View, USER1, must be granted the SELECT privilege using the WITH GRANT OPTION on the underlying objects! dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> GRANT SELECT ON test.tab1 TO user1 WITH GRANT OPTION; GRANT PRIVILEGE dbadmin=> \c - user3 You are now connected as user "user3". dbadmin=> SELECT * FROM test.tab1_vw; c --- 1 (1 row) That’s better!

Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERVIEW.htm

https://www.vertica.com/blog/a-view-owner-needs-access-to-the-underlying-objects-quick-tip/

Have fun!