Change owner of schema?

Forum for the novice Vertica database administrator

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: 1738
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 HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”

Who is online

Users browsing this forum: No registered users and 1 guest