DROP USER

The DROP USER statement removes one or more SQL users. You can use the keywords ROLE and USER interchangeably. DROP USER is an alias for DROP ROLE.

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

Consideration

Users that own objects (such as databases, tables, schemas, and types) cannot be dropped until the ownership is transferred to another user.

Required privileges

Non-admin users cannot drop admin users. To drop non-admin users, the user must be a member of the admin role or have the CREATEROLE parameter set.

Synopsis

See DROP ROLE: Synopsis.

Parameters

Parameter	Description
`user_name`	The name of the user to remove. To remove multiple users, use a comma-separate list of usernames. You can use `SHOW USERS` to find usernames.

Example

All of a user’s privileges must be revoked before the user can be dropped. In this example, first check a user’s privileges. Then, revoke the user’s privileges before removing the user.

CREATE DATABASE test;
CREATE TABLE customers (k int, v int);
CREATE USER max;
GRANT ALL ON TABLE customers TO max;

SHOW GRANTS ON customers FOR max;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  test          | public      | customers  | max     | ALL            |      f
(1 row)

You can test that dropping the user will fail unless all privileges are revoked (including default privileges). Issue the following statement to revoke a subset of the user’s privileges:

REVOKE CREATE,INSERT,UPDATE ON customers FROM max;

If you attempt to drop a user with privileges remaining, you will encounter an error like the following:

DROP USER max;

ERROR: cannot drop role/user max: grants still exist on test.public.customers
SQLSTATE: 2BP01

To see what privileges the user still has remaining on the table, issue the following statement:

SHOW GRANTS ON TABLE test.customers FOR max;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  test          | public      | customers  | max     | BACKUP         |      f
  test          | public      | customers  | max     | CHANGEFEED     |      f
  test          | public      | customers  | max     | DELETE         |      f
  test          | public      | customers  | max     | DROP           |      f
  test          | public      | customers  | max     | SELECT         |      f
  test          | public      | customers  | max     | ZONECONFIG     |      f
(6 rows)

To drop the user you must revoke all of the user’s remaining privileges:

REVOKE ALL ON TABLE public.customers FROM max;

Now dropping the user should succeed:

DROP USER max;

Statements

Syntax

Data Types

Spatial Data

Constraints

Consideration

Required privileges

Synopsis

Parameters

Example

See also

​Consideration

​Required privileges

​Synopsis

​Parameters

​Example

​See also

Consideration

Required privileges

Synopsis

Parameters

Example

See also