> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # ALTER ROLE Use the `ALTER ROLE` [statement](/docs/stable/sql-statements.md) to add, change, or remove a [role's](/docs/stable/create-role.md) password, change the role options for a role, and set default [session variable](/docs/stable/set-vars.md) values for a role. You can use the keywords `ROLE` and `USER` interchangeably. [`ALTER USER`](/docs/stable/alter-user) is an alias for `ALTER ROLE`. ## Considerations ### Password management Password creation and alteration is supported only in secure clusters. ### Session variable precedence When a [session](/docs/stable/show-sessions.md) starts, CockroachDB determines the initial value of each [session variable](/docs/stable/session-variables.md) by evaluating the settings in the following order (items earlier in the list take precedence over later items): 1. [Connection string parameters](/docs/stable/connection-parameters.md): A value supplied as a query parameter in the connection URL (for example, `.../movr?sslmode=disable&timezone=UTC`). 2. [Per-role, per-database defaults](/docs/stable/alter-role.md#set-default-session-variable-values-for-a-role-in-a-specific-database): A value set by `ALTER ROLE {role_name} IN DATABASE {db_name} SET {var}={value}`. 3. [Per-role, all-database defaults](/docs/stable/alter-role.md#set-default-session-variable-values-for-a-role): A value set by `ALTER ROLE {role_name} SET {var}={value}`. 4. [All-role, per-database defaults](/docs/stable/alter-role.md#set-default-session-variable-values-for-a-specific-database): A value set by `ALTER ROLE ALL IN DATABASE {db_name} SET {var}={value}` or equivalently by `ALTER DATABASE {db_name} SET {var}={value}`. 5. [Cluster-wide defaults for all roles and all databases](/docs/stable/alter-role.md#set-default-session-variable-values-for-all-users): A value set by `ALTER ROLE ALL SET {var}={value}`. If a session variable is not modified using any of the preceding methods, the built-in system default value is used. Note that the [`root` user](/docs/stable/security-reference/authorization) is exempt from settings 3–5. The `root` user is only affected by values specified in the connection string. You can set session variables for the duration of a single transaction by using [`SET LOCAL {var}={value}`](/docs/stable/set-vars#set-a-variable-for-the-duration-of-a-single-transaction). You can also [override session variables](/docs/stable/cost-based-optimizer.md#override-session-variables) for executions of a specific statement fingerprint by using the [`information_schema.crdb_set_session_variable_hint()`](/docs/stable/functions-and-operators#system-repair-functions) function. > [!NOTE] > > Changes to defaults using the preceding methods only apply to **future** sessions. This is because session variable resolution happens at session start time. To change a default value in an existing open session, set the variable explicitly with [`SET`](/docs/stable/set-vars). ## Required privileges * To alter an [`admin` role](/docs/stable/security-reference/authorization#admin-role), the user must be a member of the `admin` role. * To alter other roles, the user must be a member of the `admin` role or have the [`CREATEROLE`](/docs/stable/create-role#create-a-role-that-can-create-other-roles-and-manage-authentication-methods-for-the-new-roles) [role option](#role-options). * For [per-database defaults](#set-default-session-variable-values-for-a-specific-database), the [owner](/docs/stable/security-reference/authorization.md#object-ownership) of a database can execute `ALTER ROLE ALL IN DATABASE ... {SET|RESET}` for that database (in addition to users who meet the general requirements above). ## Synopsis ![alter\_role syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/alter_role.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=e37b19de20bc2332232be55b8bba09fa) ## Parameters | Parameter | Description | | - | - | | `role_name` | The name of the role to alter. | | `WITH role_option` | Apply a [role option](#role-options) to the role. | | `SET {session variable}` | Set default [session variable](/docs/stable/set-vars.md) values for a role. | | `RESET {session variable}` `RESET ALL` | Reset one session variable or all session variables to the default value. | | `IN DATABASE database_name` | Specify a database for which to apply session variable defaults. When `IN DATABASE` is not specified, the default session variable values apply for a role in all databases. In order for a session to initialize session variable values to database defaults, the database must be specified as a [connection parameter](/docs/stable/connection-parameters.md). Database default values will not appear if the database is set after connection with `USE `/`SET database= [!NOTE] > > The following statements are run by the `root` user that is a member of the `admin` role and has `ALL` privileges. ### Allow a role to log in to the database using a password The following example allows a role to log in to the database with a [password](/docs/stable/authentication.md#client-authentication): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE ROLE carl; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH LOGIN PASSWORD 'An0ther$tr0nGpassW0rD' VALID UNTIL '2021-10-10'; ``` ### Prevent a role from using password authentication The following statement prevents the user from using password authentication and mandates certificate-based [client authentication](/docs/stable/authentication.md#client-authentication): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH PASSWORD NULL; ``` ### Allow a role to create other roles and manage authentication methods for the new roles The following example allows the role to [create other roles](/docs/stable/create-role.md) and [manage authentication methods](/docs/stable/authentication.md#client-authentication) for them: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH CREATEROLE CREATELOGIN; ``` ### Allow a role to create and rename databases The following example allows the role to [create](/docs/stable/create-database.md) or [rename](/docs/stable/alter-database.md#rename-to) databases: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH CREATEDB; ``` ### Allow a role to pause, resume, and cancel non-admin jobs The following example allows the role to [pause](/docs/stable/pause-job.md), [resume](/docs/stable/resume-job.md), and [cancel](/docs/stable/cancel-job.md) jobs: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH CONTROLJOB; ``` ### Allow a role to see and cancel non-admin queries and sessions The following example allows the role to cancel [queries](/docs/stable/cancel-query.md) and [sessions](/docs/stable/cancel-session.md) for other non-`admin` roles: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH CANCELQUERY VIEWACTIVITY; ``` ### Allow a role to control changefeeds The following example allows the role to run [`CREATE CHANGEFEED`](/docs/stable/create-changefeed): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH CONTROLCHANGEFEED; ``` ### Allow a role to modify cluster settings The following example allows the role to modify [cluster settings](/docs/stable/cluster-settings.md): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH MODIFYCLUSTERSETTING; ``` ### Allow a role to bypass row-level security (RLS) To allow a [role](/docs/stable/security-reference/authorization.md#users-and-roles) to bypass [row-level security](/docs/stable/row-level-security.md), execute the following statement to grant the [`BYPASSRLS`](/docs/stable/security-reference/authorization) privilege: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH BYPASSRLS; ``` To disable the role's ability to bypass RLS, execute the following statement: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE carl WITH NOBYPASSRLS; ``` ### Set default session variable values for a role In the following example, the `root` user creates a role named `max`, and sets the default value of the `timezone` [session variable](/docs/stable/set-vars.md#supported-variables) for the `max` role. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE ROLE max WITH LOGIN; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE max SET timezone = 'America/New_York'; ``` This statement does not affect the default `timezone` value for any role other than `max`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW timezone; ``` ``` timezone ------------ UTC (1 row) ``` To see the default `timezone` value for the `max` role, run the `SHOW` statement as a member of the `max` role: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW timezone; ``` ``` timezone -------------------- America/New_York (1 row) ``` For a list of the session variables that have been updated from default values, see [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role). ### Set default session variable values for a role in a specific database In the following example, the `root` user creates a role named `max` and a database named `movr`, and sets the default value of the `statement_timeout` [session variable](/docs/stable/set-vars.md#supported-variables) for the `max` role in the `movr` database. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE DATABASE IF NOT EXISTS movr; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE ROLE IF NOT EXISTS max WITH LOGIN; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE max IN DATABASE movr SET statement_timeout = '10s'; ``` This statement does not affect the default `statement_timeout` value for any role other than `max`, or in any database other than `movr`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW statement_timeout; ``` ``` statement_timeout --------------------- 0 (1 row) ``` To see the new default `statement_timeout` value for the `max` role, run the `SHOW` statement as a member of the `max` role that has connected to the cluster, with the database `movr` specified in the connection string. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach sql --url 'postgresql://max@localhost:26257/movr?sslmode=disable' ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW statement_timeout; ``` ``` statement_timeout --------------------- 10000 (1 row) ``` For a list of the session variables that have been updated from default values, see [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role). ### Set default session variable values for a specific database In the following example, the `root` user creates a database named `movr`, and sets the default value of the `timezone` [session variable](/docs/stable/set-vars.md#supported-variables) for all roles in that database. The [owner](/docs/stable/security-reference/authorization.md#object-ownership) of a database can also execute `ALTER ROLE ALL IN DATABASE ... {SET|RESET}` for that database. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE DATABASE IF NOT EXISTS movr; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE ALL IN DATABASE movr SET timezone = 'America/New_York'; ``` > [!NOTE] > > This statement is identical to [`ALTER DATABASE movr SET timezone = 'America/New_York';`](/docs/stable/alter-database). This statement does not affect the default `timezone` value for any database other than `movr`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW timezone; ``` ``` timezone ------------ UTC (1 row) ``` To see the default `timezone` value for the `max` role, run the `SHOW` statement as a member of the `max` role: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW timezone; ``` ``` timezone -------------------- America/New_York (1 row) ``` For a list of the session variables that have been updated from default values, see [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role). ### Set default session variable values for all users To set a default value for all users for any [session variable](/docs/stable/set-vars.md) that applies during login, issue a statement like the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE ALL SET sql.spatial.experimental_box2d_comparison_operators.enabled = "on"; ``` ``` ALTER ROLE ``` The `root` user is exempt from session variable settings changes. This is a failsafe; if you make a mistake with a session variable setting, you can still log in as `root` and be unaffected. > [!NOTE] > > Use [`ALTER ROLE ALL SET {sessionvar} = {val}`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users) instead of the `sql.defaults.*` [cluster settings](/docs/stable/cluster-settings.md). This allows you to set a default value for all users for any [session variable](/docs/stable/set-vars.md) that applies during login, making the `sql.defaults.*` cluster settings redundant. For a list of the session variables that have been updated from default values, see [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role). ### Set the `SUBJECT` role option for certificate based authentication You can associate an [X.509](https://en.wikipedia.org/wiki/X.509) certificate's Subject with a [role](/docs/stable/security-reference/authorization.md#roles) as shown below. Note that the Subject fields in the certificate have to be an exact match with what you pass in via the SQL statement. By exact match, we mean that the order of attributes passed in via the SQL statement must match the order of attributes in the certificate. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE max WITH SUBJECT 'CN=myName2,OU=myOrgUnit2,O=myOrg2,L=myLocality2,ST=myState2,C=myCountry2' LOGIN; ``` If you manage your own Certificate Authority (CA) infrastructure, CockroachDB supports mapping between the Subject field of your [X.509 certificates](https://en.wikipedia.org/wiki/X.509) and SQL [roles](/docs/stable/security-reference/authorization.md#roles). For more information, see [Certificate-based authentication using multiple values from the X.509 Subject field](/docs/stable/certificate-based-authentication-using-the-x509-subject-field.md). ## See also * [`DROP ROLE`](/docs/stable/drop-role) * [`SHOW ROLES`](/docs/stable/show-roles) * [`GRANT`](/docs/stable/grant) * [`SHOW GRANTS`](/docs/stable/show-grants) * [`cockroach cert`](/docs/stable/cockroach-cert) * [SQL Statements](/docs/stable/sql-statements.md) * [Authorization Best Practices](/docs/stable/security-reference/authorization.md#authorization-best-practices) * [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role)