Page 1 of 2
Change owner of schema?
Posted: Wed Jan 02, 2013 9:36 pm
by becky
Hello,
Can we change the owner of a schema?
Code: Select all
dbadmin=> \dn
List of schemas
Name | Owner | Comment
---------------+---------+---------
v_internal | dbadmin |
v_catalog | dbadmin |
v_monitor | dbadmin |
public | dbadmin |
my_system_01 | dbadmin |
dataschema11 | dbadmin |
(6 rows)
I tried the obvious, but altering a schema in this manner doesn't work:
Code: Select all
dbadmin=> alter schema my_system_01 owner to becky;
ERROR 2676: Command ALTER SCHEMA is not supported
Re: Change owner or schema?
Posted: Thu Jan 03, 2013 11:12 am
by id10t
Hi!
The schema owner is typically the user who creates the schema, but CREATED(or OWNER) != has privileges.
I can be owner but without privileges (once I created schema and after it privilege is revoked):
Code: Select all
dbadmin=> \dn
List of schemas
Name | Owner | Comment
------------+---------+---------
v_internal | dbadmin |
v_catalog | dbadmin |
v_monitor | dbadmin |
public | dbadmin |
Vertica | dbadmin |
foo | danik |
(6 rows)
dbadmin=> select USER;
current_user
--------------
dbadmin
(1 row)
Code: Select all
dbadmin=> REVOKE ALL ON SCHEMA Vertica FROM danik;
REVOKE PRIVILEGE
dbadmin=> \dn
List of schemas
Name | Owner | Comment
------------+---------+---------
v_internal | dbadmin |
v_catalog | dbadmin |
v_monitor | dbadmin |
public | dbadmin |
Vertica | dbadmin |
foo | danik |
(6 rows)
Code: Select all
dbadmin=> \c db danik
You are now connected to database "db" as user "danik".
db=> create table Vertica.Egg (id int);
ROLLBACK 4367: Permission denied for schema Vertica
db=> \dn
List of schemas
Name | Owner | Comment
------------+---------+---------
v_internal | dbadmin |
v_catalog | dbadmin |
v_monitor | dbadmin |
public | dbadmin |
foo | danik |
(5 rows)
As you see: I still owner but can't do a thing in schema.
Hoe it helps.
Re: Change owner or schema?
Posted: Thu Jan 03, 2013 2:33 pm
by becky
Thanks, sKwa. But I'm not sure I understand. In your example the user danik is the owner of the foo schema, not the vertica schema. Can you take away the privileges on the foo schema from danik even though he is the owner? That's weird if so.
Re: Change owner or schema?
Posted: Thu Jan 03, 2013 9:07 pm
by id10t
Hi!
Oops...
My fail. Now, after I checked my paths twice
I have to say - I don't understand how it works and need to learn.
Re: Change owner of schema?
Posted: Wed Apr 03, 2013 8:57 am
by khine
Hi there can i ask you a ques that how can i create a database schema ?
Re: Change owner of schema?
Posted: Wed Apr 03, 2013 12:44 pm
by JimKnicely
Hi Khine,
Welcome to the forums!
To create a schema in a Vertica database you must be logged in as a user having the superuser or CREATE privilege for the database.
You use the CREATE SCHEMA command to create a schema:
Code: Select all
dbadmin=> CREATE SCHEMA my_schema;
CREATE SCHEMA
You can use the \dn vsql meta command to view info about a schema:
Code: Select all
dbadmin=> \dn my_schema;
List of schemas
Name | Owner | Comment
-----------+---------+---------
my_schema | dbadmin |
(1 row)
Notice that by default, the owner of the schema is the creator.
If you want another user to own the schema, use the AUTHORIZATION key word in the create statement.
Example:
Code: Select all
dbadmin=> CREATE USER jim;
CREATE USER
dbadmin=> CREATE SCHEMA jim_schema AUTHORIZATION jim;
CREATE SCHEMA
dbadmin=> \dn jim_schema
List of schemas
Name | Owner | Comment
------------+-------+---------
jim_schema | jim |
(1 row)
I hope this helps!
Re: Change owner of schema?
Posted: Tue Oct 27, 2015 3:50 pm
by mesunderlin
All of the above is how to change the default owner at schema creation.
Is there anyway to change the owner of an already existing schema?
Example:
User Bob has created schema my_schema, owned by user Bob.
Bob wins lotto and leaves company
With Bob gone, we want to delete user Bob, but want to keep my_schema and it's data
Desire is to simply change ownership of my_schema to user Jill
Can this be done?