Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # ALTER USER ALTER USER Changes a database user. ## Required privileges Required privileges Following are required privileges for ALTER USER: + Superuser + Users with the ALTER USER privilege + Current user who wants to change their own password ## Syntax Syntax ``` ALTER USER username [ WITH ] option [, ... ] where option is CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | SYSLOG ACCESS { RESTRICTED | UNRESTRICTED } | PASSWORD { 'password' | 'md5hash' | 'sha256hash' | DISABLE } [ VALID UNTIL 'expiration_date' ] | RENAME TO new_name | | CONNECTION LIMIT { limit | UNLIMITED } | SESSION TIMEOUT limit | RESET SESSION TIMEOUT | SET parameter { TO | = } { value | DEFAULT } | RESET parameter | EXTERNALID external_id ``` ## Parameters Parameters *username* Name of the user. WITH Optional keyword. CREATEDB \$1 NOCREATEDB The CREATEDB option allows the user to create new databases. NOCREATEDB is the default. CREATEUSER \$1 NOCREATEUSER The CREATEUSER option creates a superuser with all database privileges, including CREATE USER. The default is NOCREATEUSER. For more information, see [Superusers](r_superusers.md). SYSLOG ACCESS \$1 RESTRICTED \$1 UNRESTRICTED \$1 A clause that specifies the level of access that the user has to the Amazon Redshift system tables and views. Regular users who have the SYSLOG ACCESS RESTRICTED permission can see only the rows generated by that user in user-visible system tables and views. The default is RESTRICTED. Regular users who have the SYSLOG ACCESS UNRESTRICTED permission can see all rows in user-visible system tables and views, including rows generated by another user. UNRESTRICTED doesn't give a regular user access to superuser-visible tables. Only superusers can see superuser-visible tables. Giving a user unrestricted access to system tables gives the user visibility to data generated by other users. For example, STL\$1QUERY and STL\$1QUERYTEXT contain the full text of INSERT, UPDATE, and DELETE statements, which might contain sensitive user-generated data. All rows in SVV\$1TRANSACTIONS are visible to all users. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data). PASSWORD \$1 '*password*' \$1 '*md5hash*' \$1 '*sha256hash*' \$1 DISABLE \$1 Sets the user's password. By default, users can change their own passwords, unless the password is disabled. To disable a user's password, specify DISABLE. When a user's password is disabled, the password is deleted from the system and the user can log on only using temporary AWS Identity and Access Management (IAM) user credentials. For more information, see [Using IAM authentication to generate database user credentials](https://docs.aws.amazon.com/redshift/latest/mgmt/generating-user-credentials.html). Only a superuser can enable or disable passwords. You can't disable a superuser's password. To enable a password, run ALTER USER and specify a password. For details about using the PASSWORD parameter, see [CREATE USER](r_CREATE_USER.md). VALID UNTIL '*expiration\$1date*' Specifies that the password has an expiration date. Use the value `'infinity'` to avoid having an expiration date. The valid data type for this parameter is timestamp. Only superusers can use this parameter. RENAME TO Renames the user. *new\$1name* New name of the user. For more information about valid names, see [Names and identifiers](r_names.md). When you rename a user, you must also reset the user’s password. The reset password doesn't have to be different from the previous password. The user name is used as part of the password encryption, so when a user is renamed, the password is cleared. The user will not be able to log on until the password is reset. For example: ``` alter user newuser password 'EXAMPLENewPassword11'; ``` CONNECTION LIMIT \$1 *limit* \$1 UNLIMITED \$1 The maximum number of database connections the user is permitted to have open concurrently. The limit isn't enforced for superusers. Use the UNLIMITED keyword to permit the maximum number of concurrent connections. A limit on the number of connections for each database might also apply. For more information, see [CREATE DATABASE](r_CREATE_DATABASE.md). The default is UNLIMITED. To view current connections, query the [STV\$1SESSIONS](r_STV_SESSIONS.md) system view. If both user and database connection limits apply, an unused connection slot must be available that is within both limits when a user attempts to connect. SESSION TIMEOUT *limit* \$1 RESET SESSION TIMEOUT The maximum time in seconds that a session remains inactive or idle. The range is 60 seconds (one minute) to 1,728,000 seconds (20 days). If no session timeout is set for the user, the cluster setting applies. For more information, see [ Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*. When you set the session timeout, it's applied to new sessions only. To view information about active user sessions, including the start time, user name, and session timeout, query the [STV\$1SESSIONS](r_STV_SESSIONS.md) system view. To view information about user-session history, query the [STL\$1SESSIONS](r_STL_SESSIONS.md) view. To retrieve information about database users, including session-timeout values, query the [SVL\$1USER\$1INFO](r_SVL_USER_INFO.md) view. SET Sets a configuration parameter to a new default value for all sessions run by the specified user. RESET Resets a configuration parameter to the original default value for the specified user. *parameter* Name of the parameter to set or reset. *value* New value of the parameter. DEFAULT Sets the configuration parameter to the default value for all sessions run by the specified user. EXTERNALID *external\$1id* The identifier for the user, which is associated with an identity provider. The user must have their password disabled. For more information, see [Native identity provider (IdP) federation for Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-native-idp.html). ## Usage notes Usage Notes + **Attempting to alter rdsdb** – You can't alter the user named `rdsdb`. + **Creating an unknown password** – When using AWS Identity and Access Management (IAM) authentication to create database user credentials, you might want to create a superuser that is able to log in only using temporary credentials. You can't disable a superuser's password, but you can create an unknown password using a randomly generated MD5 hash string. ``` alter user iam_superuser password 'md51234567890123456780123456789012'; ``` + **Setting search\$1path** – When you set the [search\$1path](r_search_path.md) parameter with the ALTER USER command, the modification takes effect on the specified user's next login. If you want to change the search\$1path value for the current user and session, use a SET command. + **Setting the time zone** – When you use SET TIMEZONE with the ALTER USER command, the modification takes effect on the specified user's next login. + **Working with dynamic data masking and row-level security policies** – When your provisioned cluster or serverless namespace has any dynamic data masking or row-level security policies, the following commands are blocked for regular users: ``` ALTER SET enable_case_sensitive_super_attribute/enable_case_sensitive_identifier/downcase_delimited_identifier ``` Only superusers and users with the ALTER USER privilege can set these configuration options. For information on row-level security, see [Row-level security](t_rls.md). For information on dynamic data masking, see [Dynamic data masking](t_ddm.md). ## Examples Examples The following example gives the user ADMIN the privilege to create databases: ``` alter user admin createdb; ``` The following example sets the password of the user ADMIN to `adminPass9` and sets an expiration date and time for the password: ``` alter user admin password 'adminPass9' valid until '2017-12-31 23:59'; ``` The following example renames the user ADMIN to SYSADMIN: ``` alter user admin rename to sysadmin; ``` The following example updates the idle-session timeout for a user to 300 seconds. ``` ALTER USER dbuser SESSION TIMEOUT 300; ``` Resets the user's idle-session timeout. When you reset it, the cluster setting applies. You must be a database superuser to run this command. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*. ``` ALTER USER dbuser RESET SESSION TIMEOUT; ``` The following example updates the external ID for a user named `bob`. The namespace is `myco_aad`. If the namespace isn't associated with a registered identity provider, it results in an error. ``` ALTER USER myco_aad:bob EXTERNALID "ABC123" PASSWORD DISABLE; ``` The following example sets the time zone for all sessions run by a specific database user. It changes the time zone for subsequent sessions, but not for the current session. ``` ALTER USER odie SET TIMEZONE TO 'Europe/Zurich'; ``` The following example sets the maximum number of database connections that the user `bob` is allowed to have open. ``` ALTER USER bob CONNECTION LIMIT 10; ```