Parameters

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.

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.

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_QUERY_TEXT contain the full text of INSERT, UPDATE, and DELETE
statements, which might contain sensitive user-generated data.

All rows in STV_RECENTS and SVV_TRANSACTIONS are visible to all users.

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

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.

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:

Concatenate the password and user name.

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

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.

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

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

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:

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: