REASSIGN OWNED

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

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

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

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 can always use REASSIGN OWNED BY.

Syntax

Parameters

Parameter	Description
role\_spec\_list	The source role, or a comma-separated list of source roles.
role\_spec	The target role.

Example

Setup

To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:

$ 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.

> CREATE ROLE cockroachlabs;

> GRANT CREATE ON DATABASE movr TO cockroachlabs;

> ALTER TABLE users OWNER TO cockroachlabs;
> ALTER TABLE vehicles OWNER TO cockroachlabs;
> ALTER TABLE rides OWNER TO cockroachlabs;

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

> CREATE ROLE movrlabs;

> GRANT CREATE ON DATABASE movr TO movrlabs;

> REASSIGN OWNED BY cockroachlabs TO movrlabs;

> 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