OWNER TO

On this page Carat arrow pointing down
Warning:
As of May 16, 2023, CockroachDB v21.2 is no longer supported. For more details, refer to the Release Support Policy.

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.

Note:

The OWNER TO statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Note:

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 the CREATEDB 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 the CREATE 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:

icon/buttons/copy
$ 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.

icon/buttons/copy
> 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:

icon/buttons/copy
> 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)
Note:

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.

icon/buttons/copy
> ALTER TABLE promo_codes OWNER TO max;

To verify that the owner is now max, query the pg_catalog.pg_tables table:

icon/buttons/copy
> SELECT tableowner FROM pg_catalog.pg_tables WHERE tablename = 'promo_codes';
  tableowner
--------------
  max
(1 row)
Note:

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.

See also


Yes No
On this page

Yes No