Change owner of schema?

Moderator: NorbertKrupa

Krishnamraju
Newbie
Newbie
Posts: 1
Joined: Sat Apr 22, 2017 6:12 pm

Re: Change owner of schema?

Post by Krishnamraju » Sat Apr 22, 2017 6:18 pm

Hi @mesunderlin
have you found any solution to this problem ?, we have also facing same scenario now. Kindly help us

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Change owner of schema?

Post by JimKnicely » Wed Apr 26, 2017 2:34 pm

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:

  1. Rename the current schema to a different name
  2. Recreate the intended schema with the desired owner
  3. 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>;
  4. Drop the renamed schema
Example:

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”