The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Cascading Schema Ownership

Jim Knicely authored this tip.

Yesterday’s quick tip revealed that as of Vertica 9.1.1, you can transfer the ownership of a schema to another user. But what about the underlying schema objects (i.e., TABLES, VIEWS, etc.)?

By default, the ALTER SCHEMA…OWNER TO command does not affect ownership of objects in the target schema or the privileges granted on them. That’s where the CASCADE clause comes into play!

It will:

  • Transfer ownership of objects owned by the previous schema owner to the new owner.
  • Revoke all object privileges granted by the previous schema owner.

Example:

dbadmin=> SELECT schema_name, schema_owner FROM schemata WHERE schema_name = 'fact_tables';
 schema_name | schema_owner
-------------+--------------
 fact_tables | jim
(1 row)

dbadmin=> SELECT table_schema, table_name, owner_name FROM tables WHERE table_name = 'fact1';
 table_schema | table_name | owner_name
--------------+------------+------------
 fact_tables  | fact1      | jim
(1 row)

dbadmin=> ALTER SCHEMA fact_tables OWNER TO jane CASCADE;
ALTER SCHEMA

dbadmin=> SELECT schema_name, schema_owner FROM schemata WHERE schema_name = 'fact_tables';
 schema_name | schema_owner
-------------+--------------
 fact_tables | jane
(1 row)

dbadmin=> SELECT table_schema, table_name, owner_name FROM tables WHERE table_name = 'fact1';
 table_schema | table_name | owner_name
--------------+------------+------------
 fact_tables  | fact1      | jane
(1 row)

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.