DROP OWNED BY

The DROP OWNED BY drops all objects owned by and any on objects not owned by a .

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

The must have the DROP on the specified objects.

DROP OWNED BY will result in an error if the user was granted a (i.e., using the statement). To work around this, use and then use for each system-level privilege in the result.

Synopsis

Parameters

Parameter	Description
role_spec_list	The source , or a comma-separated list of source roles.
RESTRICT	(Default) Do not drop ownership if any objects (such as and tables) use it.
CASCADE	Not implemented.

Known limitations

• types are not dropped.

• drops all owned objects as well as any on objects not owned by the .

• If the for which you are trying to DROP OWNED BY was granted a (i.e., using the statement), the following error will be signalled:

  ERROR: cannot perform drop owned by if role has synthetic privileges; foo has entries in system.privileges
  SQLSTATE: 0A000
  HINT: perform REVOKE SYSTEM ... for the relevant privileges foo has in system.privileges
  

  The phrase “synthetic privileges” in the error message refers to .

  The workaround is to use and then use for each privilege in the result.

Examples

The following examples assume a . They involve a user we will create called maxroach and several tables. The setup is shown below.

From a Terminal window, open a SQL shell as the root user:

cockroach sql --insecure --host localhost --port 26257

Next, create the user maxroach:

CREATE USER IF NOT EXISTS maxroach;

From a second Terminal window, open a SQL shell as the newly created user maxroach.

cockroach sql --insecure --host localhost --port 26257 --user maxroach

Drop all objects owned by a user/role

From the maxroach user’s SQL shell, create a table called max_kv:

CREATE TABLE IF NOT EXISTS max_kv (k INT, v INT);

To verify that this table is owned by maxroach, use :

SHOW GRANTS FOR maxroach;

  database_name | schema_name | object_name   | grantee  | privilege_type | is_grantable
----------------+-------------+---------------+----------+----------------+---------------
  defaultdb     | public      | max_kv        | maxroach | ALL            |      t
(1 row)

To drop all of the objects owned by the user maxroach, switch to the root user’s SQL shell and use DROP OWNED BY:

DROP OWNED BY maxroach;

In this case, maxroach only owns the max_kv table, so this will drop that table from the database completely. To confirm that the table has been dropped, run :

SHOW TABLES;

SHOW TABLES 0

From the root user’s SQL shell, use to further confirm that the maxroach user has no remaining object grants:

SHOW GRANTS FOR maxroach;

SHOW GRANTS 0

Drop all grants on objects for a user/role

From the root user’s SQL shell, create a table called root_kv:

CREATE TABLE IF NOT EXISTS root_kv (k INT, v INT);

Next, grant all privileges on that table to user maxroach using :

GRANT ALL on root_kv TO maxroach;

Next, confirm that the user maxroach has all privileges on the table using :

SHOW GRANTS FOR maxroach;

  database_name | schema_name | object_name   | grantee  | privilege_type | is_grantable
----------------+-------------+---------------+----------+----------------+---------------
  defaultdb     | public      | root_kv       | maxroach | ALL            |      f
(1 row)

Next, switch to the maxroach user’s SQL shell, and insert some data into the table. It should succeed:

INSERT INTO root_kv(k) select i from generate_series(1,10) as i;

INSERT 0 10

Next, switch to the root user’s SQL shell and use DROP OWNED BY to remove all grants on objects to the user maxroach:

DROP OWNED BY maxroach;

Next, confirm that the user maxroach has no grants on any objects using :

SHOW GRANTS FOR maxroach;

SHOW GRANTS 0

Finally, switch back to the maxroach user’s SQL shell and try to insert data into the root_kv table. This should signal an error:

INSERT INTO root_kv(k) select i from generate_series(1,10) as i;

ERROR: user maxroach does not have INSERT privilege on relation root_kv
SQLSTATE: 42501

See also