CockroachDB Cloud requires you to create SQL users to access the cluster.
By default, a new SQL user created using a Console Admin is assigned to the admin role. An admin SQL user has full privileges for all databases and tables in your cluster. This user can also create additional users and grant them appropriate privileges.
Create a SQL user
Only Console Admins can create SQL users. If you are a Developer, you need to ask your Console Admin for the credentials of a SQL user to access the cluster. To find out who your Console Admin is, log in and navigate to Cluster Overview > Access.
Once you are logged in, you can use the Console to create a new user:
- Navigate to your cluster's SQL Users page.
Click the Add User button in the top right corner.
The Create SQL user modal displays.
Enter a Username.
Click Generate & save password.
Copy the generated password and save it in a secure location.
Currently, all new users are created with full privileges. For more information and to change the default settings, see Grant privileges and Use roles.
Once you have connected to the cluster's SQL client, you can create a new user.
To create a new user, use the CREATE USER ... WITH PASSWORD statement:
> CREATE USER <username> WITH PASSWORD '<password>';
Be sure to create a password for each new user. Without a password, a user cannot connect to the cluster or access the DB Console. To add or change a password for a user, use the ALTER USER statement.
Manage SQL users
View all users in your cluster
To view a list of all of the users in your cluster, navigate to the SQL Users page.
On the SQL Users page, you can do the following:
To list all the users in your cluster, use the SHOW USERS statement:
> SHOW USERS;
Change a user's password
Only Console Admins can change a user's password. If you are a Developer, you need to ask your Console Admin to change the password. To find out who your Console Admin is, log in and navigate to Cluster Overview > Access.
To change a user's password:
- Navigate to the SQL Users page.
In the row of the user whose password needs to be changed, click the ... button.
From the dropdown, select Change Password.
Click Generate & save password.
Copy the generated password and save it in a secure location.
To change a user's password, use the ALTER USER statement:
> ALTER USER <user> WITH PASSWORD '<new password>';
Remove a user
To remove a user:
- Navigate to the SQL Users page.
In the row of the user you want to remove, click the ... button.
From the dropdown, select Delete SQL User.
Click Delete.
To remove a user, use the DROP USER statement:
> DROP USER <user>;
All of a user's privileges must be revoked before the user can be dropped.
Grant privileges
Access to the data in your cluster is controlled by privileges. When a user connects to a database, either via the CockroachDB SQL client or a PostgreSQL driver or ORM, CockroachDB checks the user's privileges for each statement executed. If the user does not have sufficient privileges for a statement, CockroachDB returns an error.
To grant a user privileges for specific databases and tables in your cluster, use the GRANT statement. For example, to assign a user all privileges for all tables in a database:
> GRANT ALL ON DATABASE <database> TO <user>;
To assign a user more limited privileges for one table in a database:
> GRANT SELECT, INSERT ON TABLE <database>.<table> TO <user>;
For more details, see Privileges and GRANT.
Manage privileges
View a user's privileges
To show privileges granted to a user, use the SHOW GRANTS statement:
> SHOW GRANTS ON DATABASE <database> FOR <user>;
Revoke a user's privileges
To revoke privileges from a user, use the REVOKE statement:
> REVOKE INSERT ON TABLE <database>.<table> FROM <user>;
Use roles
Role-based access control lets you simplify how you manage privileges. In essence, a role is a group containing any number of other roles and users as members. You can assign privileges to a role, and all direct and indirect members of the role will inherit the privileges.
Once you have connected to the cluster, you can set up roles:
To create a role, use the
CREATE ROLEstatement:> CREATE ROLE <role>;To grant privileges to a role, use the
GRANT <privilege>statement:> GRANT <privilege> ON DATABASE <database> TO <role>;To add a user (or another role) to a role, use the
GRANT <role>statement:> GRANT <role> TO <user or role>;To revoke privileges from a role, use the
REVOKE <privilege>statement:> REVOKE INSERT ON TABLE <database>.<table> FROM <role>;To remove a user (or another role) from a role, use the
REVOKE <role>statement:> REVOKE <role> FROM <user or role>;To list all roles in your cluster, use the
SHOW ROLESstatement:> SHOW ROLES;To remove a role, use the
DROP ROLEstatement:> DROP ROLE <role>;Note:All of a role's privileges must be revoked before the role can be dropped.
See also
Was this helpful?
