Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

ALTER USER

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 execute this command.

Syntax

Copy
ALTER USER username [ WITH ] option [, ... ] where option is CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | PASSWORD { 'password' | 'md5hash' } [ VALID UNTIL 'expiration_date' ] | RENAME TO new_name | | CONNECTION LIMIT { limit | UNLIMITED } | SET parameter { TO | = } { value | DEFAULT } | RESET parameter

Parameters

username

Name of the user account.

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.

PASSWORD { 'password' | 'md5hash' }

Sets the user's password.

You can specify the password in clear text or as an MD5 hash string.

For clear text, the password must meet the following constraints:

  • It must be 8 to 64 characters in length.

  • It must contain at least one uppercase letter, one lowercase letter, and one number.

  • It can use any printable ASCII characters (ASCII code 33 to 126) except ' (single quote), " (double quote), \, /, @, or space.

As a more secure alternative to passing the CREATE USER password parameter as clear text, you can specify an MD5 hash of a string that includes the password and user name.

Note

When you specify an MD5 hash string, the ALTER USER command checks for a valid MD5 hash string, but it doesn't validate the password portion of the string. It is possible in this case to create a password, such as an empty string, that you can't use to log on to the database.

To specify an MD5 password, follow these steps:

  1. Concatenate the password and user name.

    For example, for password ez and user user1, the concatenated string is ezuser1.

  2. Convert the concatenated string into a 32-character MD5 hash string. You can use any MD5 utility to create the hash string. The following example uses the Amazon Redshift MD5 Function and the concatenation operator ( || ) to return a 32-character MD5-hash string.

    Copy
    select md5('ez' || 'user1'); md5 -------------------------------- 153c434b4b77c89e6b94f12c5393af5b
  3. Concatenate 'md5' in front of the MD5 hash string and provide the concatenated string as the md5hash argument.

    Copy
    create user user1 password 'md5153c434b4b77c89e6b94f12c5393af5b';
  4. Log on to the database using the user name and password.

    For this example, log on as user1 with password ez.

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.

RENAME TO

Renames the user account.

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 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 in until the password is reset. For example:

Copy
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 is not enforced for super users. Use the UNLIMITED keyword to permit the maximum number of concurrent connections. The limit of concurrent connections for each cluster is 500. 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.

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.

Usage Notes

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 for the current user and session, use a SET command.

Examples

The following example gives the user ADMIN the privilege to create databases:

Copy
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:

Copy
alter user admin password 'adminPass9' valid until '2017-12-31 23:59';

The following example renames the user ADMIN to SYSADMIN:

Copy
alter user admin rename to sysadmin;