OWNER TO
is a subcommand of ALTER DATABASE
, ALTER TABLE
, ALTER SCHEMA
, and ALTER TYPE
, and is used to change the owner of an object in a cluster.
This page documents ALTER DATABASE ... OWNER TO
and ALTER TABLE ... OWNER TO
. For details on the ALTER SCHEMA ... OWNER TO
and ALTER TYPE ... OWNER TO
, see the ALTER SCHEMA
and ALTER TYPE
pages.
Required privileges
- To change the owner of a database, the user must be an
admin
user, or the current owner of the database and a member of the new owner role. The user must also have theCREATEDB
privilege. - To change the owner of a table, the user must be an
admin
user, or the current owner of the table and a member of the new owner role. The new owner role must also have theCREATE
privilege on the schema to which the table belongs.
Syntax
Databases
ALTER DATABASE <name> OWNER TO <newowner>
Tables
ALTER TABLE <name> OWNER TO <newowner>
Parameters
Parameter | Description |
---|---|
name |
The name of the table or database. |
newowner |
The name of the new owner. |
Examples
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.
To follow along, run cockroach demo
to start a temporary, in-memory cluster with the movr
dataset preloaded:
$ cockroach demo
Change a database's owner
Suppose that the current owner of the movr
database is root
and you want to change the owner to a new user named max
.
> ALTER DATABASE movr OWNER TO max;
To verify that the owner is now max
, query the pg_catalog.pg_database
and pg_catalog.pg_roles
tables:
> SELECT rolname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid WHERE datname = 'movr';
rolname
-----------
max
(1 row)
If the user running the command is not an admin user, they must own the database and be a member of the new owning role. They must also have the CREATEDB
privilege.
Change a table's owner
Suppose that the current owner of the rides
table is root
and you want to change the owner to a new user named max
.
> ALTER TABLE promo_codes OWNER TO max;
To verify that the owner is now max
, query the pg_catalog.pg_tables
table:
> SELECT tableowner FROM pg_catalog.pg_tables WHERE tablename = 'promo_codes';
tableowner
--------------
max
(1 row)
If the user running the command is not an admin user, they must own the table and be a member of the new owning role. Also, the new owner role must also have the CREATE
privilege on the schema to which the table belongs.