Changing the Owner of a View
Jim_Knicely
- Select Field - Administrator
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!
0