ALTER USER

Changes user account parameters and user-level configuration parameters.

Syntax

ALTER USER user‑name {
   account‑parameter setting[,…]
   | SET [PARAMETER] cfg‑parameter=value[,…]
   | CLEAR [PARAMETER] cfg‑parameter[,…]
}

Parameters

user‑name	Name of the 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‑parameter value	Specifies user account settings (see below). Changes to a user account apply only to the current session and to all later sessions launched by this user.
SET [PARAMETER]	Sets the specified configuration parameters. The new setting applies only to the current session, and to all later sessions launched by this user. Concurrent user sessions are unaffected by new settings unless they call meta-function RESET_SESSION.
CLEAR [PARAMETER]	Resets the specified configuration parameters to their default values.

SET | CLEAR PARAMETER can specify only user-level configuration parameters, otherwise Vertica returns an error. For details, see Setting User-Level Configuration Parameters below.

User Account Parameters

Specify one or more user‑account parameters and their settings as a comma-delimited list:

account‑parameter setting[,…]

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

IDENTIFIED BY
PROFILE
SECURITY ALGORITHM

Parameter	Settings
ACCOUNT	Locks or unlocks user access to the database, set to one of the following: `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	Specifies what roles are the default roles for this user, set to one of the following: `NONE` (default): Removes all default roles. `role[,…]`: Comma-delimited list of roles. `ALL`: Sets as default all user roles. `ALL EXCEPT role[,…]`: 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	Specifies how long a user query can block on any session socket, 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]' \| ['password_hash' salt 'hash_salt'] [REPLACE 'current_password']	Specifies a password with one of the following methods: password specifies a plaintext password, which is then hashed for storage in your database password_hash specifies a pre-hashed password and its associated hex string hash_salt. Setting a password this way bypasses all password complexity requirements. providing an empty string or omitting the parameter creates a user without a password 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	The length of time the system waits before disconnecting an idle session, 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	Specifies the maximum number of connections the user can have to the server, 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	Specifies how much memory can be allocated to user requests, set to one of the following expressions: `NONE` (default): No limit 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	Assigns a profile that controls password requirements for this user, set to 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	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	Assigns a default resource pool to this user.
RUNTIMECAP	Specifies how long this user's queries can execute, set to 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	Specifies the user's default search path that tells Vertica which schemas to search for unqualified references to tables and UDFs, set to one of the following: `DEFAULT` (default): Sets the search path as follows: "$user", public, v_catalog, v_monitor, v_internal 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 system-level parameter, `SecurityAlgorithm` `SHA512` `MD5` The user's password expires when you change the `SECURITY_ALGORITHM` value and must be reset.
TEMPSPACECAP	Sets how much temporary file storage is available for user requests, one of the following: `NONE` (default): No limit String value that specifies the storage limit, one of the following: `int%` expresses the maximum as a percentage of total temporary storage available to the Resource Manager, where int is an integer value between 0 and 100. For example: `TEMPSPACECAP '40%'` `int{K\|M\|G\|T}` expresses storage 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

When changing a another user's default resource pool to one outside of the PUBLIC schema, the user must have USAGE privileges on the resource pool from at least one of the following:

Setting User-Level Configuration Parameters

SET | CLEAR PARAMETER can specify only user-level configuration parameters, otherwise Vertica returns an error. Only superusers can set and clear user-level parameters, unless they are also supported at the session level.

To get the names of user-level parameters, query system table CONFIGURATION_PARAMETERS. For example:

SELECT parameter_name, allowed_levels FROM configuration_parameters 
      WHERE allowed_levels ilike '%USER%' AND parameter_name ilike '%depot%';
     parameter_name      |     allowed_levels
-------------------------+-------------------------
 UseDepotForWrites       | SESSION, USER, DATABASE
 DepotOperationsForQuery | SESSION, USER, DATABASE
 UseDepotForReads        | SESSION, USER, DATABASE
(3 rows)

The following example sets the user-level configuration parameter DepotOperationsForQuery for a specific user:

=> SHOW USER user1 ALL;
          name           | setting
-------------------------+---------
 DepotOperationsForQuery | Fetches
(1 row)

=> ALTER USER user1 SET PARAMETER UseDepotForWrites=0;
ALTER USER
=> SHOW USER user1 ALL;
          name           | setting
-------------------------+---------
 DepotOperationsForQuery | Fetches
 UseDepotForWrites       | 0
(2 rows)

Examples

Change user's password

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

Change user's security algorithm and password

Change a 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

Make a user's assigned roles the user's default roles:

=> CREATE USER user1;
CREATE USER
=> GRANT role1, role2, role3 to user1;
=> ALTER USER user1 DEFAULT ROLE ALL;

Assign user default roles with EXCEPT

Set all user-assigned roles to default roles except role1:

=> CREATE USER user2;
CREATE USER
=> GRANT role1, role2, role3 to user2;
=> ALTER USER user2 DEFAULT ROLE ALL EXCEPT role1;