Cascading Schema Ownership

Posted August 13, 2018 by Sarah Lemaire, Manager, Vertica Documentation

Jim Knicely authored this tip.</br? 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.)?</br? 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!</br? It will:</br?
    • * Transfer ownership of objects owned by the previous schema owner to the new owner.
</br?
    • * Revoke all object privileges granted by the previous schema owner.
</br? 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!