
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!