DROP USER

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

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

Considerations

• The admin user/role cannot be dropped, and root must always be a member of admin.
• A user/role cannot be dropped if it has privileges. Use to remove privileges.
• Users/roles that (such as databases, tables, schemas, and types) cannot be dropped until the .
• If a user/role is logged in while a drops that user, CockroachDB checks that the user exists before allowing it to inherit privileges from . In addition, any active are revoked when a user is dropped.

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 parameter set.

Synopsis

See .

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 to find usernames.

Example

Remove privileges

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)

REVOKE CREATE,INSERT,UPDATE ON customers FROM max;

Remove default privileges

In addition to removing a user’s privileges, a user’s must be removed prior to dropping the user. If you attempt to drop a user with modified default privileges, 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;

See also