Cascading Schema Ownership

[Deleted User]

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!