Change owner of schema?

Moderator: NorbertKrupa

User avatar
becky
Intermediate
Intermediate
Posts: 118
Joined: Sat Apr 28, 2012 11:37 am

Change owner of schema?

Post by becky » Wed Jan 02, 2013 9:36 pm

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
THANKS - BECKSTER

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Change owner or schema?

Post by id10t » Thu Jan 03, 2013 11:12 am

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.

User avatar
becky
Intermediate
Intermediate
Posts: 118
Joined: Sat Apr 28, 2012 11:37 am

Re: Change owner or schema?

Post by becky » Thu Jan 03, 2013 2:33 pm

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.
THANKS - BECKSTER

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Change owner or schema?

Post by id10t » Thu Jan 03, 2013 9:07 pm

Hi!

Oops... :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.

khine
Beginner
Beginner
Posts: 33
Joined: Wed Apr 03, 2013 2:54 am

Re: Change owner of schema?

Post by khine » Wed Apr 03, 2013 8:57 am

Hi there can i ask you a ques that how can i create a database schema ?

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 03, 2013 12:44 pm

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!
Jim Knicely

Image

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

mesunderlin
Newbie
Newbie
Posts: 5
Joined: Wed Mar 26, 2014 9:12 pm

Re: Change owner of schema?

Post by mesunderlin » Tue Oct 27, 2015 3:50 pm

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?

Post Reply

Return to “New to Vertica Database Administration”