OWNER TO
is a subcommand of ALTER DATABASE
, ALTER TABLE
, ALTER SCHEMA
, ALTER SEQUENCE
, ALTER TYPE
, and ALTER VIEW
, and is used to change the owner of an object in a cluster.
The OWNER TO
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
This page documents ALTER DATABASE ... OWNER TO
and ALTER TABLE ... OWNER TO
. For details on the ALTER SCHEMA ... OWNER TO
, ALTER SEQUENCE ... OWNER TO
, ALTER TYPE ... OWNER TO
, and ALTER VIEW ... OWNER TO
, see the ALTER SCHEMA
, ALTER SEQUENCE
, ALTER TYPE
, and ALTER VIEW
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.