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.
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 OWNEDstatement must be a member of theadminrole, or must be a member of the target role and have theCREATEprivilege on the current database. - The target role (i.e., the desired role of the objects) must have the
CREATEprivilege 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)
See also
Was this helpful?
