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

# CREATE USER

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `CREATE USER` <InternalLink path="sql-statements">statement</InternalLink> creates SQL users, which let you control <InternalLink path="security-reference/authorization#managing-privileges">privileges</InternalLink> on your databases and tables.

You can use the keywords `ROLE` and `USER` interchangeably. `CREATE USER` is equivalent to <InternalLink path="create-role">`CREATE ROLE`</InternalLink>, 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 \`\` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>.
</Note>

## Considerations

* After creating users, you must <InternalLink path="grant">grant them privileges to databases and tables</InternalLink>.
* All users belong to the `public` role, to which you can <InternalLink path="grant">grant</InternalLink> and <InternalLink path="revoke">revoke</InternalLink> privileges.
* On secure clusters, you must <InternalLink path="cockroach-cert#create-the-certificate-and-key-pair-for-a-client">create client certificates for users</InternalLink> and users must [authenticate their access to the cluster](#user-authentication).

## 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 <InternalLink path="create-role#synopsis">`CREATE ROLE`: Synopsis</InternalLink>.

## 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 <InternalLink path="system-catalogs">system catalogs</InternalLink>.
* User and role names share the same namespace and must be unique.

### Role options

| Role option                                     | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| ----------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `CANCELQUERY`/`NOCANCELQUERY`                   | **Deprecated in v22.2: Use the `CANCELQUERY` <InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to cancel <InternalLink path="cancel-query">queries</InternalLink> and <InternalLink path="cancel-session">sessions</InternalLink> 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. <br /><br />By default, the role option is set to `NOCANCELQUERY` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                               |
| `CONTROLCHANGEFEED`/`NOCONTROLCHANGEFEED`       | **Deprecated in v23.1: Use the `CHANGEFEED` <InternalLink path="security-reference/authorization#supported-privileges">privilege</InternalLink>.** Allow or disallow a role to run <InternalLink path="create-changefeed">`CREATE CHANGEFEED`</InternalLink> on tables they have `SELECT` privileges on. <br /><br />By default, the role option is set to `NOCONTROLCHANGEFEED` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `CONTROLJOB`/`NOCONTROLJOB`                     | Allow or disallow a role to <InternalLink path="pause-job">pause</InternalLink>, <InternalLink path="resume-job">resume</InternalLink>, and <InternalLink path="cancel-job">cancel</InternalLink> jobs. Non-`admin` roles cannot control jobs created by `admin` roles. <br /><br />By default, the role option is set to `NOCONTROLJOB` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `CREATEDB`/`NOCREATEDB`                         | Allow or disallow a role to <InternalLink path="create-database">create</InternalLink> or <InternalLink path="alter-database#rename-to">rename</InternalLink> a database. The role is assigned as the owner of the database. <br /><br />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. <br /><br />By default, the role option is set to `NOCREATELOGIN` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `CREATEROLE`/`NOCREATEROLE`                     | Allow or disallow the new role to <InternalLink path="create-role">create</InternalLink>, alter, and <InternalLink path="drop-role">drop</InternalLink> other non-`admin` roles. <br /><br />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 <InternalLink path="authentication#client-authentication">client authentication methods</InternalLink>. 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 <InternalLink path="cluster-settings">cluster settings</InternalLink> with the `sql.defaults` prefix. <br /><br />By default, the role option is set to `NOMODIFYCLUSTERSETTING` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `PASSWORD password`/`PASSWORD NULL`             | The credential the role uses to <InternalLink path="authentication#client-authentication">authenticate their access to a secure cluster</InternalLink>. A password should be entered as a <InternalLink path="sql-constants#string-literals">string literal</InternalLink>. For compatibility with PostgreSQL, a password can also be entered as an identifier. <br /><br />To prevent a role from using <InternalLink path="authentication#client-authentication">password authentication</InternalLink> and to mandate <InternalLink path="authentication#client-authentication">certificate-based client authentication</InternalLink>, <InternalLink path="create-role#prevent-a-role-from-using-password-authentication">set the password as `NULL`</InternalLink>.                                                                                                                                                                                                                                                                                                                                                                                                 |
| `SQLLOGIN`/`NOSQLLOGIN`                         | **Deprecated in v22.2: Use the `NOSQLLOGIN` <InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to log in using the SQL CLI with one of the <InternalLink path="authentication#client-authentication">client authentication methods</InternalLink>. 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. <br /><br />Without any role options all login behavior is permitted.                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `VALID UNTIL`                                   | The date and time (in the <InternalLink path="timestamp">`timestamp`</InternalLink> format) after which the [password](#parameters) is not valid.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `VIEWACTIVITY`/`NOVIEWACTIVITY`                 | **Deprecated in v22.2: Use the `VIEWACTIVITY` <InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to see other roles' <InternalLink path="show-statements">queries</InternalLink> and <InternalLink path="show-sessions">sessions</InternalLink> using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the <InternalLink path="ui-statements-page">**Statements**</InternalLink> and <InternalLink path="ui-transactions-page">**Transactions**</InternalLink> 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.<br /><br />By default, the role option is set to `NOVIEWACTIVITY` for all non-`admin` roles.                                                                                                                                                                                                                                                                      |
| `VIEWCLUSTERSETTING` / `NOVIEWCLUSTERSETTING`   | **Deprecated in v22.2: Use the `VIEWCLUSTERSETTING` <InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to view the <InternalLink path="cluster-settings">cluster settings</InternalLink> with `SHOW CLUSTER SETTING` or to access the <InternalLink path="ui-debug-pages">**Cluster Settings**</InternalLink> page in the DB Console. <br /><br />By default, the role option is set to `NOVIEWCLUSTERSETTING` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `VIEWACTIVITYREDACTED`/`NOVIEWACTIVITYREDACTED` | **Deprecated in v22.2: Use the `VIEWACTIVITYREDACTED` <InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** 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 <InternalLink path="sql-constants">constants</InternalLink> for other users when using the `listSessions` endpoint through the <InternalLink path="cluster-api">Cluster API</InternalLink>. 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. <br /><br />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:

* <InternalLink path="authentication#client-authentication">Client certificate and key authentication</InternalLink>, 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 <InternalLink path="alter-user">`ALTER USER`</InternalLink> 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.

* <InternalLink path="gssapi_authentication">GSSAPI authentication</InternalLink>.

## Examples

To run the following examples, <InternalLink path="cockroach-start-single-node">start a secure single-node cluster</InternalLink> 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
---------+---------+------------
admin    |         | {}
root     |         | {admin}
(2 rows)
```

