ALTER USER - Amazon Redshift
Required privilegesSyntaxParametersUsage NotesExamples

ALTER USER

Changes a database user.

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

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
| EXTERNALID external_id

Parameters

username

Name of the user.

WITH

Optional keyword.

CREATEDB | NOCREATEDB

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

CREATEUSER | NOCREATEUSER

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

SYSLOG ACCESS { RESTRICTED | UNRESTRICTED }

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.

Note

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 AWS 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.

Only superusers can use this parameter.

RENAME TO

Renames the user.

new_name

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

Important

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 { limit | UNLIMITED }

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.

Note

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 | 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 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_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.

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_id

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.

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_path – 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.

  • 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 <current_user> 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. For information on dynamic data masking, see Dynamic data masking.

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 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;