ALTER USER

Changes a database user account. Changes that you make to a user account affect only future user sessions.

The following ALTER USER parameters are invalid for a user who is added to the Vertica database with the LDAPLink service:

  • IDENTIFIED BY
  • PROFILE
  • SECURITY ALGORITHM

Syntax

ALTER USER name user‑parameter setting[,…]

 
ACCOUNT
DEFAULT ROLE*
GRACEPERIOD
IDENTIFIED BY
IDLESESSIONTIMEOUT
MAXCONNECTIONS 
MEMORYCAP 
PASSWORD EXPIRE 
PROFILE
RENAME TO*
RESOURCE POOL
RUNTIMECAP
SEARCH_PATH
SECURITY_ALGORITHM
TEMPSPACECAP

* Cannot be used in combination with other parameters

Parameters

name

Specifies the name of the new user. Names that contain special characters must be double-quoted. To enforce case-sensitivity, use double-quotes.

For details on name requirements, see Creating a Database Name and Password.
ACCOUNT { LOCK | UNLOCK }

Locks or unlocks a user's access to the database:

  • UNLOCK (default) 
  • LOCK prevents a new user from logging in. This can be useful when creating an account for a user who does not need immediate access.

To automate account locking, set a maximum number of failed login attempts with CREATE PROFILE.

DEFAULT ROLE roles‑expression

Specifies what roles are the default roles for this user, with one of the following expressions:

  • NONE (default): Removes all default roles.
  • role[,…]: A comma-delimited list of roles.
  • ALL: Sets as default all user roles.
  • ALL EXCEPT role[,…] : A comma -delimited list of roles to exclude as default roles.

Default roles are automatically activated when a user logs in. The roles specified by this parameter supersede any roles assigned earlier.

DEFAULT ROLE cannot be specified in combination with other ALTER USER parameters.

GRACEPERIOD limit

Specifies how long a user query can block on any session socket, where limit is one of the following:

  • NONE (default): Removes any grace period previously set on session queries.
  • 'interval': Specifies as an interval the maximum grace period for current session queries, up to 20 days.

For details, see Handling Session Socket Blocking.
IDENTIFIED BY
'[new‑password]'
[REPLACE
'current‑password']

Sets a new password for the user, where new‑password must conform to the password complexity policy set by the user's profile.

Superusers can change the password for any user, and are not required to specify the REPLACE clause. Non-superusers can only change their own password, and must supply their current password with the REPLACE clause.

If you supply an empty string, the user's current password is removed, and the user is no longer prompted for a password when starting a new session.

For details, see Password Guidelines and Creating a Database Name and Password.
IDLESESSIONTIMEOUT limit

The length of time the system waits before disconnecting an idle session, where limit is one of the following:

  • NONE (default): No limit set for this user. If you omit this parameter, no limit is set for this user.
  • 'interval' An interval value, up to one year.

For details, see Managing Client Connections.
MAXCONNECTIONS limit

Specifies the maximum number of connections the user can have to the server, where limit is one of the following:

  • NONE (default): No limit set. If you omit this parameter, the user can have an unlimited number of connections across the database cluster.
  • integer ON DATABASE: Sets to integer the maximum number of connections across the database cluster.
  • integer ON NODE: Sets to integer the maximum number of connections to each node.

For details, see Managing Client Connections.
MEMORYCAP limit

Specifies how much memory can be allocated to user requests, where limit is specified in this format:

  • NONE (default): No limit

  • 'max‑expression': A string value that specifies the memory limit, one of the following:

    • int% — Expresses the maximum as a percentage of total memory available to the Resource Manager, where int is an integer value between 0 and 100.For example:

      MEMORYCAP '40%'

    • int{K|M|G|T} — Expresses memory allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      MEMORYCAP '10G'

PASSWORD EXPIRE

Forces immediate expiration of the user's password. The user must change the password on the next login.

PASSWORD EXPIRE has no effect when using external password authentication methods such as LDAP or Kerberos.

PROFILE profile

Assigns a profile that controls password requirements for this user, where profile is one of the following:

  • DEFAULT (default): Assigns the default database profile to this user.
  • profile-name: A profile that is defined by CREATE PROFILE. 
RENAME TO new-user-name
 Assigns the user a new user name. All privileges assigned to the user remain unchanged.

RENAME TO cannot be specified in combination with other ALTER USER parameters.

RESOURCE POOL pool‑name

Assigns a default resource pool to this user. The user must also be granted privileges to this pool, unless privileges to the pool are set to PUBLIC.

RUNTIMECAP limit

Specifies how long this user's queries can execute, where limit is one of the following:

  • NONE (default): No limit set for this user. If you omit this parameter, no limit is set for this user.
  • 'interval' An interval value, up to one year.

A query's runtime limit can be set at three levels: the user's runtime limit, the user's resource pool, and the session setting. For more information, see Setting a Runtime Limit for Queries in the Administrator's Guide.
SEARCH_PATH path

Specifies the user's default search path that tells Vertica which schemas to search for unqualified references to tables and UDFs, where path is one of the following:

  • DEFAULT (default): Sets the search path as follows: 
    "$user", public, v_catalog, v_monitor, v_internal
  • A comma-delimited list of schemas.

For details, see Setting Search Paths in the Administrator's Guide.
SECURITY_ALGORITHM 'algorithm'

Set the user-level security algorithm for hash authentication, where algorithm is one of the following:

  • NONE (default): Uses the MD5 algorithm for hash authentication.
  • MD5
  • SHA512

The user's password expires when you change the SECURITY_ALGORITHM value, and must be reset.
TEMPSPACECAP limit

Limits how much temporary file storage is available for user requests, where limit is one of the following:

  • NONE (default): No limit

  • 'max‑expression': A string value that specifies the storage limit, one of the following:

    • int% — Expresses storage as a percentage of total file space is available, where int is an integer value between 0 and 100.For example:

      TEMPSPACECAP '40%'

    • int{K|M|G|T} — Expresses memory allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      TEMPSPACECAP '10G'

Privileges

Non-superusers can change the following options on their own user accounts:

  • IDENTIFIED BY
  • RESOURCE POOL
  • SEARCH_PATH
  • SECURITY_ALGORITHM 

Examples

Change the user password 

=> CREATE USER user1;
=> ALTER USER user1 IDENTIFIED BY 'newpassword';

Change the security algorithm and password

This example changes the user's hash authentication and password to SHA-512 and newpassword, respectively. When you execute the ALTER USER statement, Vertica hashes the password, using the SHA-512 algorithm, and saves the hashed version:

=> CREATE USER user1;
=> ALTER USER user1 SECURITY_ALGORITHM 'SHA512' IDENTIFIED BY 'newpassword'

Assign user default roles

This example makes a user's assigned roles the user's default roles. The first ALTER USER statement makes role1 the default role. The second ALTER USER statement makes role1, role2, and role3 the user's default roles.

=> CREATE USER user1;
CREATE USER
=> GRANT role1, role2, role3 to user1;
=> ALTER USER user1 default role role1;
=> ALTER USER user1 default role ALL;

Assign user default roles with EXCEPT

This example makes all the user's assigned roles default roles with the exception of role1.

=> GRANT role1, role2, role3 to user1;
=> ALTER USER user1 default role ALL EXCEPT r1;

See Also