> ## 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](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) creates SQL users, which let you control [privileges](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) on your databases and tables. You can use the keywords `ROLE` and `USER` interchangeably. `CREATE USER` is equivalent to [`CREATE ROLE`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes). ## Considerations * After creating users, you must [grant them privileges to databases and tables](https://docs.cockroachlabs.com/docs/v24.2/grant). * All users belong to the `public` role, to which you can [grant](https://docs.cockroachlabs.com/docs/v24.2/grant) and [revoke](https://docs.cockroachlabs.com/docs/v24.2/revoke) privileges. * On secure clusters, you must [create client certificates for users](https://docs.cockroachlabs.com/docs/v24.2/cockroach-cert#create-the-certificate-and-key-pair-for-a-client) 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 [`CREATE ROLE`: Synopsis](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/system-catalogs). * 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` [system privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to cancel [queries](https://docs.cockroachlabs.com/docs/v24.2/cancel-query) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/cancel-session) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to run [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/pause-job), [resume](https://docs.cockroachlabs.com/docs/v24.2/resume-job), and [cancel](https://docs.cockroachlabs.com/docs/v24.2/cancel-job) 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](https://docs.cockroachlabs.com/docs/v24.2/create-database) or [rename](https://docs.cockroachlabs.com/docs/v24.2/alter-database#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](https://docs.cockroachlabs.com/docs/v24.2/create-role), alter, and [drop](https://docs.cockroachlabs.com/docs/v24.2/drop-role) 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](https://docs.cockroachlabs.com/docs/v24.2/authentication#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](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings) 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](https://docs.cockroachlabs.com/docs/v24.2/authentication#client-authentication). A password should be entered as a [string literal](https://docs.cockroachlabs.com/docs/v24.2/sql-constants#string-literals). For compatibility with PostgreSQL, a password can also be entered as an identifier. To prevent a role from using [password authentication](https://docs.cockroachlabs.com/docs/v24.2/authentication#client-authentication) and to mandate [certificate-based client authentication](https://docs.cockroachlabs.com/docs/v24.2/authentication#client-authentication), [set the password as `NULL`](https://docs.cockroachlabs.com/docs/v24.2/create-role#prevent-a-role-from-using-password-authentication). | | `SQLLOGIN`/`NOSQLLOGIN` | **Deprecated in v22.2: Use the `NOSQLLOGIN` [system privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to log in using the SQL CLI with one of the [client authentication methods](https://docs.cockroachlabs.com/docs/v24.2/authentication#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`](https://docs.cockroachlabs.com/docs/v24.2/timestamp) format) after which the [password](#parameters) is not valid. | | `VIEWACTIVITY`/`NOVIEWACTIVITY` | **Deprecated in v22.2: Use the `VIEWACTIVITY` [system privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to see other roles' [queries](https://docs.cockroachlabs.com/docs/v24.2/show-statements) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/show-sessions) using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the [**Statements**](https://docs.cockroachlabs.com/docs/v24.2/ui-statements-page) and [**Transactions**](https://docs.cockroachlabs.com/docs/v24.2/ui-transactions-page) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to view the [cluster settings](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings) with `SHOW CLUSTER SETTING` or to access the [**Cluster Settings**](https://docs.cockroachlabs.com/docs/v24.2/ui-debug-pages) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#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](https://docs.cockroachlabs.com/docs/v24.2/sql-constants) for other users when using the `listSessions` endpoint through the [Cluster API](https://docs.cockroachlabs.com/docs/v24.2/cluster-api). 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](https://docs.cockroachlabs.com/docs/v24.2/authentication#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`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/gssapi_authentication). ## Examples To run the following examples, [start a secure single-node cluster](https://docs.cockroachlabs.com/docs/v24.2/cockroach-start-single-node) 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. ### 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: * [Grant them privileges to databases](https://docs.cockroachlabs.com/docs/v24.2/grant). * For secure clusters, you must also [create their client certificates](https://docs.cockroachlabs.com/docs/v24.2/cockroach-cert#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 --------------+---------------------------------------+------------ 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 [create other users](https://docs.cockroachlabs.com/docs/v24.2/create-user) and [manage authentication methods](https://docs.cockroachlabs.com/docs/v24.2/authentication#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 -----------------+---------------------------------------+------------ 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 [create](https://docs.cockroachlabs.com/docs/v24.2/create-database) or [rename](https://docs.cockroachlabs.com/docs/v24.2/alter-database#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 ----------------------+---------------------------------------+------------ 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 cancel [queries](https://docs.cockroachlabs.com/docs/v24.2/cancel-query) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/cancel-session) for other non-`admin` roles: The following example allows the user to [pause](https://docs.cockroachlabs.com/docs/v24.2/pause-job), [resume](https://docs.cockroachlabs.com/docs/v24.2/resume-job), and [cancel](https://docs.cockroachlabs.com/docs/v24.2/cancel-job) 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 [queries](https://docs.cockroachlabs.com/docs/v24.2/cancel-query) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/cancel-session) 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 [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/v24.2/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 -----------------------+---------------------------------------+------------ 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 [cluster settings](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings): ```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 * [Authorization](https://docs.cockroachlabs.com/docs/v24.2/authorization) * [`ALTER USER`](https://docs.cockroachlabs.com/docs/v24.2/alter-user) * [`DROP USER`](https://docs.cockroachlabs.com/docs/v24.2/drop-user) * [`SHOW USERS`](https://docs.cockroachlabs.com/docs/v24.2/show-users) * [`GRANT`](https://docs.cockroachlabs.com/docs/v24.2/grant) * [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/v24.2/show-grants) * [Create Security Certificates](https://docs.cockroachlabs.com/docs/v24.2/cockroach-cert) * [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) * [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)