Moving a Table to a New Schema and Inherting the New Schema Privileges

Jim_Knicely · December 2019

You can move a table from one schema to another schema with a simple ALTER TABLE command. But, you might be surprised that the table will not inherit the new schema’s privileges even if the new schema was created using the DEFAULT INCLUDE PRIVILEGES directive. After moving the table you will need to manually alter the table telling Vertica you want it to inherit the new schema’s privileges.

Why? Because enabling inherited privileges with ALTER SCHEMA … DEFAULT INCLUDE PRIVILEGES only affects newly created tables and views. This setting does not affect already-existing tables and views.

The move operation is a meta-data/catalog update only. Vertica does not issue a CREATE TABLE statement in the target schema

Example:

dbadmin=> CREATE USER dennis;
CREATE USER

dbadmin=> CREATE SCHEMA s DEFAULT INCLUDE PRIVILEGES;
CREATE SCHEMA

dbadmin=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA s TO dennis;
GRANT PRIVILEGE

dbadmin=> CREATE TABLE s.t1 (c INT); -- This works and user DENNIS can read from it
WARNING 6978:  Table "t1" will include privileges from schema "s"
CREATE TABLE

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

dbadmin=> SELECT * FROM s.t1;
c
---
(0 rows)

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

dbadmin=> CREATE TABLE public.t2 (c INT);
CREATE TABLE

dbadmin=> ALTER TABLE public.t2 SET SCHEMA s; -- This works, but user DENNIS cannot read from it
ALTER TABLE

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

dbadmin=> SELECT * FROM s.t2;
ERROR 4367:  Permission denied for relation t2

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

dbadmin=> ALTER TABLE s.t2 INCLUDE SCHEMA PRIVILEGES; -- Now DENNIS can from read it!
ALTER TABLE

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

dbadmin=> SELECT * FROM s.t2;
c
---
(0 rows)

Helpful Links:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/MovingATableToAnotherSchema.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Privileges/TableViewInheritance.htm

Have Fun!