REASSIGN OWNED

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.

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

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

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 cockroach demo to start a temporary, in-memory cluster with the movr 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)

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Syntax

Parameters

Example

Setup

Change the owner of all tables in a database

See also

​Required privileges

​Syntax

​Parameters

​Example

​Setup

​Change the owner of all tables in a database

​See also

Required privileges

Syntax

Parameters

Example

Setup

Change the owner of all tables in a database

See also