Skip to content

Latest commit

 

History

History
263 lines (183 loc) · 10 KB

user-authorization.md

File metadata and controls

263 lines (183 loc) · 10 KB
title summary toc docs_area
User Authorization
Learn about the user authorization features for CockroachDB Cloud clusters.
true
manage

{{ site.data.products.db }} 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

Use the {{ site.data.products.db }} Console Use the SQL client

{% include cockroachcloud/cockroachcloud-ask-admin.md %}

Once you are logged in, you can use the Console to create a new user:

  1. Navigate to your cluster's SQL Users page.

  2. Click the Add User button in the top right corner.

    The Create SQL user modal displays.

  3. Enter a Username.

  4. Click Generate & save password.

  5. 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](connect-to-your-cluster.html), you can create a new user.

To create a new user, use the CREATE USER ... WITH PASSWORD statement:

{% include_cached copy-clipboard.html %}

> CREATE USER <username> WITH PASSWORD '<password>';

{{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

Manage SQL users

View all users in your cluster

Use the {{ site.data.products.db }} Console Use the SQL client

To view a list of all of the users in your cluster, navigate to the **SQL Users** page. 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`](../{{site.versions["cloud"]}}/show-users.html) statement:

{% include_cached copy-clipboard.html %}

> SHOW USERS;

Change a user's password

Use the {{ site.data.products.db }} Console Use the SQL client

{{site.data.alerts.callout_info}} Only [Console Admins](console-access-management.html#console-admin) can change a user's password. If you are a [Developer](console-access-management.html#developer), you need to ask your Console Admin to change the password. To find out who your Console Admin is, [log in](https://cockroachlabs.cloud/) and navigate to **Cluster Overview** > **Access**. {{site.data.alerts.end}}

To change a user's password:

  1. Navigate to the SQL Users page.

  2. In the row of the user whose password needs to be changed, click the ... button.

    Change SQL password

  3. From the dropdown, select Change Password.

  4. Click Generate & save password.

  5. Copy the generated password and save it in a secure location.

To change a user's password, use the [`ALTER USER`](../{{site.versions["cloud"]}}/alter-user.html) statement:

{% include_cached copy-clipboard.html %}

> ALTER USER <user> WITH PASSWORD '<new password>';

Remove a user

Use the {{ site.data.products.db }} Console Use the SQL client

To remove a user:

  1. Navigate to the SQL Users page.

  2. In the row of the user you want to remove, click the ... button.

    Remove a SQL users

  3. From the dropdown, select Delete SQL User.

  4. Click Delete.

To remove a user, use the [`DROP USER`](../{{site.versions["cloud"]}}/drop-user.html) statement:

{% include_cached copy-clipboard.html %}

> DROP USER <user>;

{{site.data.alerts.callout_info}} All of a user's privileges must be revoked before the user can be dropped. {{site.data.alerts.end}}

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:

{% include_cached copy-clipboard.html %}

> GRANT ALL ON DATABASE <database> TO <user>;

To assign a user more limited privileges for one table in a database:

{% include_cached copy-clipboard.html %}

> 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:

{% include_cached copy-clipboard.html %}

> SHOW GRANTS ON DATABASE <database> FOR <user>;

Revoke a user's privileges

To revoke privileges from a user, use the REVOKE statement:

{% include_cached copy-clipboard.html %}

> 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 ROLE statement:

    {% include_cached copy-clipboard.html %}

    > CREATE ROLE <role>;

  • To grant privileges to a role, use the GRANT <privilege> statement:

    {% include_cached copy-clipboard.html %}

    > GRANT <privilege> ON DATABASE <database> TO <role>;

  • To add a user (or another role) to a role, use the GRANT <role> statement:

    {% include_cached copy-clipboard.html %}

    > GRANT <role> TO <user or role>;

  • To revoke privileges from a role, use the REVOKE <privilege> statement:

    {% include_cached copy-clipboard.html %}

    > REVOKE INSERT ON TABLE <database>.<table> FROM <role>;

  • To remove a user (or another role) from a role, use the REVOKE <role> statement:

    {% include_cached copy-clipboard.html %}

    > REVOKE <role> FROM <user or role>;

  • To list all roles in your cluster, use the SHOW ROLES statement:

    {% include_cached copy-clipboard.html %}

    > SHOW ROLES;

  • To remove a role, use the DROP ROLE statement:

    {% include_cached copy-clipboard.html %}

    > DROP ROLE <role>;

    {{site.data.alerts.callout_info}} All of a role's privileges must be revoked before the role can be dropped. {{site.data.alerts.end}}

See also