CREATE USER

On this page

The CREATE USER statement creates SQL users, which let you control privileges on your databases and tables.

Considerations

• Usernames:
  • Are case-insensitive
  • Must start with a letter, number, or underscore
  • Must contain only letters, numbers, periods, or underscores
  • Must be between 1 and 63 characters.
• After creating users, you must grant them privileges to databases and tables.
• All users belong to the public role, to which you can grant and revoke privileges.
• On secure clusters, you must create client certificates for users and users must authenticate their access to the cluster.

Required privileges

New in v20.1: To create other users, the user must have the CREATEROLE parameter set.

Synopsis

Parameters

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, which is available to all users. To ensure the highest level of security, we recommend only using client certificate and key authentication.

• Password authentication, 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 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, which is available to Enterprise users.

Examples

Create a user

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

> CREATE USER carl;

After creating users, you must:

• Grant them privileges to databases.
• For secure clusters, you must also create their client certificates.

Allow the user to create other users

> CREATE USER carl with CREATEROLE;

Create a user with a password

> CREATE USER carl WITH PASSWORD 'ilov3beefjerky';

CREATE USER 1

Create a user with a password using an identifier

The following statement changes the password to ilov3beefjerky, as above:

> CREATE USER carl WITH PASSWORD ilov3beefjerky;

This is equivalent to the example in the previous section because the password contains only lowercase characters.

In contrast, the following statement changes the password to thereisnotomorrow, even though the password in the syntax contains capitals, because identifiers are normalized automatically:

> CREATE USER carl WITH PASSWORD ThereIsNoTomorrow;

To preserve case in a password specified using identifier syntax, use double quotes:

> CREATE USER carl WITH PASSWORD "ThereIsNoTomorrow";

Prevent a user from using password authentication

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

> CREATE USER carl WITH PASSWORD NULL;

Set password validity

The following statement sets the date and time after which the password is not valid:

> CREATE USER carl VALID UNTIL '2021-01-01';

Manage users

After creating a user, you can use the ALTER USER statement to add or change the user's password, update role options, and the DROP USER statement to the remove users.

Authenticate as a specific user

$ cockroach sql --user=carl

$ cockroach sql --user=carl

$ cockroach sql --insecure --user=carl

Set login privileges for a user

The following statement prevents the user from logging in using any user authentication method:

> CREATE USER carl NOLOGIN;

> SHOW USERS;

  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     | NOLOGIN    | {}
  root     | CREATEROLE | {admin}
(3 rows)

To allow the user to log in using one of the user authentication methods, use the ALTER USER statement:

> ALTER USER carl LOGIN;

> SHOW USERS;

  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     |            | {}
  root     | CREATEROLE | {admin}
(3 rows)

See also

• Authorization
• ALTER USER
• DROP USER
• SHOW USERS
• GRANT
• SHOW GRANTS
• Create Security Certificates
• Other SQL Statements

Was this helpful?