Hi @mesunderlin
have you found any solution to this problem ?, we have also facing same scenario now. Kindly help us
Change owner of schema?
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Change owner of schema?
Hi,
Having the ability to change the owner of a schema after the schema has been created is on the road map!
In the mean time, the work around is as follows:
dbadmin=> select schema_owner from schemata where schema_name = 'the_schema';
schema_owner
--------------
old_user
(1 row)
dbadmin=> select owner_name from tables where table_schema = 'the_schema';
owner_name
------------
old_user
old_user
(2 rows)
dbadmin=> alter schema the_schema rename to the_schema_old;
ALTER SCHEMA
dbadmin=> alter table the_schema_old.t1 set schema the_schema;
ALTER TABLE
dbadmin=> alter table the_schema_old.t2 set schema the_schema;
ALTER TABLE
dbadmin=> alter table the_schema_old.t1 owner to new_user;
ALTER TABLE
dbadmin=> alter table the_schema_old.t2 owner to new_user;
ALTER TABLE
dbadmin=> select schema_owner from schemata where schema_name = 'the_schema';
schema_owner
--------------
new_user
(1 row)
dbadmin=> select owner_name from tables where table_schema = 'the_schema';
owner_name
------------
new_user
new_user
(2 rows)
dbadmin=> drop schema the_schema_old;
DROP SCHEMA
Having the ability to change the owner of a schema after the schema has been created is on the road map!
In the mean time, the work around is as follows:
- Rename the current schema to a different name
- Recreate the intended schema with the desired owner
- Move the tables to the intended schema with:
ALTER TABLE <table_name> SET SCHEMA <intended_schema_name>;
ALTER TABLE <table_name> owner to <intended_owner_name>; - Drop the renamed schema
dbadmin=> select schema_owner from schemata where schema_name = 'the_schema';
schema_owner
--------------
old_user
(1 row)
dbadmin=> select owner_name from tables where table_schema = 'the_schema';
owner_name
------------
old_user
old_user
(2 rows)
dbadmin=> alter schema the_schema rename to the_schema_old;
ALTER SCHEMA
dbadmin=> alter table the_schema_old.t1 set schema the_schema;
ALTER TABLE
dbadmin=> alter table the_schema_old.t2 set schema the_schema;
ALTER TABLE
dbadmin=> alter table the_schema_old.t1 owner to new_user;
ALTER TABLE
dbadmin=> alter table the_schema_old.t2 owner to new_user;
ALTER TABLE
dbadmin=> select schema_owner from schemata where schema_name = 'the_schema';
schema_owner
--------------
new_user
(1 row)
dbadmin=> select owner_name from tables where table_schema = 'the_schema';
owner_name
------------
new_user
new_user
(2 rows)
dbadmin=> drop schema the_schema_old;
DROP SCHEMA
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.