REASSIGN OWNED

New in v21.1: 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.

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

  • The user executing the REASSIGN OWNED statement must be a member of the admin role, or must be a member of the target role and have the CREATE privilege on the current database.
  • The target role (i.e., the desired role of the objects) must have the CREATE privilege on the current database.

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

YesYes NoNo