> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE USER

The `CREATE USER` [statement](/docs/stable/sql-statements.md) creates SQL users, which let you control [privileges](/docs/stable/security-reference/authorization.md#managing-privileges) on your databases and tables.

You can use the keywords `ROLE` and `USER` interchangeably. `CREATE USER` is equivalent to [`CREATE ROLE`](/docs/stable/create-role), with one exception: `CREATE ROLE` sets the `NOLOGIN` [role option](#role-options), which prevents the new role from being used to log in to the database. You can use `CREATE ROLE` and specify the `LOGIN` [role option](#role-options) to achieve the same result as `CREATE USER`.

> [!NOTE]
>
> The `CREATE USER` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md).

## Considerations

* After creating users, you must [grant them privileges to databases and tables](/docs/stable/grant.md).
* All users belong to the `public` role, to which you can [grant](/docs/stable/grant.md) and [revoke](/docs/stable/revoke.md) privileges.
* On secure clusters, you must [create client certificates for users](/docs/stable/cockroach-cert.md#create-the-certificate-and-key-pair-for-a-client) and users must [authenticate their access to the cluster](#user-authentication).
* The `CREATE USER` statement waits for full-cluster visibility of the new user metadata. It is not blocked by long-running transactions that may have accessed older metadata, and it does not create a [background job](/docs/stable/show-jobs.md).

## Required privileges

To create other users, the user must be a member of the `admin` role or have the [`CREATEROLE`](#create-a-user-that-can-create-other-users-and-manage-authentication-methods-for-the-new-users) parameter set.

## Synopsis

See [`CREATE ROLE`: Synopsis](/docs/stable/create-role#synopsis).

## Parameters

| Parameter | Description |
| - | - |
| `name` | The name of the user to create. |
| `WITH role_option` | Apply a [role option](#role-options) to the role. |

### User names

* Are case-insensitive.
* Must start with a letter or underscore.
* Must contain only letters, numbers, periods, or underscores.
* Must be between 1 and 63 characters.
* Cannot be `none`.
* Cannot start with `pg_` or `crdb_internal`. Object names with these prefixes are reserved for [system catalogs](/docs/stable/system-catalogs.md).
* User and role names share the same namespace and must be unique.

### Role options

| Role option | Description |
| - | - |
|  `BYPASSRLS`/`NOBYPASSRLS` | : Allow or disallow a role to bypass [row-level security (RLS)](/docs/stable/row-level-security.md) policies on a table. This option controls the access from an RLS perspective only; the user also needs sufficient [`GRANT`](/docs/stable/grant) privileges to read or write to the table. |
| `CANCELQUERY`/`NOCANCELQUERY` | **Deprecated in v22.2: Use the `CANCELQUERY` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to cancel [queries](/docs/stable/cancel-query.md) and [sessions](/docs/stable/cancel-session.md) of other roles. Without this role option, roles can only cancel their own queries and sessions. Even with the `CANCELQUERY` role option, non-`admin` roles cannot cancel `admin` queries or sessions. This option should usually be combined with `VIEWACTIVITY` so that the role can view other roles' query and session information.  By default, the role option is set to `NOCANCELQUERY` for all non-`admin` roles. |
| `CONTROLCHANGEFEED`/`NOCONTROLCHANGEFEED` | **Deprecated in v23.1: Use the `CHANGEFEED` [privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to run [`CREATE CHANGEFEED`](/docs/stable/create-changefeed) on tables they have `SELECT` privileges on.  By default, the role option is set to `NOCONTROLCHANGEFEED` for all non-`admin` roles. |
| `CONTROLJOB`/`NOCONTROLJOB` | Allow or disallow a role to [pause](/docs/stable/pause-job.md), [resume](/docs/stable/resume-job.md), and [cancel](/docs/stable/cancel-job.md) jobs. Non-`admin` roles cannot control jobs created by `admin` roles.  By default, the role option is set to `NOCONTROLJOB` for all non-`admin` roles. |
| `CREATEDB`/`NOCREATEDB` | Allow or disallow a role to [create](/docs/stable/create-database.md) or [rename](/docs/stable/alter-database.md#rename-to) a database. The role is assigned as the owner of the database.  By default, the role option is set to `NOCREATEDB` for all non-`admin` roles. |
| `CREATELOGIN`/`NOCREATELOGIN` | Allow or disallow a role to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN/NOLOGIN` role options.  By default, the role option is set to `NOCREATELOGIN` for all non-`admin` roles. |
| `CREATEROLE`/`NOCREATEROLE` | Allow or disallow the new role to [create](/docs/stable/create-role.md), alter, and [drop](/docs/stable/drop-role.md) other non-`admin` roles.  By default, the role option is set to `NOCREATEROLE` for all non-`admin` roles. |
| `LOGIN`/`NOLOGIN` | Allow or disallow a role to log in with one of the [client authentication methods](/docs/stable/authentication.md#client-authentication). Setting the role option to `NOLOGIN` prevents the role from logging in using any authentication method. |
| `MODIFYCLUSTERSETTING`/`NOMODIFYCLUSTERSETTING` | Allow or disallow a role to modify the [cluster settings](/docs/stable/cluster-settings.md) with the `sql.defaults` prefix.  By default, the role option is set to `NOMODIFYCLUSTERSETTING` for all non-`admin` roles. |
| `PASSWORD password`/`PASSWORD NULL` | The credential the role uses to [authenticate their access to a secure cluster](/docs/stable/authentication.md#client-authentication). A password should be entered as a [string literal](/docs/stable/sql-constants.md#string-literals). For compatibility with PostgreSQL, a password can also be entered as an identifier.  To prevent a role from using [password authentication](/docs/stable/authentication.md#client-authentication) and to mandate [certificate-based client authentication](/docs/stable/authentication.md#client-authentication), [set the password as `NULL`](/docs/stable/create-role#prevent-a-role-from-using-password-authentication). |
| `PROVISIONSRC` | A system-managed role option that identifies the authentication method and identity provider that automatically provisioned this user. The format is `{method}:{provider}` where method is `ldap`, `oidc`, or `jwt_token`. Examples: `ldap:example.com`, `oidc:https://accounts.google.com`, `jwt_token:https://auth.example.com`. This option is set automatically during [LDAP](/docs/stable/ldap-authentication.md#option-1-automatic-user-provisioning-recommended), [OIDC](/docs/stable/sso-db-console.md#step-3-configure-user-creation), or [JWT](/docs/stable/sso-sql.md#configure-user-provisioning) user provisioning and cannot be modified or removed. Users with this option cannot change their own passwords. |
| `SQLLOGIN`/`NOSQLLOGIN` | **Deprecated in v22.2: Use the `NOSQLLOGIN` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to log in using the SQL CLI with one of the [client authentication methods](/docs/stable/authentication.md#client-authentication). The role option to `NOSQLLOGIN` prevents the role from logging in using the SQL CLI with any authentication method while retaining the ability to log in to DB Console. It is possible to have both `NOSQLLOGIN` and `LOGIN` set for a role and `NOSQLLOGIN` takes precedence on restrictions.  Without any role options all login behavior is permitted. |
| `VALID UNTIL` | The date and time (in the [`timestamp`](/docs/stable/timestamp) format) after which the [password](#parameters) is not valid. |
| `VIEWACTIVITY`/`NOVIEWACTIVITY` | **Deprecated in v22.2: Use the `VIEWACTIVITY` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to see other roles' [queries](/docs/stable/show-statements.md) and [sessions](/docs/stable/show-sessions.md) using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the [**Statements**](/docs/stable/ui-statements-page.md) and [**Transactions**](/docs/stable/ui-transactions-page.md) pages in the DB Console. `VIEWACTIVITY` also permits visibility of node hostnames and IP addresses in the DB Console. With `NOVIEWACTIVITY`, the `SHOW` commands show only the role's own data, and DB Console pages redact node hostnames and IP addresses. By default, the role option is set to `NOVIEWACTIVITY` for all non-`admin` roles. |
| `VIEWCLUSTERSETTING` / `NOVIEWCLUSTERSETTING` | **Deprecated in v22.2: Use the `VIEWCLUSTERSETTING` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to view the [cluster settings](/docs/stable/cluster-settings.md) with `SHOW CLUSTER SETTING` or to access the [**Cluster Settings**](/docs/stable/ui-debug-pages.md) page in the DB Console.  By default, the role option is set to `NOVIEWCLUSTERSETTING` for all non-`admin` roles. |
| `VIEWACTIVITYREDACTED`/`NOVIEWACTIVITYREDACTED` | **Deprecated in v22.2: Use the `VIEWACTIVITYREDACTED` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to see other roles' queries and sessions using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the Statements and Transactions pages in the DB Console. With `VIEWACTIVITYREDACTED`, a user will not have access to the usage of statements diagnostics bundle (which can contain PII information) in the DB Console, and will not be able to list queries containing [constants](/docs/stable/sql-constants.md) for other users when using the `listSessions` endpoint through the [Cluster API](/docs/stable/cluster-api.md). It is possible to have both `VIEWACTIVITY` and `VIEWACTIVITYREDACTED`, and `VIEWACTIVITYREDACTED` takes precedence on restrictions. If the user has `VIEWACTIVITY` but doesn't have `VIEWACTIVITYREDACTED`, they will be able to see DB Console pages and have access to the statements diagnostics bundle.  By default, the role option is set to `NOVIEWACTIVITYREDACTED` for all non-`admin` roles. |

## User authentication

Secure clusters require users to authenticate their access to databases and tables. CockroachDB offers three methods for this:

* [Client certificate and key authentication](/docs/stable/authentication.md#client-authentication), which is available to all users. To ensure the highest level of security, we recommend only using client certificate and key authentication.

* [Password authentication](#create-a-user-with-a-password), which is available to users and roles who you've created passwords for. To create a user with a password, use the `WITH PASSWORD` clause of `CREATE USER`. To add a password to an existing user, use the [`ALTER USER`](/docs/stable/alter-user) statement.

  Users can use passwords to authenticate without supplying client certificates and keys; however, we recommend using certificate-based authentication whenever possible.

  Password creation is supported only in secure clusters.

* [GSSAPI authentication](/docs/stable/gssapi_authentication.md).

## Examples

To run the following examples, [start a secure single-node cluster](/docs/stable/cockroach-start-single-node.md) and use the built-in SQL shell:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql --certs-dir=certs
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW USERS;
```

```
username | options | member_of | estimated_last_login_time
---------+---------+-----------+------------------------------
admin    | {}      | {}        | NULL
root     | {}      | {admin}   | NULL
(2 rows)
```

> [!NOTE]
>
> The following statements are run by the `root` user that is a member of the `admin` role and has `ALL` privileges.

### Create a user

Usernames are case-insensitive; must start with a letter or underscore; must contain only letters, numbers, periods, or underscores; and must be between 1 and 63 characters.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER no_options;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
 username  | options | member_of | estimated_last_login_time
-----------+---------+-----------+------------------------------
admin      | {}      | {}        | NULL
no_options | {}      | {}        | NULL
root       | {}      | {admin}   | NULL
(3 rows)
```

After creating users, you must:

* [Grant them privileges to databases](/docs/stable/grant.md).
* For secure clusters, you must also [create their client certificates](/docs/stable/cockroach-cert.md#create-the-certificate-and-key-pair-for-a-client).

### Create a user with a password

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER with_password WITH LOGIN PASSWORD '$tr0nGpassW0rD' VALID UNTIL '2021-10-10';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
  username    |                  options                  | member_of | estimated_last_login_time
--------------+-------------------------------------------+-----------+------------------------------
admin         | {}                                        | {}        | NULL
no_options    | {}                                        | {}        | NULL
root          | {}                                        | {admin}   | NULL
with_password | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(4 rows)
```

### Prevent a user from using password authentication

The following statement prevents the user from using password authentication and mandates certificate-based client authentication:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER no_password WITH PASSWORD NULL;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
  username    |                  options                  | member_of | estimated_last_login_time
--------------+-------------------------------------------+-----------+------------------------------
admin         | {}                                        | {}        | NULL
no_options    | {}                                        | {}        | NULL
no_password   | {}                                        | {}        | NULL
root          | {}                                        | {admin}   | NULL
with_password | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(5 rows)
```

### Create a user that can create other users and manage authentication methods for the new users

The following example allows the user to [create other users](/docs/stable/create-user.md) and [manage authentication methods](/docs/stable/authentication.md#client-authentication) for them:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER can_create_users WITH CREATEROLE CREATELOGIN;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
    username     |                  options                  | member_of | estimated_last_login_time
-----------------+-------------------------------------------+-----------+------------------------------
admin            | {}                                        | {}        | NULL
can_create_users | {CREATELOGIN,CREATEROLE}                  | {}        | NULL
no_options       | {}                                        | {}        | NULL
no_password      | {}                                        | {}        | NULL
root             | {}                                        | {admin}   | NULL
with_password    | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(6 rows)
```

### Create a user that can create and rename databases

The following example allows the user to [create](/docs/stable/create-database.md) or [rename](/docs/stable/alter-database.md#rename-to) databases:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER can_create_db WITH CREATEDB;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
      username        |                  options                  | member_of | estimated_last_login_time
----------------------+-------------------------------------------+-----------+------------------------------
admin                 | {}                                        | {}        | NULL
can_create_db         | {CREATEDB}                                | {}        | NULL
can_create_users      | {CREATELOGIN,CREATEROLE}                  | {}        | NULL
no_options            | {}                                        | {}        | NULL
no_password           | {}                                        | {}        | NULL
root                  | {}                                        | {admin}   | NULL
with_password         | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(7 rows)
```

### Create a user that can pause, resume, and cancel non-admin jobs

The following example allows the user to [pause](/docs/stable/pause-job.md), [resume](/docs/stable/resume-job.md), and [cancel](/docs/stable/cancel-job.md) jobs:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER can_control_job WITH CONTROLJOB;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
      username        |                  options                  | member_of | estimated_last_login_time
----------------------+-------------------------------------------+-----------+------------------------------
admin                 | {}                                        | {}        | NULL
can_control_job       | {CONTROLJOB}                              | {}        | NULL
can_create_db         | {CREATEDB}                                | {}        | NULL
can_create_users      | {CREATELOGIN,CREATEROLE}                  | {}        | NULL
no_options            | {}                                        | {}        | NULL
no_password           | {}                                        | {}        | NULL
root                  | {}                                        | {admin}   | NULL
with_password         | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(8 rows)
```

### Create a user that can see and cancel non-admin queries and sessions

The following example allows the user to cancel [queries](/docs/stable/cancel-query.md) and [sessions](/docs/stable/cancel-session.md) for other non-`admin` roles:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER can_manage_queries WITH CANCELQUERY VIEWACTIVITY;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
      username        |                  options                  | member_of | estimated_last_login_time
----------------------+-------------------------------------------+-----------+------------------------------
admin                 | {}                                        | {}        | NULL
can_control_job       | {CONTROLJOB}                              | {}        | NULL
can_create_db         | {CREATEDB}                                | {}        | NULL
can_create_users      | {CREATELOGIN,CREATEROLE}                  | {}        | NULL
can_manage_queries    | {CANCELQUERY,VIEWACTIVITY}                | {}        | NULL
no_options            | {}                                        | {}        | NULL
no_password           | {}                                        | {}        | NULL
root                  | {}                                        | {admin}   | NULL
with_password         | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(9 rows)
```

### Create a user that can control changefeeds

The following example allows the user to run [`CREATE CHANGEFEED`](/docs/stable/create-changefeed):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER can_control_changefeed WITH CONTROLCHANGEFEED;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
       username        |                  options                  | member_of | estimated_last_login_time
-----------------------+-------------------------------------------+-----------+------------------------------
admin                  | {}                                        | {}        | NULL
can_control_changefeed | {CONTROLCHANGEFEED}                       | {}        | NULL
can_control_job        | {CONTROLJOB}                              | {}        | NULL
can_create_db          | {CREATEDB}                                | {}        | NULL
can_create_users       | {CREATELOGIN,CREATEROLE}                  | {}        | NULL
can_manage_queries     | {CANCELQUERY,VIEWACTIVITY}                | {}        | NULL
no_options             | {}                                        | {}        | NULL
no_password            | {}                                        | {}        | NULL
root                   | {}                                        | {admin}   | NULL
with_password          | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(10 rows)
```

### Create a user that can modify cluster settings

The following example allows the user to modify [cluster settings](/docs/stable/cluster-settings.md):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE USER can_modify_cluster_setting WITH MODIFYCLUSTERSETTING;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW USERS;
```

```
         username          |                  options                  | member_of | estimated_last_login_time
---------------------------+-------------------------------------------+-----------+------------------------------
admin                      | {}                                        | {}        | NULL
can_control_changefeed     | {CONTROLCHANGEFEED}                       | {}        | NULL
can_control_job            | {CONTROLJOB}                              | {}        | NULL
can_create_db              | {CREATEDB}                                | {}        | NULL
can_create_users           | {CREATELOGIN,CREATEROLE}                  | {}        | NULL
can_manage_queries         | {CANCELQUERY,VIEWACTIVITY}                | {}        | NULL
can_modify_cluster_setting | {MODIFYCLUSTERSETTING}                    | {}        | NULL
no_options                 | {}                                        | {}        | NULL
no_password                | {}                                        | {}        | NULL
root                       | {}                                        | {admin}   | NULL
with_password              | {VALID UNTIL=2025-10-10 00:00:00+00:00}   | {}        | NULL
(11 rows)
```

### Create debug\_user for diagnostics (Preview)

The `debug_user` is a special privileged user for collecting [`cockroach debug zip`](/docs/stable/cockroach-debug-zip) and [`cockroach debug tsdump`](/docs/stable/cockroach-debug-tsdump) data. For complete setup including certificate generation and server configuration, see [Disable root login and use debug\_user](/docs/stable/security-reference/authentication.md#disable-root-login-and-use-debug_user).

Basic user creation:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE USER debug_user;
```

For SQL privilege requirements (needed for debug zip only, not tsdump), see Step 1 in the [debug\_user setup procedure](/docs/stable/security-reference/authentication.md#step-1-create-debug_user).

## See also

* [Authorization](/docs/stable/authorization.md)
* [`ALTER USER`](/docs/stable/alter-user)
* [`DROP USER`](/docs/stable/drop-user)
* [`SHOW USERS`](/docs/stable/show-users)
* [`GRANT`](/docs/stable/grant)
* [`SHOW GRANTS`](/docs/stable/show-grants)
* [Create Security Certificates](/docs/stable/cockroach-cert.md)
* [SQL Statements](/docs/stable/sql-statements.md)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)