> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# CREATE ROLE
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `CREATE ROLE` statement creates SQL roles, which are groups containing any number of roles and users as members. You can assign privileges to roles, and all members of the role (regardless of whether if they are direct or indirect members) will inherit the role's privileges.
You can use the keywords `ROLE` and `USER` interchangeably. `CREATE USER` is equivalent to `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`.
The \`\` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
## Considerations
* After creating a role, you must grant it privileges to databases and tables.
* All privileges of a role are inherited by all of its members.
* Users and roles can be members of roles.
* Role options of a role are not inherited by any of its members.
* There is no limit to the number of members in a role.
* Membership loops are not allowed (direct: `A is a member of B is a member of A` or indirect: `A is a member of B is a member of C ... is a member of A`).
## Required privileges
Unless a role is a member of the `admin` role, additional [privileges](#parameters) are required to manage other roles.
* To create other roles, a role must have the [`CREATEROLE`](#create-a-role-that-can-create-other-roles-and-manage-authentication-methods-for-the-new-roles) [role option](#role-options).
* To add the `LOGIN` capability for other roles so that they can log in as users, a role must also have the [`CREATELOGIN`](#create-a-role-that-can-create-other-roles-and-manage-authentication-methods-for-the-new-roles) role option.
* To be able to grant or revoke membership to a role for additional roles, a member of the role must be set as a role admin for that role.
## Synopsis
## Parameters
| Parameter | Description |
| ------------------ | ----------------------------------------------- |
| `name` | The name of the role to create. |
| `WITH role_option` | Apply a [role option](#role-options) to a role. |
### Role names
* Are case-insensitive.
* Must start with either 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.
* 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.** Allow or disallow a role to cancel queries and sessions 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.** Allow or disallow a role to run `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, resume, and cancel 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 or rename 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, alter, and drop 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. 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 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. A password should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier.
To prevent a role from using password authentication and to mandate certificate-based client authentication, set the password as `NULL`. |
| `SQLLOGIN`/`NOSQLLOGIN` | **Deprecated in v22.2: Use the `NOSQLLOGIN` system privilege.** Allow or disallow a role to log in using the SQL CLI with one of the client authentication methods. 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` format) after which the [password](#parameters) is not valid. |
| `VIEWACTIVITY`/`NOVIEWACTIVITY` | **Deprecated in v22.2: Use the `VIEWACTIVITY` system privilege.** 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. `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.** Allow or disallow a role to view the cluster settings with `SHOW CLUSTER SETTING` or to access the **Cluster Settings** 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.** 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 for other users when using the `listSessions` endpoint through the 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. |
## Examples
To run the following examples, start a secure single-node cluster 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 ROLES;
```
```
username | options | member_of
---------+---------+------------
admin | | {}
root | | {admin}
(2 rows)
```
The following statements are run by the `root` user that is a member of the `admin` role and has `ALL` privileges.
### Create a role
Role names 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.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE no_options;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
----------+---------+------------
admin | | {}
no_options | NOLOGIN | {}
root | | {admin}
(3 rows)
```
After creating roles, you must grant them privileges to databases.
### Create a role that can log in to the database
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_login WITH LOGIN PASSWORD '$tr0nGpassW0rD' VALID UNTIL '2021-10-10';
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
-----------+---------------------------------------+------------
admin | | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options | NOLOGIN | {}
root | | {admin}
(4 rows)
```
### Prevent a role from using password authentication
The following statement prevents the role from using password authentication and mandates certificate-based client authentication:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE ROLE no_password WITH PASSWORD NULL;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
-----------+---------------------------------------+------------
admin | | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options | NOLOGIN | {}
no_password| NOLOGIN | {}
root | | {admin}
(5 rows)
```
### Create a role that can create other roles and manage authentication methods for the new roles
The following example allows the role to create other users and manage authentication methods for them:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_create_role WITH CREATEROLE CREATELOGIN;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
----------------+---------------------------------------+------------
admin | | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options | NOLOGIN | {}
no_password | NOLOGIN | {}
root | | {admin}
(6 rows)
```
### Create a role that can create and rename databases
The following example allows the role to create or rename databases:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_create_db WITH CREATEDB;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
----------------------+---------------------------------------+------------
admin | | {}
can_create_db | CREATEDB, NOLOGIN | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
no_options | NOLOGIN | {}
no_password | NOLOGIN | {}
root | | {admin}
(7 rows)
```
### Create a role that can pause, resume, and cancel non-admin jobs
The following example allows the role to pause, resume, and cancel jobs:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_control_job WITH CONTROLJOB;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
----------------------+---------------------------------------+------------
admin | | {}
can_control_job | CONTROLJOB, NOLOGIN | {}
can_create_db | CREATEDB, NOLOGIN | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
manage_auth_for_roles | CREATELOGIN, NOLOGIN | {}
no_options | NOLOGIN | {}
no_password | NOLOGIN | {}
root | | {admin}
(8 rows)
```
### Create a role that can see and cancel non-admin queries and sessions
The following example allows the role to cancel queries and sessions for other non-`admin` roles:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_manage_queries WITH CANCELQUERY VIEWACTIVITY;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
----------------------+---------------------------------------+------------
admin | | {}
can_control_job | CONTROLJOB, NOLOGIN | {}
can_create_db | CREATEDB, NOLOGIN | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
can_manage_queries | CANCELQUERY, NOLOGIN, VIEWACTIVITY | {}
no_options | NOLOGIN | {}
no_password | NOLOGIN | {}
root | | {admin}
(9 rows)
```
### Create a role that can control changefeeds
The following example allows the role to run `CREATE CHANGEFEED`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_control_changefeed WITH CONTROLCHANGEFEED;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
-----------------------+---------------------------------------+------------
admin | | {}
can_control_changefeed | CONTROLCHANGEFEED, NOLOGIN | {}
can_control_job | CONTROLJOB, NOLOGIN | {}
can_create_db | CREATEDB, NOLOGIN | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
can_manage_queries | CANCELQUERY, NOLOGIN, VIEWACTIVITY | {}
no_options | NOLOGIN | {}
no_password | NOLOGIN | {}
root | | {admin}
(10 rows)
```
### Create a role that can modify cluster settings
The following example allows the role to modify cluster settings:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> CREATE ROLE can_modify_cluster_setting WITH MODIFYCLUSTERSETTING;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> SHOW ROLES;
```
```
username | options | member_of
---------------------------+---------------------------------------+------------
admin | | {}
can_control_changefeed | CONTROLCHANGEFEED, NOLOGIN | {}
can_control_job | CONTROLJOB, NOLOGIN | {}
can_create_db | CREATEDB, NOLOGIN | {}
can_create_role | CREATELOGIN, CREATEROLE, NOLOGIN | {}
can_login | VALID UNTIL=2021-10-10 00:00:00+00:00 | {}
can_manage_queries | CANCELQUERY, NOLOGIN, VIEWACTIVITY | {}
can_modify_cluster_setting | MODIFYCLUSTERSETTING, NOLOGIN | {}
no_options | NOLOGIN | {}
no_password | NOLOGIN | {}
root | | {admin}
(11 rows)
```
## See also
* Authorization
* Authorization Best Practices
* `ALTER ROLE`
* `DROP ROLE`
* `GRANT`
* `REVOKE`
* `SHOW ROLES`
* `SHOW USERS`
* `SHOW GRANTS`
* SQL Statements
* Online Schema Changes