DROP OWNED BY

On this page Carat arrow pointing down

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

Note:

The DROP OWNED BY statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

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

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

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:

icon/buttons/copy
cockroach sql --insecure --host localhost --port 26257

Next, create the user maxroach:

icon/buttons/copy
CREATE USER IF NOT EXISTS maxroach;

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

icon/buttons/copy
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:

icon/buttons/copy
CREATE TABLE IF NOT EXISTS max_kv (k INT, v INT);

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

icon/buttons/copy
SHOW GRANTS FOR maxroach;
  database_name | schema_name | relation_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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
CREATE TABLE IF NOT EXISTS root_kv (k INT, v INT);

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

icon/buttons/copy
GRANT ALL on root_kv TO maxroach;

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

icon/buttons/copy
SHOW GRANTS FOR maxroach;
  database_name | schema_name | relation_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:

icon/buttons/copy
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:

icon/buttons/copy
DROP OWNED BY maxroach;

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

icon/buttons/copy
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:

icon/buttons/copy
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


Yes No
On this page

Yes No