ALTER ROLE statement to add, change, or remove a role’s password, change the role options for a role, and set default session variable values for a role.
You can use the keywords ROLE and USER interchangeably. ALTER USER is an alias for ALTER ROLE.
Considerations
- Password creation and alteration is supported only in secure clusters.
Required privileges
- To alter an
adminrole, the user must be a member of theadminrole. - To alter other roles, the user must be a member of the
adminrole or have theCREATEROLErole option.
Synopsis
Parameters
Role options
Examples
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:Prevent a role from using password authentication
The following statement prevents the user from using password authentication and mandates certificate-based client authentication: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 and manage authentication methods for them:Allow a role to create and rename databases
The following example allows the role to create or rename databases:Allow a role to pause, resume, and cancel non-admin jobs
The following example allows the role to pause, resume, and cancel jobs:Allow a role to see and cancel non-admin queries and sessions
The following example allows the role to cancel queries and sessions for other non-admin roles:
Allow a role to control changefeeds
The following example allows the role to runCREATE CHANGEFEED:
Allow a role to modify cluster settings
The following example allows the role to modify cluster settings:Set default session variable values for a role
In the following example, theroot user creates a role named max, and sets the default value of the timezone session variable for the max role.
timezone value for any role other than max:
timezone value for the max role, run the SHOW statement as a member of the max role:
Set default session variable values for a role in a specific database
In the following example, theroot user creates a role named max and a database named movr, and sets the default value of the statement_timeout session variable for the max role in the movr database.
statement_timeout value for any role other than max, or in any database other than movr.
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.
Set default session variable values for a specific database
In the following example, theroot user creates a database named movr, and sets the default value of the timezone session variable for all roles in that database.
This statement is identical to
ALTER DATABASE movr SET timezone = 'America/New_York';.timezone value for any database other than movr:
timezone value for the max role, run the SHOW statement as a member of the max role:
Set default session variable values for all users
To set a default value for all users for any session variable that applies during login, issue a statement like the following: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.
Use
ALTER ROLE ALL SET {sessionvar} = {val} instead of the sql.defaults.* cluster settings. This allows you to set a default value for all users for any session variable that applies during login, making the sql.defaults.* cluster settings redundant.