DROP USER - CockroachDB

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 “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Consideration

Users that (such as databases, tables, schemas, and types) cannot be dropped until the .

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

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 ). 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;

​Consideration

​Required privileges

​Synopsis

​Parameters

​Example

​See also

Consideration

Required privileges

Synopsis

Parameters

Example

See also