Allowing Users to Query a View Owned by Another User

Jim_Knicely · January 2019

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/9.2.x/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!