ALTER USER - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.


Changes a database user account. If you are the current user, you can change your own password. For all other options, you must be a database superuser to run this command.


ALTER USER username [ WITH ] option [, ... ] where option is CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | SYSLOG ACCESS { RESTRICTED | UNRESTRICTED } | PASSWORD { 'password' | 'md5hash' | 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



Name of the user account.


Optional keyword.


The CREATEDB option allows the user to create new databases. NOCREATEDB is the default.


The CREATEUSER option creates a superuser with all database privileges, including CREATE USER. The default is NOCREATEUSER. For more information, see superuser.


A clause that specifies the level of access that the user has to the Amazon Redshift system tables and views.

If RESTRICTED is specified, the user can see only the rows generated by that user in user-visible system tables and views. The default is RESTRICTED.

If UNRESTRICTED is specified, the user 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_QUERY and STL_QUERYTEXT contain the full text of INSERT, UPDATE, and DELETE statements, which might contain sensitive user-generated data.

All rows in SVV_TRANSACTIONS are visible to all users.

For more information, see Visibility of data in system tables and views.

PASSWORD { 'password' | 'md5hash' | DISABLE }

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 Amazon Identity and Access Management (IAM) user credentials. For more information, see Using IAM authentication to generate database user credentials. 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.

VALID UNTIL 'expiration_date'

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.


Renames the user account.


New name of the user. For more information about valid names, see Names and identifiers.


When you rename a user, you must also change the user’s 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';

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. The default is UNLIMITED. To view current connections, query the STV_SESSIONS 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.


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 in the Amazon Redshift Cluster 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_SESSIONS system view. To view information about user-session history, query the STL_SESSIONS view. To retrieve information about database users, including session-timeout values, query the SVL_USER_INFO view.


Sets a configuration parameter to a new default value for all sessions run by the specified user.


Resets a configuration parameter to the original default value for the specified user.


Name of the parameter to set or reset.


New value of the parameter.


Sets the configuration parameter to the default value for all sessions run by the specified user.

Usage notes

When using Amazon Identity and Access Management (IAM) authentication to create database user credentials, you might want to create a superuser that is able to log on 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 'mdA51234567890123456780123456789012';

When you set the search_path parameter with the ALTER USER command, the modification takes effect on the specified user's next login. If you want to change the search_path value for the current user and session, use a SET command.


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.


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 in the Amazon Redshift Cluster Management Guide.