REASSIGN OWNED

On this page

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

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:

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

Pricing

Contact us

Sign In