Changing the Owner of a View

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

We can change the owner of a database view with the ALTER VIEW … OWNER TO command.

Example:

dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> CREATE VIEW test_vw AS SELECT c1 FROM test;
CREATE VIEW

dbadmin=> SELECT table_schema, table_name, owner_name
dbadmin->   FROM views
dbadmin->  WHERE table_name = 'test_vw';
table_schema | table_name | owner_name
--------------+------------+------------
public       | test_vw    | dbadmin
(1 row)

dbadmin=> GRANT SELECT ON test_vw TO test1;
GRANT PRIVILEGE

dbadmin=> \c - test1
You are now connected as user "test1".

dbadmin=> select * from test_vw;
 c1
----
  1
(1 row)

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> ALTER VIEW test_vw OWNER TO test1;
ALTER VIEW

dbadmin=> SELECT table_schema, table_name, owner_name
dbadmin->   FROM views
dbadmin->  WHERE table_name = 'test_vw';
table_schema | table_name | owner_name
--------------+------------+------------
public       | test_vw    | test1
(1 row)

dbadmin=> \c - test1
You are now connected as user "test1".

dbadmin=> SELECT * FROM test_vw;
ERROR 4367:  Permission denied for relation test_vw

Wait! The TEST1 user now owns the view TEST_VW but can’t read from it?!?! To overcome this issue, I’ll need to grant the SELECT privilege on the underlying table, which is owned by DBADMIN, to the new view owner!

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> GRANT SELECT ON test TO test1;
GRANT PRIVILEGE

dbadmin=> \c - test1
You are now connected as user "test1".

dbadmin=> SELECT * FROM test_vw;
c1
----
  1
(1 row)

dbadmin=> SELECT grantor, privileges_description, object_schema, object_name, object_type
dbadmin->   FROM grants WHERE grantee = 'test1';
 grantor | privileges_description | object_schema | object_name | object_type
---------+------------------------+---------------+-------------+--------------
 dbadmin | USAGE                  |               | general     | RESOURCEPOOL
 test1   | SELECT                 | public        | test_vw     | VIEW
 dbadmin | SELECT                 | public        | test        | TABLE
(3 rows)

Helpful links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/ALTERVIEW.htm

Have fun!

Sign In or Register to comment.