REASSIGN OWNED

On this page Carat arrow pointing down
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. For more details, refer to the Release Support Policy.

The REASSIGN OWNED statement changes the ownership of all database objects (i.e., tables, types, or schemas) in the current database that are currently owned by a specific role or user.

Note:

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

Tip:

To change the ownership of any single object (e.g., a table or a database), use the OWNER TO subcommand of the object's ALTER statement.

Required privileges

  • To reassign ownership with REASSIGN OWNED, the user must be a member of the current owner's role and a member of the target owner's role.
  • Members of the admin role can always use REASSIGN OWNED BY.

Syntax

REASSIGN OWNED BY role_spec_list TO role_spec

Parameters

Parameter Description
role_spec_list The source role, or a comma-separated list of source roles.
role_spec The target role.

Example

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 the owner of all tables in a database

Suppose that the current owner of the users, vehicles, and rides tables in the movr database is a role named cockroachlabs.

icon/buttons/copy
> CREATE ROLE cockroachlabs;
icon/buttons/copy
> GRANT CREATE ON DATABASE movr TO cockroachlabs;
icon/buttons/copy
> ALTER TABLE users OWNER TO cockroachlabs;
> ALTER TABLE vehicles OWNER TO cockroachlabs;
> ALTER TABLE rides OWNER TO cockroachlabs;
icon/buttons/copy
> SHOW TABLES;
  schema_name |         table_name         | type  |     owner     | estimated_row_count | locality
--------------+----------------------------+-------+---------------+---------------------+-----------
  public      | promo_codes                | table | demo          |                1000 | NULL
  public      | rides                      | table | cockroachlabs |                 500 | NULL
  public      | user_promo_codes           | table | demo          |                   0 | NULL
  public      | users                      | table | cockroachlabs |                  50 | NULL
  public      | vehicle_location_histories | table | demo          |                1000 | NULL
  public      | vehicles                   | table | cockroachlabs |                  15 | NULL
(6 rows)

Now suppose you want to change the owner for all of the tables owned by cockroachlabs to a new role named movrlabs.

icon/buttons/copy
> CREATE ROLE movrlabs;
icon/buttons/copy
> GRANT CREATE ON DATABASE movr TO movrlabs;
icon/buttons/copy
> REASSIGN OWNED BY cockroachlabs TO movrlabs;
icon/buttons/copy
> SHOW TABLES;
  schema_name |         table_name         | type  |  owner   | estimated_row_count | locality
--------------+----------------------------+-------+----------+---------------------+-----------
  public      | promo_codes                | table | demo     |                1000 | NULL
  public      | rides                      | table | movrlabs |                 500 | NULL
  public      | user_promo_codes           | table | demo     |                   0 | NULL
  public      | users                      | table | movrlabs |                  50 | NULL
  public      | vehicle_location_histories | table | demo     |                1000 | NULL
  public      | vehicles                   | table | movrlabs |                  15 | NULL
(6 rows)

See also


Yes No
On this page

Yes No