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