DROP OWNED BY

On this page

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

Required privileges

The role must have the DROP privilege on the specified objects.

DROP OWNED BY will result in an error if the user was granted a system-level privilege (i.e., using the GRANT SYSTEM ... statement). To work around this, use SHOW SYSTEM GRANTS FOR <role> and then use REVOKE SYSTEM ... for each system-level privilege in the result.

Synopsis

Parameters

Known limitations

• ENUM types are not dropped.
• DROP OWNED BY drops all owned objects as well as any grants on objects not owned by the role.

• If the role for which you are trying to DROP OWNED BY was granted a system-level privilege (i.e., using the GRANT SYSTEM ... 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 system-level privileges.

  The workaround is to use SHOW SYSTEM GRANTS FOR {role} and then use REVOKE SYSTEM ... for each privilege in the result. #88149

Examples

The following examples assume a local cluster is running. 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:

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;

SHOW TABLES 0

From the root user's SQL shell, use SHOW GRANTS 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:

GRANT ALL on root_kv TO maxroach;

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

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:

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

• Authorization in CockroachDB
• GRANT
• SHOW GRANTS
• REVOKE
• REASSIGN OWNED
• DROP ROLE
• SHOW TABLES
• SQL Statements
• Online Schema Changes