<Note>
  The following statements are run by the `root` user that is a member of the `admin` role and has `ALL` privileges.
</Note>

### 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
-------------+---------+------------
admin      |         | {}
no_options |         | {}
root       |         | {admin}
(3 rows)
```

After creating users, you must:

* <InternalLink path="grant">Grant them privileges to databases</InternalLink>.
* For secure clusters, you must also <InternalLink path="cockroach-cert#create-the-certificate-and-key-pair-for-a-client">create their client certificates</InternalLink>.

### 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
--------------+---------------------------------------+------------
admin         |                                       | {}
no_options    |                                       | {}
root          |                                       | {admin}
with_password | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(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
--------------+---------------------------------------+------------
admin         |                                       | {}
no_options    |                                       | {}
no_password   |                                       | {}
root          |                                       | {admin}
with_password | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(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 <InternalLink path="create-user">create other users</InternalLink> and <InternalLink path="authentication#client-authentication">manage authentication methods</InternalLink> 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
-----------------+---------------------------------------+------------
admin            |                                       | {}
can_create_users | CREATELOGIN, CREATEROLE               | {}
no_options       |                                       | {}
no_password      |                                       | {}
root             |                                       | {admin}
with_password    | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(6 rows)
```

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

The following example allows the user to <InternalLink path="create-database">create</InternalLink> or <InternalLink path="alter-database#rename-to">rename</InternalLink> 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
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_create_db         | CREATEDB                              | {}
can_create_users      | CREATELOGIN, CREATEROLE               | {}
no_options            |                                       | {}
no_password           |                                       | {}
root                  |                                       | {admin}
with_password         | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(7 rows)
```

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

The following example allows the user to <InternalLink path="pause-job">pause</InternalLink>, <InternalLink path="resume-job">resume</InternalLink>, and <InternalLink path="cancel-job">cancel</InternalLink> 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
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_control_job       | CONTROLJOB                            | {}
can_create_db         | CREATEDB                              | {}
can_create_users      | CREATELOGIN, CREATEROLE               | {}
no_options            |                                       | {}
no_password           |                                       | {}
root                  |                                       | {admin}
with_password         | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(8 rows)
```

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

The following example allows the user to cancel <InternalLink path="cancel-query">queries</InternalLink> and <InternalLink path="cancel-session">sessions</InternalLink> 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
----------------------+---------------------------------------+------------
admin                 |                                       | {}
can_control_job       | CONTROLJOB                            | {}
can_create_db         | CREATEDB                              | {}
can_create_users      | CREATELOGIN, CREATEROLE               | {}
can_manage_queries    | CANCELQUERY, VIEWACTIVITY             | {}
no_options            |                                       | {}
no_password           |                                       | {}
root                  |                                       | {admin}
with_password         | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(9 rows)
```

### Create a user that can control changefeeds

The following example allows the user to run <InternalLink path="create-changefeed">`CREATE CHANGEFEED`</InternalLink>:

```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
-----------------------+---------------------------------------+------------
admin                  |                                       | {}
can_control_changefeed | CONTROLCHANGEFEED                     | {}
can_control_job        | CONTROLJOB                            | {}
can_create_db          | CREATEDB                              | {}
can_create_users       | CREATELOGIN, CREATEROLE               | {}
can_manage_queries     | CANCELQUERY, VIEWACTIVITY             | {}
no_options             |                                       | {}
no_password            |                                       | {}
root                   |                                       | {admin}
with_password          | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(10 rows)
```

### Create a user that can modify cluster settings

The following example allows the user to modify <InternalLink path="cluster-settings">cluster settings</InternalLink>:

```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
---------------------------+---------------------------------------+------------
admin                      |                                       | {}
can_control_changefeed     | CONTROLCHANGEFEED                     | {}
can_control_job            | CONTROLJOB                            | {}
can_create_db              | CREATEDB                              | {}
can_create_users           | CREATELOGIN, CREATEROLE               | {}
can_manage_queries         | CANCELQUERY, VIEWACTIVITY             | {}
can_modify_cluster_setting | MODIFYCLUSTERSETTING                  | {}
no_options                 |                                       | {}
no_password                |                                       | {}
root                       |                                       | {admin}
with_password              | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
(11 rows)
```

## See also

* <InternalLink path="authorization">Authorization</InternalLink>
* <InternalLink path="alter-user">`ALTER USER`</InternalLink>
* <InternalLink path="drop-user">`DROP USER`</InternalLink>
* <InternalLink path="show-users">`SHOW USERS`</InternalLink>
* <InternalLink path="grant">`GRANT`</InternalLink>
* <InternalLink path="show-grants">`SHOW GRANTS`</InternalLink>
* <InternalLink path="cockroach-cert">Create Security Certificates</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>
* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>