A View Owner Needs Access to the Underlying Objects

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

You can use the ALTER VIEW … OWNER TO command to change a Vertica database view’s ownership. It’s important to know that a View’s owner must also have access to the underlying objects referenced by the view!

Example:

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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERVIEW.htm

Sign In or Register to comment.