The CREATE USER
statement creates SQL users, which let you control privileges on your databases and tables.
cockroach user set
command to create and manage users.Considerations
- Usernames are case-insensitive; must start with either a letter or underscore; must contain only letters, numbers, or underscores; and must be between 1 and 63 characters.
- After creating users, you must grant them privileges to databases and tables.
- On secure clusters, you must create client certificates for users and users must authenticate their access to the cluster.
- Removing a user does not remove that user's privileges. Therefore, to prevent a future user with an identical username from inheriting an old user's privileges, it's important to revoke a user's privileges before or after removing the user.
Required Privileges
The user must have the INSERT
and UPDATE
privileges on the system.users
table.
Synopsis
Parameters
Parameter | Description |
---|---|
name |
The name of the user you want to create. Usernames are case-insensitive; must start with either a letter or underscore; must contain only letters, numbers, or underscores; and must be between 1 and 63 characters. |
password |
Let the user authenticate their access to a secure cluster using this password. Passwords must be entered as string values surrounded by single quotes (' ).You cannot set a password for the root user. For secure clusters, the root user must authenticate with a client certificate and key. |
User Authentication
Secure clusters require users to authenticate their access to databases and tables. CockroachDB offers two 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 non-
root
users who you've created passwords for. To create a user with a password, use theWITH PASSWORD
clause ofCREATE USER
. To add a password to an existing user, use thecockroach user
command.Users can use passwords to authenticate without supplying client certificates and keys; however, we recommend using certificate-based authentication whenever possible.
root
) through the WITH PASSWORD
clause.Examples
Create a User
> CREATE USER jpointsman;
Usernames are case-insensitive; must start with either a letter or underscore; must contain only letters, numbers, or underscores; and must be between 1 and 63 characters.
After creating users, you must grant them privileges to databases and tables.
For users on secure clusters, you also need to generate client certificates and keys to authenticate the user's access to the cluster.
Create a User with Password Authentication
> CREATE USER jpointsman WITH PASSWORD 'Q7gc8rEdS';
Manage Users
After creating users, you can manage them using the cockroach user
command.
Authenticate as a Specific User
Insecure Clusters
$ cockroach sql --insecure --user=jpointsman
Secure Clusters with Client Certificates
All users can authenticate their access to a secure cluster using a client certificate issued to their username.
$ cockroach sql --user=jpointsman
Secure Clusters with Passwords
Users with passwords can authenticate their access by entering their password at the command prompt instead of using their client certificate and key.
$ cockroach sql --user=jpointsman